Solved

Access Date Query

Posted on 2016-11-17
28
51 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
  • 7
  • 7
  • 6
  • +2
28 Comments
 
LVL 13

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 49

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 45

Expert Comment

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

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 13

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 49

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 13

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 49

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 49

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 13

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 13

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 49

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 49

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 49

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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