pdvsa
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"),Mo nth("Date" )-3,1) And DateSerial(Year("Date"),Mo nth("Date" )+3,0)));
thank you
WHERE (((Import_FC_Archive.Date)
thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you want the date range 8/1/2016 to 1/31/2017 is this correct?
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.
<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.
ASKER
<I made the changes
I copied and pasted
I copied and pasted
ASKER
<you want the date range 8/1/2016 to 1/31/2017 is this correct?
actually it is to 2/28/17
actually it is to 2/28/17
change this
DateSerial(Year(Date()),Mo nth(Date() )+3,0)
with
DateSerial(Year(Date()),Mo nth(Date() )+4,0)
DateSerial(Year(Date()),Mo
with
DateSerial(Year(Date()),Mo
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.
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..
can you upload a copy of the table..
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
works perfectly. I think I had some corruption as you suggested. thank you sir...