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

BDE1994

New member
Local time
Today, 07:30
Joined
Feb 21, 2020
Messages
10
I am needing to pull data for what's due next month. I don't want to have to update it every month, just want it to do it automatically. So today is 2/21/2020, i want it to show 3/1/20 to 3/31/20..
 

MickJav

AWF VIP
Local time
Today, 13:30
Joined
Nov 28, 2005
Messages
1,432
You could use Between #3/1/2020# And #3/31/2020# To limit a query.
 

bastanu

Registered User.
Local time
Today, 05:30
Joined
Apr 13, 2010
Messages
315
Attached is an old module I use to work with dates.

So in your case you would have Between FirstOfNextMonth(Date ()) AND LastOfNextMonth(Date()).

Cheers,
Vlad
 

Attachments

CJ_London

Super Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 19, 2013
Messages
11,808
first of next month can be determined by using this formula
dateadd("m",1,dateadd("d",-day(date),date)+1)

and the last day

dateadd("m",2,dateadd("d",-day(date)+1,date))-1
 

BDE1994

New member
Local time
Today, 07:30
Joined
Feb 21, 2020
Messages
10
None of these have worked.... here is what i have to pull the current month, Between DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1) And DateAdd("d",-1,DateAdd("m",1,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)))

i figured it would be easy to change it to pull the next month, but haven't figured it out...
 

theDBguy

I’m here to help
Local time
Today, 05:30
Joined
Oct 29, 2018
Messages
8,652
None of these have worked.... here is what i have to pull the current month, Between DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1) And DateAdd("d",-1,DateAdd("m",1,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)))

i figured it would be easy to change it to pull the next month, but haven't figured it out...
Hmm, this should pull this month's dates:
Code:
Between DateSerial(Year(Date),Month(Date),1) And DateSerial(Year(Date),Month(Date)+1,0)
If so, then this should pull next month's dates:
Code:
Between DateSerial(Year(Date),Month(Date)+1,1) And DateSerial(Year(Date),Month(Date)+2,0)
Hope that helps...
 

BDE1994

New member
Local time
Today, 07:30
Joined
Feb 21, 2020
Messages
10
Hmm, this should pull this month's dates:
Code:
Between DateSerial(Year(Date),Month(Date),1) And DateSerial(Year(Date),Month(Date)+1,0)
If so, then this should pull next month's dates:
Code:
Between DateSerial(Year(Date),Month(Date)+1,1) And DateSerial(Year(Date),Month(Date)+2,0)
Hope that helps...
When i tried that it says "Data type mismatch in criteria expression."
 

MickJav

AWF VIP
Local time
Today, 13:30
Joined
Nov 28, 2005
Messages
1,432
Try Between Cdate(DateSerial(Year(Date),Month(Date),1)) And Cdate(DateSerial(Year(Date),Month(Date)+1,0))


Click to expand...
 

theDBguy

I’m here to help
Local time
Today, 05:30
Joined
Oct 29, 2018
Messages
8,652
When i tried that it says "Data type mismatch in criteria expression."
Hi. If you got that error, it might mean you have a null value in your field. Please double check that and make sure you have dates in all your records.
 

BDE1994

New member
Local time
Today, 07:30
Joined
Feb 21, 2020
Messages
10
This is a small section of my database, every person has a section like this that I pull from.

Tasks

LabsDiagnosisReoccurenceNext Due Date
TSH/FT4/LipidHypothyroid/Hyperlipodemiaq 3 month
03/04/2020​
Valproic/CBC/AST/ALTHRMq 6 months
06/23/2020​
Glucose/AmylaseHRMAnnual
06/23/2020​
EKGE78.4Annual
Blood/Body Fluid PreCautions09/06/2018
 

theDBguy

I’m here to help
Local time
Today, 05:30
Joined
Oct 29, 2018
Messages
8,652
This is a small section of my database, every person has a section like this that I pull from.

Tasks

LabsDiagnosisReoccurenceNext Due Date
TSH/FT4/LipidHypothyroid/Hyperlipodemiaq 3 month
03/04/2020​
Valproic/CBC/AST/ALTHRMq 6 months
06/23/2020​
Glucose/AmylaseHRMAnnual
06/23/2020​
EKGE78.4Annual
Blood/Body Fluid PreCautions09/06/2018
So, if you're pulling the next due dates for next month, then that's why you were getting the type mismatch error becausee EKG and the next one don't have due dates. You could try adding Is Not Null to your WHERE clause, but I'm not sure if that's good enough.
 

BDE1994

New member
Local time
Today, 07:30
Joined
Feb 21, 2020
Messages
10
Why does it work for my first query then to pull the current month if those same spots don't have due dates?
 

theDBguy

I’m here to help
Local time
Today, 05:30
Joined
Oct 29, 2018
Messages
8,652
Why does it work for my first query then to pull the current month if those same spots don't have due dates?
Hi. I am not sure I can answer that question since I can't see what you're doing and your db. Are you able to share a copy of it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 19, 2013
Messages
11,808
have you tried the code suggested in post #5?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 19, 2013
Messages
11,808
but i am wanting the whole month
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
 

ebs17

Member
Local time
Today, 14:30
Joined
Feb 7, 2020
Messages
64
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
 

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

Top Bottom