Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you want the date range  8/1/2016  to  1/31/2017  is this correct?
Avatar of pdvsa

ASKER

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.
Avatar of pdvsa

ASKER

<I made the changes
I copied and pasted
Avatar of pdvsa

ASKER

<you want the date range  8/1/2016  to  1/31/2017  is this correct?
actually it is to 2/28/17
change this

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

with

DateSerial(Year(Date()),Month(Date())+4,0)
Avatar of pdvsa

ASKER

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.
how is your date formatted?

can you upload a copy of the table..
Avatar of pdvsa

ASKER

Rey, please see attached query in db.
format is short date.

thank you.
between.accdb
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa

ASKER

works perfectly.  I think I had some corruption as you suggested.   thank you sir...