[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Searching for a date with parameter query not working

Posted on 2011-10-20
11
Medium Priority
?
350 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:AnnetteDavid
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36998838
Try explicity onverting your field to date before using it in a comparison.

ie:

CDate([YourField])

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36998848
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36998872
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 36999748
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
 
LVL 44

Expert Comment

by:GRayL
ID: 37002455
Make sure the first line of your query is:

PARAMETERS [Enter Start Date Here] Date, [Enter End Date Here] Date;
0
 

Author Comment

by:AnnetteDavid
ID: 37004762
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
 

Author Comment

by:AnnetteDavid
ID: 37005661
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37005684
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37005691
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
 

Author Closing Comment

by:AnnetteDavid
ID: 37005749
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37005819
Glad to help out :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question