Between formula (beginning of month)

Experts, I have a query with a WHERE condition to filter for the following criteria:  +-3 months from Date() but must be from beginning of the month (-3) to the end of the month(+3).  When I run, I am getting a data type mismatch error.  There might be a more simple way to do this than below.

WHERE (((Import_FC_Archive.Date) Between DateSerial(Year("Date"),Month("Date")-3,1) And DateSerial(Year("Date"),Month("Date")+3,0)));

thank you
pdvsaProject financeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
remove the "" in the Date and add ()  -- Date()  and add bracket to the Date field -- [Date]

WHERE (((Import_FC_Archive.[Date]) Between DateSerial(Year(Date()),Month(Date())-3,1) And DateSerial(Year(Date()),Month(Date())+3,0)));
0
 
Rey Obrero (Capricorn1)Commented:
you want the date range  8/1/2016  to  1/31/2017  is this correct?
0
 
pdvsaProject financeAuthor Commented:
HI Rey,

<You want the date range  8/1/2016  to  1/31/2017  is this correct?
Yes

I made the changes and no longer have that error but it returns 0 records.  There are quite a few records in the date range +-3 months.

Let me know what the next step is when yo have a sec.  thank you.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
pdvsaProject financeAuthor Commented:
<I made the changes
I copied and pasted
0
 
pdvsaProject financeAuthor Commented:
<you want the date range  8/1/2016  to  1/31/2017  is this correct?
actually it is to 2/28/17
0
 
Rey Obrero (Capricorn1)Commented:
change this

DateSerial(Year(Date()),Month(Date())+3,0)

with

DateSerial(Year(Date()),Month(Date())+4,0)
0
 
pdvsaProject financeAuthor Commented:
Ok I made the change to +4 as instructed above.
I still dont get any records returned.

fyi:  I can change the where condition to
Between Date()-90 And Date()+90
and many records are returned.
0
 
Rey Obrero (Capricorn1)Commented:
how is your date formatted?

can you upload a copy of the table..
0
 
pdvsaProject financeAuthor Commented:
Rey, please see attached query in db.
format is short date.

thank you.
between.accdb
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try doing a compact and repair of your DB

here test this... run QueryTest
between.accdb
0
 
pdvsaProject financeAuthor Commented:
works perfectly.  I think I had some corruption as you suggested.   thank you sir...
0
All Courses

From novice to tech pro — start learning today.