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

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
Ok Guys, I had another go. Still get the same error as in post 6. If I delete the "" around all the instances of [Date] & run the query it, instead asks me for a date.

Query reproduced below;

SELECT Qry_Scheduled_Inspections_Next_Month.Equipment_Desc, Qry_Scheduled_Inspections_Next_Month.Schedule
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\#")));

Appreciate your input.
 

Micron

AWF VIP
Local time
Today, 02:53
Joined
Oct 20, 2018
Messages
3,148
Did you see post 18 re "Date" which you still have in that last sql example?
 

theDBguy

I’m here to help
Local time
Yesterday, 23:53
Joined
Oct 29, 2018
Messages
10,768
Ok Guys, I had another go. Still get the same error as in post 6. If I delete the "" around all the instances of [Date] & run the query it, instead asks me for a date.

Query reproduced below;

SELECT Qry_Scheduled_Inspections_Next_Month.Equipment_Desc, Qry_Scheduled_Inspections_Next_Month.Schedule
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\#")));

Appreciate your input.
Hi. Try using Date(), with parens, then.
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
Micron, Yes I did, after deleting the " around date, upon running the query I was asked to 'enter date'. Will have another go to confirm that I didn't miss any.
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
theDBguy, I don't know what you mean. I'd appreciate an explanation. Thanks
 

Micron

AWF VIP
Local time
Today, 02:53
Joined
Oct 20, 2018
Messages
3,148
I was asked to 'enter date'
That means the word is being interpreted as a parameter that Access cannot resolve, thus a prompt for its value. If you want to use the current date, depending on how, you either use Date or Date() but never [Date] - which would be a field named "Date" - a no-no because it's a reserved word. Also, never "Date" because that is a string, which cannot refer to a field by that name, nor the Date() function. Not sure what you mean by checking to see if you "missed any" because your example has several cases. Here are just 2:

Format(DateSerial(Year("Date"),Month("Date")
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
Micron, if you look at my original post, i have a query that includes a calculated date column called 'schedule' it is populated by a calculation using dateadd to arrive at a future date. I then have been filtering using criteria<Date() to filter out all occurances that have a date>than todays date, that is 'overdue'. This all works fine, but now i wish to generate a report for all dates due the following month. Seems simple, but i've been unsuccessful. Should i replace date() for all occurances of 'date' in my criteria & have another go? Appreciate your time.
 

theDBguy

I’m here to help
Local time
Yesterday, 23:53
Joined
Oct 29, 2018
Messages
10,768
theDBguy, I don't know what you mean. I'd appreciate an explanation. Thanks
This is what I meant. What happens when you try it this way?
SQL:
SELECT Qry_Scheduled_Inspections_Next_Month.Equipment_Desc, Qry_Scheduled_Inspections_Next_Month.Schedule
FROM Qry_Scheduled_Inspections_Next_Month
WHERE (((Qry_Scheduled_Inspections_Next_Month.Schedule) Between Format(DateSerial(Year(Date()),Month(Date())+1,1),"\#yyyy-mm-dd\#") And Format(DateSerial(Year(Date()),Month(Date())+2,0),"\#yyyy-mm-dd\#")));
 

Micron

AWF VIP
Local time
Today, 02:53
Joined
Oct 20, 2018
Messages
3,148
Should i replace date() for all occurances of 'date' in my criteria & have another go?
Waiting to see what you make of dbg's last post as I agree with either Date() or Date but not [Date] and not "Date"
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
Thanks for clarifying this, will give it a go but won't be till after the weekend. Will certainly let you know. Cheers.
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
Guys, substituted date() as recommended. Query did not crash. Will test it out but I think all will be fine now. Thanks to all whom assisted me.
 

theDBguy

I’m here to help
Local time
Yesterday, 23:53
Joined
Oct 29, 2018
Messages
10,768
Guys, substituted date() as recommended. Query did not crash. Will test it out but I think all will be fine now. Thanks to all whom assisted me.
Hi. Congratulations! Let us know how it goes.
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
Hi, I had to change the syntax a bit to;
Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date())+2,1)-1
Seems to work fine now. Cheers.
 

theDBguy

I’m here to help
Local time
Yesterday, 23:53
Joined
Oct 29, 2018
Messages
10,768
Hi, I had to change the syntax a bit to;
Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date())+2,1)-1
Seems to work fine now. Cheers.
Hmm, not sure your change/edit was necessary. It should have been just:
Code:
DateSerial(Year(Date()),Month(Date())+2,0)
Like I showed you earlier.
 

HillTJ

Registered User.
Local time
Yesterday, 19:53
Joined
Apr 1, 2019
Messages
307
theDBguy, Yep, I arrived at the same conclusion a little while ago!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom