Using Form Data as Query criteria

[rrobinson]

I'm new to this forum and creating parameter driven queries. I have searched the forum a bit to see if anyone else has had this issue, but nothing so I'm posting my issue in search for some help...so here goes.



I've followed Access Help instructions on how to "Create a form that collects parameters" but when I add the criteria to prompt for Dates and test the query I get a blank query instead of seeing the form that I created.

Here's the SQL View of the query (sorry not sure if I used the code tags properly below):

[SELECT Case_Tracker.[Case ID], Case_Tracker.Status, Case_Tracker.[Date Analyst Received], Case_Tracker.[1st Analyst]
FROM Case_Tracker
WHERE (((Case_Tracker.[Case ID]) Like "CR*") AND ((Case_Tracker.Status) Like "6") AND ((Case_Tracker.[Date Analyst Received]) Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate])) OR (((Case_Tracker.[Case ID]) Like "CR*") AND ((Case_Tracker.Status) Like "8") AND ((Case_Tracker.[Date Analyst Received]) Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate])) OR (((Case_Tracker.[Case ID]) Like "CR*") AND ((Case_Tracker.Status) Like "9") AND ((Case_Tracker.[Date Analyst Received]) Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate])) OR (((Case_Tracker.[Case ID]) Like "CR*") AND ((Case_Tracker.Status) Like "10") AND ((Case_Tracker.[Date Analyst Received]) Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate])) OR (((Case_Tracker.[Case ID]) Like "CR*") AND ((Case_Tracker.Status) Like "11") AND ((Case_Tracker.[Date Analyst Received]) Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate])) OR (((Case_Tracker.[Case ID]) Like "CR*") AND ((Case_Tracker.Status) Like "12") AND ((Case_Tracker.[Date Analyst Received]) Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]));/]


Any help would be greatly appreciated!

Thanks.

[ranman256]

The LIKE operator wants a wildcard. You did it right for like CR*
but not LIKE "6", etc.
you want LIKE "6*"

And to use form params, put the full path in the query criteria...
[startDate]= forms!frmFind!txtDate

[rrobinson]

Thank you for your response.

How would you enter that to prompt for a date range? This is what I entered and now 2 dialog boxes appear for Start and End Date. The first box has the "Start Date" title and then the 2nd one has "Forms!Date Range!StartDate"

Between [Start Date]=[Forms]![Date Range]![StartDate] And [End Date]=[Forms]![Date Range]![EndDate]

[nick404]

Is your form name "Date Range"? [[Forms]![YourFormName]![Start Date]] perhaps?
And I think for your like statement, like ranman says, you want it to either be with a wildcard or just say = "(your number)"

[rrobinson]

Thanks to both of you for the advice on the like...I changed it to = "(number)" since it's a static number pulling from a table.

Yes, that's the form name. I changed it to the criteria below and now get an error "The expression you entered contains invalid syntax...You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks."

Between [Start Date]=[[Forms]![Date Range]![StartDate]] And [End Date]=[[Forms]![Date Range]![EndDate]]

[nick404]

Okay, and just to verify "StartDate" and "EndDate" are the names for the fields in you form you are drawing this from? Sometimes I forget to name them correctly too. Ah, perhaps it is this. After your = place "" around the sequence. Quotations are typically requisite when using an = sign.
Between [Start Date] = "[Forms]![Date Range]![StartDate]" And [End Date] = "[Forms]![Date Range]![EndDate]"

[rrobinson]

Yes, those are the field names. I tried the following and it still prompts me using the "Enter parameter value" dialog box and not the form I specified but nothing is being pulled.

Between [Start Date]="[[Forms]![Date Range]![StartDate]]" And [End Date]="[[Forms]![Date Range]![EndDate]]"

[nick404]

huh, i suppose. In criteria of query try typing Between forms![Data Range]![StartDate] And forms![Data Range]![EndDate]
or some variation. For more info try this link on qbf's . https://support.microsoft.com/en-us/kb/304428

[rrobinson]

Thanks for the suggestions. That still didn't work. Now it doesn't prompt me at all, and it just goes to a blank table. I'll try the link to see if I have better luck.

[rrobinson]

Thanks Nick404! That article helped me bug out the macro that it was calling out.

[nick404]

Excellent! Hope things continue to run smoothly for you