Solved Query criteria to pull data for Next Month (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 18:01
Joined
Feb 19, 2013
Messages
16,553
Between DateSerial(Year(Date()), Month(Date()) + 1, 1)

that won't work - when month is December, the +1 will be 13, not 1
 

BDE1994

New member
Local time
Today, 13:01
Joined
Feb 21, 2020
Messages
10
Sorry, didn't realise you were not familiar with criteria

between dateadd("m",1,dateadd("d",-day(date),date)+1) and dateadd("m",2,dateadd("d",-day(date)+1,date))-1
"Data type mismatch in criteria expression."
 

ebs17

Well-known member
Local time
Today, 19:01
Joined
Feb 7, 2020
Messages
1,881
Code:
?DateSerial(Year(#12/17/2020#), Month(#12/17/2020#) + 1, 1)
01.01.2021
(My Access is a German).
DateSerial creates a correct date.

Eberhard
 
Last edited:

BDE1994

New member
Local time
Today, 13:01
Joined
Feb 21, 2020
Messages
10
Corrected criteria from # 7:
SQL:
WHERE [Next Due Date] Between DateSerial(Year(Date()), Month(Date()) + 1, 1)
                          And DateSerial(Year(Date()), Month(Date()) + 2, 0)

Use Date() instead of Date.
Jet-SQL can resolve and use functions (VBA, Access, userdefined) via the Expression Service, but not variable.

[Next Due Date] should of course have the data type Date.

Eberhard
this did not pull any data.
 

ebs17

Well-known member
Local time
Today, 19:01
Joined
Feb 7, 2020
Messages
1,881
Then there is no data filter applied accordingly.
Check your conditions and your implementation.

What I wrote was a complete WHERE part of a query, not just a criteria. Hopefully the inclined observer will recognize that.

Eberhard
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:01
Joined
Feb 19, 2013
Messages
16,553
this code produces this (UK date format), US will be month/day
?dateadd("m",1,dateadd("d",-day(date),date)+1)
01/03/2020
?dateadd("m",2,dateadd("d",-day(date)+1,date))-1
31/03/2020

perhaps you should show your whole query
 

ebs17

Well-known member
Local time
Today, 19:01
Joined
Feb 7, 2020
Messages
1,881
@CJ_London:
For use in VBA is it o.k.
For use in Jet-SQL Date() instead of Date .

Eberhard
 

BDE1994

New member
Local time
Today, 13:01
Joined
Feb 21, 2020
Messages
10
Thanks everyone for your help.. unfortunately I have patient information so i am unable to share my database or query.. I will just run a query and update it monthly to run between #date# and #date#. Thank you.
 

Users who are viewing this thread

Top Bottom