Solved Query to select records within a date range. (1 Viewer)

HillTJ

Registered User.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
Hi,

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

Schedule: 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]),""))))

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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
TheDBguy, thanks, will give it a go & let you know.
 

HillTJ

Registered User.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
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]),""))));


Thanks.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:22
Joined
Jan 23, 2006
Messages
13,685
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
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
Local time
Yesterday, 21:22
Joined
Feb 28, 2001
Messages
19,710
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
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
14,958
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.
Local time
Yesterday, 15:22
Joined
Apr 1, 2019
Messages
393
Guys, Thanks, will give it a go & let you know.
 

Users who are viewing this thread

Top Bottom