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

BDE1994

New member
Local time
Today, 14:53
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..
 

Dreamweaver

Well-known member
Local time
Today, 19:53
Joined
Nov 28, 2005
Messages
2,466
You could use Between #3/1/2020# And #3/31/2020# To limit a query.
 

bastanu

AWF VIP
Local time
Today, 12:53
Joined
Apr 13, 2010
Messages
1,401
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

  • modWorkWithDates.zip
    3.5 KB · Views: 416

CJ_London

Super Moderator
Staff member
Local time
Today, 19:53
Joined
Feb 19, 2013
Messages
16,553
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, 14:53
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
Staff member
Local time
Today, 12:53
Joined
Oct 29, 2018
Messages
21,358
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, 14:53
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."
 

Dreamweaver

Well-known member
Local time
Today, 19:53
Joined
Nov 28, 2005
Messages
2,466
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
Staff member
Local time
Today, 12:53
Joined
Oct 29, 2018
Messages
21,358
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, 14:53
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
Staff member
Local time
Today, 12:53
Joined
Oct 29, 2018
Messages
21,358
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, 14:53
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
Staff member
Local time
Today, 12:53
Joined
Oct 29, 2018
Messages
21,358
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, 19:53
Joined
Feb 19, 2013
Messages
16,553
have you tried the code suggested in post #5?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:53
Joined
Feb 19, 2013
Messages
16,553
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

Well-known member
Local time
Today, 20:53
Joined
Feb 7, 2020
Messages
1,881
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

Top Bottom