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.
AnnetteDavidAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
You can also try formatting so that things sort properly -

Use this for your field:

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

And this for your criteria:

Between Format([Enter Start Date here],"yyyymmdd") And Format([Enter End Date here], "yyyymmdd")
0
 
mbizupCommented:
Try explicity onverting your field to date before using it in a comparison.

ie:

CDate([YourField])

0
 
mbizupCommented:
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]
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dale FyeCommented:
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 ....
0
 
GRayLCommented:
Make sure the first line of your query is:

PARAMETERS [Enter Start Date Here] Date, [Enter End Date Here] Date;
0
 
AnnetteDavidAuthor Commented:
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
0
 
AnnetteDavidAuthor Commented:
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.
0
 
mbizupCommented:
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).
0
 
mbizupCommented:
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.
0
 
AnnetteDavidAuthor Commented:
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
0
 
mbizupCommented:
Glad to help out :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.