Solved

Access Date Query

Posted on 2016-11-17
28
65 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 17

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 50

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
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!

 
LVL 45

Expert Comment

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

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 17

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 50

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 17

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 50

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 50

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 17

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 17

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 45

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 45

Assisted Solution

by:aikimark
aikimark earned 75 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 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 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 300 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 50

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 50

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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