Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access query criteria syntax

Posted on 2012-08-23
13
Medium Priority
?
729 Views
Last Modified: 2012-08-24
I have a query with this criteria: Is Null Or <DateAdd("yyyy",-1,Date())

This returns records that are null or less than one year one year in the future, does anyone know how to change this to less than 6 months in the future?

Field format is Date/Time

Thanks
0
Comment
Question by:HKFuey
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 400 total points
ID: 38324274
Is Null Or <DateAdd("m",-6,Date())
0
 
LVL 1

Expert Comment

by:MarkVrenken
ID: 38324277
DateAdd("mm",-6,Date()) would be for 6 months. i don't understand your logic with the is null but i guess this is what you're asking?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38324284
Not that hard to understand. He wants all records that either have a null value or that, not having a null value, have a date inferior to one year in the future.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 28

Assisted Solution

by:burrcm
burrcm earned 400 total points
ID: 38324300
Is Null Or < DateAdd("m",+6,Date())

or just

< DateAdd("m",+6,Date())

Chris B
0
 

Author Comment

by:HKFuey
ID: 38324305
This query was to check for items never serviced or due for servicing within 6 months.

I already tried DateAdd("mm",-6,Date()) and it failed with error, 'Is Null Or <DateAdd("m",-6,Date())' DOES work but returns more records, I'm expecting less.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38324336
Actually, it should be +6, seeing as you want it in the future, but that would return even more results. You can try tweaking your values (especially trying +6), knowing that to add or subtract months you have to use "m". Otherwise, can you post some examples of values and expected results?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1200 total points
ID: 38324353
For never serviced or due in the future, are you trying to exclude records that are past due?


 Is Null Or BETWEEN Date() AND DateAdd("m",6,Date())

Or this

BETWEEN Date() AND DateAdd("m",6,Date())
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38324371
<<This query was to check for items never serviced or due for servicing within 6 months.>>

Excluding 'Past due' dates might not be quite right... is there some other field that needs to be cross-checked to determine whether or not an Item has been serviced?
0
 

Author Comment

by:HKFuey
ID: 38324372
Thanks for the (very rapid) help!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38324396
Glad to help out -

But I think burrcm's answer at http:#a38324300 might have been overlooked in the point split.  His response was the first to note that DateAdd("m",+6,Date()) was needed for a date 'in the future'.

You can hit the Request Attention button to reopen this...
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38324516
While you're at it, you don't have to divide the points evenly. I think that mbizup's answer should have more points for being the right one. You can do something like a 300/100/100 split or similar.
0
 

Author Closing Comment

by:HKFuey
ID: 38328229
Thanks again!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

571 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