Access Query Expression Using Date

Question

hello,

I am trying to run a query to select certain records based on the date.

I have a table listing my company's advertisement campaigns and these campaigns usually last several days or even several months.

in the table I have "ad start date" and "expected ad end date" as a period is short date format.

I want to run a query and I want the user to be prompted to enter current date and returns the number of days each advertisement campaign has been active. in terms of expression it would be like this Campaign active days: [current date] - [ad start date]. I did this using the expression builder.

In addition, I created a form prompting the user to enter the current date in mm/dd/yyyy format and in the query I created a  field called [current date]

and referenced that current date field to the form.. ( like this.. Current Date: [Form]![Current Date Form]![CDate txt box])

However, when I run the query it asks parameter value [current date] even though I entered the current date in the form and have it as a filed in the query.

what is going wrong?

Answer 1

Sandra,

thank you for your demo.

However, what if I want the date that user enters to be populated as a filed in the query?

Answer 2

Ken,

However, I want the user to enter the date. he or she may be interested in the number of active days on a certain date.

Answer 3

You don't need the user to enter the current date; simply return it with the Date() function.  In the query add a computed column by entering the following in the 'field' row of a blank column in design view:

    DaysActive: Date() - [ad start date]

Answer 4

....if I got your meaning,.....just "minimal" demo for you :

http://1drv.ms/1xqxyp9

run the query46 and then Ordini1 form. entering a date in testo11 testbox and then pressing enter you will see the difference wanted in continuous form

it's based on nothWind DB.

Ciao!

Sandro.

Answer 5

ciao Spark0227,

try to run this query _

PARAMETERS ddataD DateTime;

SELECT yourTable.ID, yourTable.[ad start date], DateDiff("d",[ddataD],[ad start date]) AS ddataD1

FROM yourTable;

replace yourTable with the correct name of your table.

Anyway I think you had better force the user to fill the "current data" from which you want the difference by a form...

let's start like that first.

Ciao!

Sandro.