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

ciao Spartk0227,

sorry to be late  in replying you.

I'm not pretty sure to have got very well your meaning...

so, try in this way:

copy and past form ordni1 calling it ordini2

create a new query having this sql statement :

SELECT Ordini.IDOrdine

           , Ordini.IDCliente

          , Ordini.IDImpiegato

         , Ordini.[Data Ordine], Ordini.DataRichiesta

         , DateDiff("d",[forms]![ordini2]![testo11],[datarichiesta]) AS dateDifference

FROM Ordini

and put a command button in which you invoke in click event me.requery.

put the field dateDifference in your form ad bounded textbox control.

whatever date you enter in testo11, and then pressing the relevant command button you will retrevie the datedifference in your form.

any problems take a look here :http://1drv.ms/1a1Siit

Hope this helps.

Ciap, Sandro.

Answer 2

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. 

Then reference the control in the form as a parameter both as a criterion and as the value of a column.  Be sure that:

1.  The form is open when the query is opened.
2.  The parameter is declared as DATETIME.
3.  The control in the form defaults to the current date.