Hmm, this should pull this month's dates: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...
Between DateSerial(Year(Date),Month(Date),1) And DateSerial(Year(Date),Month(Date)+1,0)
Between DateSerial(Year(Date),Month(Date)+1,1) And DateSerial(Year(Date),Month(Date)+2,0)
When i tried that it says "Data type mismatch in criteria expression."Hmm, this should pull this month's dates:
If so, then this should pull next month's dates:Code:Between DateSerial(Year(Date),Month(Date),1) And DateSerial(Year(Date),Month(Date)+1,0)
Hope that helps...Code:Between DateSerial(Year(Date),Month(Date)+1,1) And DateSerial(Year(Date),Month(Date)+2,0)
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.When i tried that it says "Data type mismatch in criteria expression."
Without any nulls (missing dates) in the data? Can you post a demo version of your db?I am still getting that error.
Labs | Diagnosis | Reoccurence | Next Due Date |
---|---|---|---|
TSH/FT4/Lipid | Hypothyroid/Hyperlipodemia | q 3 month | 03/04/2020 |
Valproic/CBC/AST/ALT | HRM | q 6 months | 06/23/2020 |
Glucose/Amylase | HRM | Annual | 06/23/2020 |
EKG | E78.4 | Annual | |
Blood/Body Fluid PreCautions | 09/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.This is a small section of my database, every person has a section like this that I pull from.
Tasks
Labs Diagnosis Reoccurence Next Due Date TSH/FT4/Lipid Hypothyroid/Hyperlipodemia q 3 month 03/04/2020Valproic/CBC/AST/ALT HRM q 6 months 06/23/2020Glucose/Amylase HRM Annual 06/23/2020EKG E78.4 Annual Blood/Body Fluid PreCautions 09/06/2018
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?Why does it work for my first query then to pull the current month if those same spots don't have due dates?
I have.. .but i am wanting the whole month, not just the first and last day of the month.have you tried the code suggested in post #5?
Sorry, didn't realise you were not familiar with criteriabut i am wanting the whole month
WHERE [Next Due Date] Between DateSerial(Year(Date()), Month(Date()) + 1, 1)
And DateSerial(Year(Date()), Month(Date()) + 2, 0)