A query that shows records from now until the end of the month...

[insixt]

I'm doing a database that records jobs to be completed.

I want to be able to see what jobs are due for this month, what jobs are due next month and what jobs are due two months from now (seperate query for each of these). I need it to work forever and thus can't use set dates.

How would I go about doing this, I know I should use 'now' somewhere in the filter term but I don't know the rest!

Please help!

http://www.accessforums.net/editpost.php?do=editpost&p=12170

 

[JPaulo]

In an empty column of your query:

MonthRef: Format([YourFieldDate];"mm")

In criteria:
Format(Date();"mm")

Or
Format(Date();"mm")+1

Or
Format(Date();"mm")+2

 

[insixt]

edit - Cheers for your help!

 

[insixt]

Hi - just tried this and I'm getting an error message

"The expression you entered contains invalid syntax."

Any help on this?

 

[boblarson]

Hi - just tried this and I'm getting an error message

"The expression you entered contains invalid syntax."

Any help on this?

You probably need to replace the semi-colon ( with a comma (,) because of your regional settings.

 

[insixt]

Thanks boblarson! However, unfortunately now I'm getting this message.

'Data type mismatch in criteria expression.'

Any ideas?

 

[raskew]

Hi -

try

Between Date() and DateSerial(year(date()), month(date()) + 1, 0)

in your date field's criteria.

HTH - Bob

 

[boblarson]

However, that is not likely going to work because when you get to November it would fail (Month 11 + 2 = 13 and there is no month 13).

So, you would need:

On your Date Field Criteria (no new field needed):

Current Month:

Between Date() and DateSerial(Year(Date()), Month(Date())+1, 0)


Next Month:

Between DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date())),1) And DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date()))+1,0)

Next 2 Months:
Between DateSerial(Year(DateAdd("m",2,Date())),Month(DateAdd("m",2,Date())),1) And DateSerial(Year(DateAdd("m",2,Date())),Month(DateAdd("m",2,Date()))+1,0)

 

[boblarson]

Hi -

try

Between Date() and DateSerial(year(date()), month(date()) + 1, 0)

in your date field's criteria.

HTH - Bob

Darn, knew someone was going to do that while I was working out all of the others

 

[insixt]

Boblarson, Thank you so much! You've made my day with your help! Thanks to everybody else aswell .

 

[insixt]

Arrrggh - Sorry guys I thought this was solved but I didn't test it properly. The one for First Month that boblarson posted works fine. But with the other two I keep getting the message:

'The expression you entered contains invalid syntax - You may have entered an opeand without an operator.'

Any suggestions on how to fix this?

 

[JPaulo]

hi man, remove the space:

Next Month:

Between DateSerial(Year(DateAdd("m",1,Date())),Month(DateA dd("m",1,Date())),1) And DateSerial(Year(DateAdd("m",1,Date())),Month(DateA dd("m",1,Date()))+1,0)

Next 2 Months:
Between DateSerial(Year(DateAdd("m",2,Date())),Month(DateA dd("m",2,Date())),1) And DateSerial(Year(DateAdd("m",2,Date())),Month(DateA dd("m",2,Date()))+1,0)

Correct:


Next Month:

Between DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date())),1) And DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date()))+1,0)

Next 2 Months:
Between DateSerial(Year(DateAdd("m",2,Date())),Month(DateAdd("m",2,Date())),1) And DateSerial(Year(DateAdd("m",2,Date())),Month(DateAdd("m",2,Date()))+1,0)

 

[insixt]

That fixed it. Thanks so much!

 

[boblarson]

That fixed it. Thanks so much!

Yeah, I should have surrounded the code with code tags because the site here adds a space after about 80 characters. It is a bit of a strange bug that is known here.