Link to home
Start Free TrialLog in
Avatar of AnnetteDavid
AnnetteDavidFlag for South Africa

asked on

Searching for a date with parameter query not working

Please can somebody help me.  I have the following in a query
ExpiryDatemMnth: DateAdd("m",[NoMnthsTillExpiry],[DateofIssueProFormaCertificate]) which is working perfectly.

I now want to search within this query with a parameter query
Between [Enter Start Date here] And [Enter End Date here] so that I can produce an Certificate Expiry Date Report

And its not working as it doesnt seem to recognise the field as a date,  I tried setting the properties to a date in the query but it still brings up a nil result as its not recognising the field.  I think I am overthinking this.  Help please.
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Try explicity onverting your field to date before using it in a comparison.

ie:

CDate([YourField])

It's not clear how you are using it, but I'm guessing you would need something like:

CDate([YourFieldName]) Between [Enter Start Date here] And [Enter End Date here]
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can also define those parameters as date values.  To do so, right click in the top of the query grid, select parameters, then enter the following under the Parameter and Data Type columns.

[Enter Start Date here]               Date/Time
[Enter End Date here]                 Date/Time

If you then look at your SQL statement, it will look something like:

PARAMETERS [Enter Start Date here] DateTime, [Enter End Date here] DateTime;
SELECT ....
Make sure the first line of your query is:

PARAMETERS [Enter Start Date Here] Date, [Enter End Date Here] Date;
Avatar of AnnetteDavid

ASKER

So Sorry I was out of the office yesterday.  I will work on this today, thank you all so much for your solutions, I will give feedback asap
Hi Muizup, I started with your solutions and it worked perfectly thanks in the query, however I would like to change the format in the report from 20111001 to a med date format of 1 Oct 2011, and I seem unable to do this in the properties.  Thank you so much again for your help.
A solution to that is to use the formatted field I gave you as a seperate field - exclusively for getting the sorting right, and keep your original field as is - but with no criteria:

ExpiryDatemMnth: DateAdd("m",[NoMnthsTillExpiry],[DateofIssueProFormaCertificate])

Use your field for the report - so that is displays the way you want it to, and my suggested field to ensure that the sorting/criteria are correct (this field does not need to appear on your report at all).
ie:  use both variations of the field in your query so that it sorts/filters properly, but only display your original field on the report to get the display you want.
Aboloutely Brilliant, thanks so much for your assistance, for the format I also used your suggested idea of using the original field, worked perfectly.  Thank you again
Glad to help out :)