Solved Query to select records within a date range. (2 Viewers)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
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, 04:58
Joined
Oct 20, 2018
Messages
3,476
Did you see post 18 re "Date" which you still have in that last sql example?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:58
Joined
Oct 29, 2018
Messages
21,357
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

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
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

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
theDBguy, I don't know what you mean. I'd appreciate an explanation. Thanks
 

Micron

AWF VIP
Local time
Today, 04:58
Joined
Oct 20, 2018
Messages
3,476
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

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
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
Staff member
Local time
Today, 01:58
Joined
Oct 29, 2018
Messages
21,357
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, 04:58
Joined
Oct 20, 2018
Messages
3,476
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

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
Thanks for clarifying this, will give it a go but won't be till after the weekend. Will certainly let you know. Cheers.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
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
Staff member
Local time
Today, 01:58
Joined
Oct 29, 2018
Messages
21,357
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

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
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
Staff member
Local time
Today, 01:58
Joined
Oct 29, 2018
Messages
21,357
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

To train a dog, first know more than the dog..
Local time
Yesterday, 21:58
Joined
Apr 1, 2019
Messages
712
theDBguy, Yep, I arrived at the same conclusion a little while ago!
 

Users who are viewing this thread

Top Bottom