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

x
?
Solved

Access Date Query

Posted on 2016-11-17
28
Medium Priority
?
85 Views
Last Modified: 2016-11-18
Dear Experts,

I'm struggling to filter a query to show records that either fall into last month, this month or the next 2 months (i.e. its November now, so I want to show records that fall into October, November, December & January). the field I am apply the filter to is [Bill Date] and I've put in a filter of >=DateSerial(Year(Date()),Month(Date())-1,1) And <DateSerial(Year(Date()),Month(Date())+2,1). But this returns nothing, can anyone help?  the SQL of the whole query is here:

SELECT AccountsQuery.Likelihood, MonthName([MonthOnly]) AS [Month], Format(Month([Bill Date]),"00") AS MonthOnly, Year([Bill Date]) & Format(Month([Bill Date]),"00") AS MonthNumber, AccountsQuery.ProjectID, AccountsQuery.Firm, [ProjectName] & " - " & [Phase Name] AS Details, AccountsQuery.Stakeholder, AccountsQuery.[Bill Date], ([Fee]) AS gggg, FormatCurrency([Fee]) AS Feef, FormatCurrency([Cos]) AS Cosf, FormatCurrency([Bottom Line]) AS [Bottom Linef], AccountsQuery.Notes, [MCIProj-Status].[Status Type], [MCIProj-Status].[Group Order]
FROM AccountsQuery LEFT JOIN [MCIProj-Status] ON AccountsQuery.Status = [MCIProj-Status].Status
WHERE (((AccountsQuery.[Bill Date])>=DateSerial(Year(Date()),Month(Date())-1,1) And (AccountsQuery.[Bill Date])<DateSerial(Year(Date()),Month(Date())+2,1)));
0
Comment
Question by:correlate
[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
  • 7
  • 7
  • 6
  • +2
28 Comments
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41891273
WHERE AccountsQuery.[BillDate] BETWEEN dateAdd("m",-1,Date()) AND  dateAdd("m",2,Date())

Open in new window

0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41891287
That will be:

WHERE (((AccountsQuery.[Bill Date])>=DateSerial(Year(Date()),Month(Date())-1,1)
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Date()),Month(Date())+3,0)));

/gustav
0
 

Author Comment

by:correlate
ID: 41891313
Thanks for these, unfortunately neither work, although the syntax looks all perfect, I'm wondering if it could be to do with the formatting of the field in the underlying table which is set to Date/Time & format "dd/mm/yyyy"?
0
Independent Software Vendors: 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!

 
LVL 46

Expert Comment

by:aikimark
ID: 41891351
Is your regional setting one with a dd/mm/yyyy setting?
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41891360
does this work better ?
WHERE AccountsQuery.[BillDate] BETWEEN #" & dateAdd("m",-1,Date()) &"# AND  #" & dateAdd("m",2,Date()) &"#""

Open in new window

0
 

Author Comment

by:correlate
ID: 41891364
Think so, here's a screenshot of the PC's region & Language settings...Time Settings
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41891369
if your settings are dd/mm/yyyy you have to tweak to this
WHERE AccountsQuery.[BillDate] BETWEEN #" & format(dateAdd("m",-1,Date()),"mm/dd/yyyy") &"# AND  #" & format(dateAdd("m",2,Date()),"mm/dd/yyyy") &"#""

Open in new window

0
 

Author Comment

by:correlate
ID: 41891377
Hi John,

I keep getting a syntax error (missing operator) with this, not sure what's missing though
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41891381
Please, please - you are both far out. The regional settings has nothing to do with this.

Your original method is correct with the modification I showed - if and if  [Bill Date] is a date field.
If it is text - which can be the only reason why your query doesn't work, convert it to date - either permanently (preferred) or like this:

WHERE DateValue(AccountsQuery.[Bill Date])>=DateSerial(Year(Date()),Month(Date())-1,1)
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Date()),Month(Date())+3,0);

/gustav
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41891385
usually the complain also points where the error is....if your query is in code transfer it to query editor to get a clearer view....if it's in a query and you use as list separator ";" then you need to change the commas "," to ";"
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41891398
Besides - John's method will never give you the desired results. Ran today, it would filter for 2016-10-17 to 2017-01-17 while you request is for 2016-10-01 to 2017-01-31.

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41891400
try this

 Where DateSerial(Year([AccountsQuery].[Bill Date]),Month([AccountsQuery].[Bill Date]),day([AccountsQuery].[Bill Date])) berween
 DateSerial(Year(Date()),Month(Date())-1,1) and DateSerial(Year(Date()),Month(Date())+3,0)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41891401
typo


try this

  Where DateSerial(Year([AccountsQuery].[Bill Date]),Month([AccountsQuery].[Bill Date]),day([AccountsQuery].[Bill Date])) between
  DateSerial(Year(Date()),Month(Date())-1,1) and DateSerial(Year(Date()),Month(Date())+3,0)
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41891415
Oh no, even more confusion added.

   DateSerial(Year([AccountsQuery].[Bill Date]),Month([AccountsQuery].[Bill Date]),day([AccountsQuery].[Bill Date]))

will return nothing but .... [Bill Date]

There are many way to complicate matters.

/gustav
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41891448
In Greece the regional settings are dd/mm/yyyy so my solution works just fine....fyi
0
 

Author Comment

by:correlate
ID: 41891451
I've stripped the query back to its basics and put an ascending sort on [Bill Date] - it doesn't sort properly which presumably implies a corruption somewhere?
Sort Problem
0
 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41891453
if there is corruption then its time for compact & repair...but probably you have a sort somewhere that is changing your order(index?)...move BillDate to be the very first column in your query
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41891456
@correlate

Is this a date field or a text field?
1
 

Author Comment

by:correlate
ID: 41891461
It's definitely a date field, but it sorts like a text field.  My only thinking is that this particular field in the query is populated by an iif statement, but all all the options in the statement take their data from date fields ( I went back through and checked each one)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41891467
@correlate
try creating a new query and place this

Field  Expr1:DateSerial(Year([Bill Date]),Month([Bill Date]),Day([Bill Date]))

Sort Ascending


see if you will get a correct listing order
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 300 total points
ID: 41891468
If you are looking at a query field that is the result of a Format() function, then you are looking at a TEXT field.  The Format() function always produces a text result, even if the original field is a date format.
0
 

Author Comment

by:correlate
ID: 41891474
Hi Rey, re the sort - that works all ok
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 41891482
It's definitely a date field, but it sorts like a text field.  

That is not possible.

My only thinking is that this particular field in the query is populated by an iif statement

Then your first statement has no value, as you now have an expression which you have created to return text.
This will, of course, sort as text, not as date.

but all all the options in the statement take their data from date fields

That doesn't matter.

To revert to have a date value to filter on, either correct the IIf expression to return a date value or use my previously posted solution:

WHERE DateValue(AccountsQuery.[Bill Date])>=DateSerial(Year(Date()),Month(Date())-1,1) 
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Date()),Month(Date())+3,0); 

Open in new window

/gustav
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 total points
ID: 41891494
@correlate,

did you try my post


   Where DateSerial(Year([AccountsQuery].[Bill Date]),Month([AccountsQuery].[Bill Date]),day([AccountsQuery].[Bill Date])) between
   DateSerial(Year(Date()),Month(Date())-1,1) and DateSerial(Year(Date()),Month(Date())+3,0)
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41891527
Rey, if [Bill Date] is a date (which is now appears not to be)

    DateSerial(Year([AccountsQuery].[Bill Date]),Month([AccountsQuery].[Bill Date]),day([AccountsQuery].[Bill Date]))

will return nothing but .... [Bill Date]

If it is text, it is just a more convoluted replacement for DateValue adding no improvement at all.

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41891552
@gustav,
I suggest that you try things first before posting something
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41891578
Oh, I noticed the missing DateValue. Couldn't you just have made a note on that?

So:
WHERE DateValue(AccountsQuery.[Bill Date])>=DateSerial(Year(Date()),Month(Date())-1,1) 
And DateValue(AccountsQuery.[Bill Date])<=DateSerial(Year(Date()),Month(Date())+3,0); 

Open in new window

/gustav
0
 

Author Closing Comment

by:correlate
ID: 41893628
Thanks for your help on this & useful tot know for the future re the way dates are handled in selection queries
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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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