Solved Date Range parameter criteria Query not working right (1 Viewer)

Fstetson

New member
Local time
Today, 08:09
Joined
Nov 12, 2020
Messages
8
Good Day all,

I have a unique situation where I have set up a series of reports for different areas to show data between user input dates. The uniqueness comes with the field (in the report query). I am applying the parameter criteria of Between [Enter the Start Date] And [Enter the End Date] in the field I have named Event Date, within a custom nested IIF statement. I cannot seem to be able to get the criteria to filter for the date range. I attach a Text FILE with the SQL Statements and screen shots. Trying to be brief as possible in forum.

If someone could please advise on how to do this right...I would appreciate it greatly. . I have found nothing online through google/bing searches. Has to be something simple I am missing, or maybe not. My database is foundationally based on Allen Browne's Recurring Task DB.



Thank you all so much!
Fred
 

Attachments

  • DataResults.png
    DataResults.png
    58.5 KB · Views: 196
  • FrmEqEvent.png
    FrmEqEvent.png
    74.2 KB · Views: 296
  • QueryLayout.png
    QueryLayout.png
    25.3 KB · Views: 308
  • ParameterIssue.txt
    3.7 KB · Views: 337

theDBguy

I’m here to help
Staff member
Local time
Today, 08:09
Joined
Oct 29, 2018
Messages
21,358
Hi Fred. Welcome to AWF!

Just a guess but try forcing the parameter input into date values by using something like this in your query's SQL statement.

...Between CDate([Enter Start Date]) And CDate([Enter End Date])

Hope that helps...
 

Fstetson

New member
Local time
Today, 08:09
Joined
Nov 12, 2020
Messages
8
Hi Fred. Welcome to AWF!

Just a guess but try forcing the parameter input into date values by using something like this in your query's SQL statement.

...Between CDate([Enter Start Date]) And CDate([Enter End Date])

Hope that helps...
Should that be in the SELECT, FROM, WHERE portion of the statement of the SQL?
 

Fstetson

New member
Local time
Today, 08:09
Joined
Nov 12, 2020
Messages
8
That's in the WHERE Clause of the SQL statement.
Okay, getting a syntax error.... here is the where statement
WHERE (Between CDate([Enter Start Date]) And CDate([Enter End Date]))
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:09
Joined
Oct 29, 2018
Messages
21,358
Okay, getting a syntax error.... here is the where statement
WHERE (Between CDate([Enter Start Date]) And CDate([Enter End Date]))
Please post the entire SQL statement. The syntax error might be elsewhere.
 

Fstetson

New member
Local time
Today, 08:09
Joined
Nov 12, 2020
Messages
8
Eureka, your solution worked, I put your suggest statement into the criteria row iof the intended field and it works. corrected the SQL itself to show as;
WHERE (((IIf(tblEquipEventException.EventID Is Null,IIf(([qryEquipEventCartesian].PeriodTypeID Is Null) Or ([qryEquipEventCartesian].PeriodFreq Is Null) Or ([qryEquipEventCartesian].InstanceID Is Null),[qryEquipEventCartesian].EventStart,DateAdd([qryEquipEventCartesian].PeriodTypeID,[qryEquipEventCartesian].InstanceID*[qryEquipEventCartesian].PeriodFreq,[qryEquipEventCartesian].EventStart)),IIf(tblEquipEventException.IsCanned,Null,tblEquipEventException.InstanceDate))) Between CDate([Enter Start Date]) And CDate([Enter End Date])))

It required the Cartesian statement to work. OMG, I cannot thank you enough!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:09
Joined
Oct 29, 2018
Messages
21,358
Eureka, your solution worked, I put your suggest statement into the criteria row iof the intended field and it works. corrected the SQL itself to show as;
WHERE (((IIf(tblEquipEventException.EventID Is Null,IIf(([qryEquipEventCartesian].PeriodTypeID Is Null) Or ([qryEquipEventCartesian].PeriodFreq Is Null) Or ([qryEquipEventCartesian].InstanceID Is Null),[qryEquipEventCartesian].EventStart,DateAdd([qryEquipEventCartesian].PeriodTypeID,[qryEquipEventCartesian].InstanceID*[qryEquipEventCartesian].PeriodFreq,[qryEquipEventCartesian].EventStart)),IIf(tblEquipEventException.IsCanned,Null,tblEquipEventException.InstanceDate))) Between CDate([Enter Start Date]) And CDate([Enter End Date])))

It required the Cartesian statement to work. OMG, I cannot thank you enough!
Okay, glad to hear you got it sorted out. Good luck with your project.
 

Fstetson

New member
Local time
Today, 08:09
Joined
Nov 12, 2020
Messages
8
I'll post a link here when completed, as it will be free to the general public anyway.
 

Users who are viewing this thread

Top Bottom