Solved Query to select records within a date range.

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.
 
Did you see post 18 re "Date" which you still have in that last sql example?
 
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.
 
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.
 
theDBguy, I don't know what you mean. I'd appreciate an explanation. Thanks
 
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")
 
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 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\#")));
 
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"
 
Thanks for clarifying this, will give it a go but won't be till after the weekend. Will certainly let you know. Cheers.
 
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.
 
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.
 
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.
 
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.
 
theDBguy, Yep, I arrived at the same conclusion a little while ago!
 

Users who are viewing this thread

Back
Top Bottom