Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Between formula (beginning of month)

Posted on 2016-11-14
11
Medium Priority
?
36 Views
Last Modified: 2016-11-14
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
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 41886345
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41886349
you want the date range  8/1/2016  to  1/31/2017  is this correct?
0
 

Author Comment

by:pdvsa
ID: 41886365
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pdvsa
ID: 41886366
<I made the changes
I copied and pasted
0
 

Author Comment

by:pdvsa
ID: 41886369
<you want the date range  8/1/2016  to  1/31/2017  is this correct?
actually it is to 2/28/17
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41886383
change this

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

with

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

Author Comment

by:pdvsa
ID: 41886408
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41886414
how is your date formatted?

can you upload a copy of the table..
0
 

Author Comment

by:pdvsa
ID: 41886480
Rey, please see attached query in db.
format is short date.

thank you.
between.accdb
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 41886495
try doing a compact and repair of your DB

here test this... run QueryTest
between.accdb
0
 

Author Closing Comment

by:pdvsa
ID: 41886548
works perfectly.  I think I had some corruption as you suggested.   thank you sir...
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question