# SolvedQuery to select records within a date range. (1 Viewer)

#### HillTJ

##### Registered User.
Hi,

I have a query that calculates a future inspection date as follows;

Where MaxofCalibration_Unit & MaxOfAnniversary_Date come from another query that gets the latest record by date. So I'm really calculating the next occurrence. This works fine & when I use a sort criteria of <Date() it filters out Overdue Inspections. What I'd like to do is run a query that allows me to filter records that have an inspection date within the next "Rolling"month.

I found this ; Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1 .I guess I have to substitute each incidence of [SalesDate] for my equation above. Is their an easier way?

Appreciate the feedback.

#### theDBguy

##### I’m here to help
Staff member
Hi. What do you mean by "Rolling" month? For example, if today is May 26, 2020, are you talking about from June 1, 2020 to June 30, 2020?

#### HillTJ

##### Registered User.
theDBguy, thanks for the prompt response. For example this month is May, I would like to look at Junes Calibrations. My aim is to give myself notice of what's coming up next month. If that makes sense..

#### theDBguy

##### I’m here to help
Staff member
theDBguy, thanks for the prompt response. For example this month is May, I would like to look at Junes Calibrations. My aim is to give myself notice of what's coming up next month. If that makes sense..
In that case, you could try using the DateSerial() function. For example,

Between DateSerial(Year(Date),Month(Date)+1,1) And DateSerial(Year(Date),Month(Date)+2,0)

#### HillTJ

##### Registered User.
TheDBguy, thanks, will give it a go & let you know.

#### HillTJ

##### Registered User.
theDBguy, so I copied your equation into the criteria of "schedule" column, that normally returns a date in the format Day/Month/Year & got a "Data Type Mismatch" error! Is this because of the date format?

#### theDBguy

##### I’m here to help
Staff member
theDBguy, so I copied your equation into the criteria of "schedule" column, that normally returns a date in the format Day/Month/Year & got a "Data Type Mismatch" error! Is this because of the date format?
In that case, you could try it this way:
Code:
``Between Format(DateSerial(Year(Date),Month(Date)+1,1),"\#dd-mm-yyyy\#") And Format(DateSerial(Year(Date),Month(Date)+2,0),"\#dd-mm-yyyy\#")``

#### HillTJ

##### Registered User.
theDBguy, thanks for the prompt response. I get the same error after copying the code into the criteria of the "Schedule" column in my query. In my original post I'd copied the actual calculation I do to generate the scheduled date (for the next occurrence), if this helps. I can also successfully filter overdue scheduled tasks by using <date() in the criteria for that column in the query. Appreciate it.

#### theDBguy

##### I’m here to help
Staff member
theDBguy, thanks for the prompt response. I get the same error after copying the code into the criteria of the "Schedule" column in my query. In my original post I'd copied the actual calculation I do to generate the scheduled date (for the next occurrence), if this helps. I can also successfully filter overdue scheduled tasks by using <date() in the criteria for that column in the query. Appreciate it.
Hi. Can you post the new SQL of your query? Thanks.

#### HillTJ

##### Registered User.
theDBGuy, see below;

SELECT TBL_Equipment.Equipment_Desc, [Qry_Inspect_Record _Test].MaxOfCalibration_Unit, TBL_Equipment.Serial_ID, TBL_Equipment.SiteID, [Qry_Inspect_Record _Test].MaxOfAnniversary_Date, Nz(DLookUp("Site_Name","TBL_Site","[SiteID]=" & [TBL_Equipment]![siteID]),0) AS Site_Name, TBL_Equipment.LocationID, DLookUp("Location","TBL_Location","[LocationID]=" & [TBL_Equipment]![LocationID]) AS Location_Name, TBL_Equipment.CategoryID, Nz(DLookUp("Category","TBL_Category","[CategoryID]=" & [TBL_Equipment]![CategoryID]),0) AS Category, TBL_Equipment.[Equipment_Active(Y/N)], TBL_Condition.Condition, TBL_Calibration_Frequency.Frequency_Desc, TBL_Condition.[Condition_Active(YN)], TBL_Calibration_Frequency.[Frequency_Active(Y/N)], IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=1,DateAdd("yyyy",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=2,DateAdd("m",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=3,DateAdd("m",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),IIf([Qry_Inspect_Record _Test]![MaxOfCalibration_Frequency]=4,DateAdd("d",[MaxofCalibration_Unit],[MaxOfAnniversary_Date]),"")))) AS Schedule, [Qry_Inspect_Record _Test].MaxOfTag_Number, DLookUp("Notes","TBL_Measurement","[TBL_Measurement]![InspectID]=" & Nz([MaxOfInspectID],0)) AS Notes
FROM ((TBL_Equipment INNER JOIN [Qry_Inspect_Record _Test] ON (TBL_Equipment.EquipmentID = [Qry_Inspect_Record _Test].EquipmentID) AND (TBL_Equipment.EquipmentID = [Qry_Inspect_Record _Test].EquipmentID)) INNER JOIN TBL_Condition ON [Qry_Inspect_Record _Test].MaxOfCondition = TBL_Condition.ConditionID) INNER JOIN TBL_Calibration_Frequency ON [Qry_Inspect_Record _Test].MaxOfCalibration_Frequency = TBL_Calibration_Frequency.Calibration_Frequency

Thanks.

#### jdraw

##### Super Moderator
Staff member
I put the sql thru the Poor SQL formatter (it may help with readability)
Code:
``````SELECT TBL_Equipment.Equipment_Desc
,[Qry_Inspect_Record _Test].MaxOfCalibration_Unit
,TBL_Equipment.Serial_ID
,TBL_Equipment.SiteID
,[Qry_Inspect_Record _Test].MaxOfAnniversary_Date
,Nz(DLookUp("Site_Name", "TBL_Site", "[SiteID]=" & [TBL_Equipment] ! [siteID]), 0) AS Site_Name
,TBL_Equipment.LocationID
,DLookUp("Location", "TBL_Location", "[LocationID]=" & [TBL_Equipment] ! [LocationID]) AS Location_Name
,TBL_Equipment.CategoryID
,Nz(DLookUp("Category", "TBL_Category", "[CategoryID]=" & [TBL_Equipment] ! [CategoryID]), 0) AS Category
,TBL_Equipment.[Equipment_Active(Y/N)]
,TBL_Condition.Condition
,TBL_Calibration_Frequency.Frequency_Desc
,TBL_Condition.[Condition_Active(YN)]
,TBL_Calibration_Frequency.[Frequency_Active(Y/N)]
,IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 1, DateAdd("yyyy", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 2, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 3, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 4, DateAdd("d", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), "")))) AS Schedule
,[Qry_Inspect_Record _Test].MaxOfTag_Number
,DLookUp("Notes", "TBL_Measurement", "[TBL_Measurement]![InspectID]=" & Nz([MaxOfInspectID], 0)) AS Notes
FROM (
(
TBL_Equipment INNER JOIN [Qry_Inspect_Record _Test] ON (TBL_Equipment.EquipmentID = [Qry_Inspect_Record _Test].EquipmentID)
AND (TBL_Equipment.EquipmentID = [Qry_Inspect_Record _Test].EquipmentID)
) INNER JOIN TBL_Condition ON [Qry_Inspect_Record _Test].MaxOfCondition = TBL_Condition.ConditionID
)
INNER JOIN TBL_Calibration_Frequency ON [Qry_Inspect_Record _Test].MaxOfCalibration_Frequency = TBL_Calibration_Frequency.Calibration_Frequency
WHERE (
((TBL_Condition.[Condition_Active(YN)]) = Yes)
AND ((TBL_Calibration_Frequency.[Frequency_Active(Y/N)]) = Yes)
AND (
(IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 1, DateAdd("yyyy", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 2, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 3, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 4, DateAdd("d", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), ""))))) BETWEEN Format(DateSerial(Year("Date"), Month("Date") + 1, 1), "\#dd-mm-yyyy\#")
AND Format(DateSerial(Year("Date"), Month("Date") + 2, 0), "\#dd-mm-yyyy\#")
)
)
ORDER BY IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 1, DateAdd("yyyy", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 2, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 3, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 4, DateAdd("d", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), ""))));``````

#### theDBguy

##### I’m here to help
Staff member
I put the sql thru the Poor SQL formatter (it may help with readability)
Code:
``````SELECT TBL_Equipment.Equipment_Desc
,[Qry_Inspect_Record _Test].MaxOfCalibration_Unit
,TBL_Equipment.Serial_ID
,TBL_Equipment.SiteID
,[Qry_Inspect_Record _Test].MaxOfAnniversary_Date
,Nz(DLookUp("Site_Name", "TBL_Site", "[SiteID]=" & [TBL_Equipment] ! [siteID]), 0) AS Site_Name
,TBL_Equipment.LocationID
,DLookUp("Location", "TBL_Location", "[LocationID]=" & [TBL_Equipment] ! [LocationID]) AS Location_Name
,TBL_Equipment.CategoryID
,Nz(DLookUp("Category", "TBL_Category", "[CategoryID]=" & [TBL_Equipment] ! [CategoryID]), 0) AS Category
,TBL_Equipment.[Equipment_Active(Y/N)]
,TBL_Condition.Condition
,TBL_Calibration_Frequency.Frequency_Desc
,TBL_Condition.[Condition_Active(YN)]
,TBL_Calibration_Frequency.[Frequency_Active(Y/N)]
,IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 1, DateAdd("yyyy", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 2, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 3, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 4, DateAdd("d", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), "")))) AS Schedule
,[Qry_Inspect_Record _Test].MaxOfTag_Number
,DLookUp("Notes", "TBL_Measurement", "[TBL_Measurement]![InspectID]=" & Nz([MaxOfInspectID], 0)) AS Notes
FROM (
(
TBL_Equipment INNER JOIN [Qry_Inspect_Record _Test] ON (TBL_Equipment.EquipmentID = [Qry_Inspect_Record _Test].EquipmentID)
AND (TBL_Equipment.EquipmentID = [Qry_Inspect_Record _Test].EquipmentID)
) INNER JOIN TBL_Condition ON [Qry_Inspect_Record _Test].MaxOfCondition = TBL_Condition.ConditionID
)
INNER JOIN TBL_Calibration_Frequency ON [Qry_Inspect_Record _Test].MaxOfCalibration_Frequency = TBL_Calibration_Frequency.Calibration_Frequency
WHERE (
((TBL_Condition.[Condition_Active(YN)]) = Yes)
AND ((TBL_Calibration_Frequency.[Frequency_Active(Y/N)]) = Yes)
AND (
(IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 1, DateAdd("yyyy", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 2, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 3, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 4, DateAdd("d", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), ""))))) BETWEEN Format(DateSerial(Year("Date"), Month("Date") + 1, 1), "\#dd-mm-yyyy\#")
AND Format(DateSerial(Year("Date"), Month("Date") + 2, 0), "\#dd-mm-yyyy\#")
)
)
ORDER BY IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 1, DateAdd("yyyy", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 2, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 3, DateAdd("m", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), IIf([Qry_Inspect_Record _Test] ! [MaxOfCalibration_Frequency] = 4, DateAdd("d", [MaxofCalibration_Unit], [MaxOfAnniversary_Date]), ""))));``````
@jdraw Thanks! @HillTJ Doesn't look like the Between part is in the right place, but it's hard to tell. If you take it out for now, does the query return all records? If so, you might just create a new query based on that query, so you can apply a single criteria. For example:
SQL:
``SELECT * FROM QueryName WHERE ColumnName Between DateSerial(...) And DateSerial(...)``
Hope that makes sense...

#### HillTJ

##### Registered User.
theDBguy, thanks for sticking with me. If I remove all of the code pasted from Post #7, the query shows all records. Similarly if I replace it with<date() that returns the correct records as well.

#### theDBguy

##### I’m here to help
Staff member
theDBguy, thanks for sticking with me. If I remove all of the code pasted from Post #7, the query shows all records. Similarly if I replace it with<date() that returns the correct records as well.
Hi. Just to be clear, I just wanted you to take out the Between part and then apply it using a new query. Did you try that?

#### HillTJ

##### Registered User.
theDBguy, no I didn't I just deleted the "Between" criteria & re-ran the existing query. So now I'll create a new query, then drag in the "old" query(Minus the between criteria), then apply the "between" line Yes?. Sorry, I'm a bit slow.

#### theDBguy

##### I’m here to help
Staff member
theDBguy, no I didn't I just deleted the "Between" criteria & re-ran the existing query. So now I'll create a new query, then drag in the "old" query(Minus the between criteria), then apply the "between" line Yes?. Sorry, I'm a bit slow.
Yes, let's try that first, just to see if it works. You can then post the SQL for that new query.

#### HillTJ

##### Registered User.
theDBguy,

See attached.

SELECT Qry_Scheduled_Inspections_Next_Month.Equipment_Desc, Qry_Scheduled_Inspections_Next_Month.MaxOfCalibration_Unit, Qry_Scheduled_Inspections_Next_Month.Serial_ID, Qry_Scheduled_Inspections_Next_Month.SiteID, Qry_Scheduled_Inspections_Next_Month.MaxOfAnniversary_Date, Qry_Scheduled_Inspections_Next_Month.Site_Name, Qry_Scheduled_Inspections_Next_Month.LocationID, Qry_Scheduled_Inspections_Next_Month.Location_Name, Qry_Scheduled_Inspections_Next_Month.CategoryID, Qry_Scheduled_Inspections_Next_Month.Category, Qry_Scheduled_Inspections_Next_Month.[Equipment_Active(Y/N)], Qry_Scheduled_Inspections_Next_Month.Condition, Qry_Scheduled_Inspections_Next_Month.Frequency_Desc, Qry_Scheduled_Inspections_Next_Month.[Condition_Active(YN)], Qry_Scheduled_Inspections_Next_Month.[Frequency_Active(Y/N)], Qry_Scheduled_Inspections_Next_Month.Schedule, Qry_Scheduled_Inspections_Next_Month.MaxOfTag_Number, Qry_Scheduled_Inspections_Next_Month.Notes
FROM Qry_Scheduled_Inspections_Next_Month
WHERE (((Qry_Scheduled_Inspections_Next_Month.Schedule) Between Format(DateSerial(Year("Date"),Month("Date")+1,1),"\#dd-mm-yyyy\#") And Format(DateSerial(Year("Date"),Month("Date")+2,0),"\#dd-mm-yyyy\#")));

I get the same "Type Mismatch" error.

#### The_Doc_Man

##### Immoderate Moderator
Staff member
In this statement I have highlighted two of the most probable culprits for the type mismatch.

Format(DateSerial(Year("Date"),Month("Date")+1,1),"\#dd-mm-yyyy\#")

In that element, "Date" (as a quoted argument) can ONLY be interpreted as a quoted string, which is datatype STRING. But the Year and Month functions don't want strings - they want an input date. I didn't bother to highlight the other place where you did the same thing.

That argument could look like Date() (without quotes) if you meant today's date, or it could look like #some-date-constant# if you wanted it for a fixed date. Other punctuation would apply if that date came from a text box or other control, or if it came from a recordset somewhere.

#### theDBguy

##### I’m here to help
Staff member
theDBguy,

See attached.

SELECT Qry_Scheduled_Inspections_Next_Month.Equipment_Desc, Qry_Scheduled_Inspections_Next_Month.MaxOfCalibration_Unit, Qry_Scheduled_Inspections_Next_Month.Serial_ID, Qry_Scheduled_Inspections_Next_Month.SiteID, Qry_Scheduled_Inspections_Next_Month.MaxOfAnniversary_Date, Qry_Scheduled_Inspections_Next_Month.Site_Name, Qry_Scheduled_Inspections_Next_Month.LocationID, Qry_Scheduled_Inspections_Next_Month.Location_Name, Qry_Scheduled_Inspections_Next_Month.CategoryID, Qry_Scheduled_Inspections_Next_Month.Category, Qry_Scheduled_Inspections_Next_Month.[Equipment_Active(Y/N)], Qry_Scheduled_Inspections_Next_Month.Condition, Qry_Scheduled_Inspections_Next_Month.Frequency_Desc, Qry_Scheduled_Inspections_Next_Month.[Condition_Active(YN)], Qry_Scheduled_Inspections_Next_Month.[Frequency_Active(Y/N)], Qry_Scheduled_Inspections_Next_Month.Schedule, Qry_Scheduled_Inspections_Next_Month.MaxOfTag_Number, Qry_Scheduled_Inspections_Next_Month.Notes
FROM Qry_Scheduled_Inspections_Next_Month
WHERE (((Qry_Scheduled_Inspections_Next_Month.Schedule) Between Format(DateSerial(Year("Date"),Month("Date")+1,1),"\#dd-mm-yyyy\#") And Format(DateSerial(Year("Date"),Month("Date")+2,0),"\#dd-mm-yyyy\#")));

I get the same "Type Mismatch" error.
Hi. I agree with @The_Doc_Man, and I was expecting you to simply do a simple query like:
SQL:
``SELECT * FROM QueryName WHERE Schedule Between...``

#### HillTJ

##### Registered User.
Guys, Thanks, will give it a go & let you know.

Replies
7
Views
180
Replies
19
Views
291
Replies
3
Views
210
Replies
7
Views
164
Replies
12
Views
516