Access Date Query

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)));
correlateAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
@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
 
John TsioumprisSoftware & Systems EngineerCommented:
WHERE AccountsQuery.[BillDate] BETWEEN dateAdd("m",-1,Date()) AND  dateAdd("m",2,Date())

Open in new window

0
 
Gustav BrockCIOCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
correlateAuthor Commented:
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
 
aikimarkCommented:
Is your regional setting one with a dd/mm/yyyy setting?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
does this work better ?
WHERE AccountsQuery.[BillDate] BETWEEN #" & dateAdd("m",-1,Date()) &"# AND  #" & dateAdd("m",2,Date()) &"#""

Open in new window

0
 
correlateAuthor Commented:
Think so, here's a screenshot of the PC's region & Language settings...Time Settings
0
 
John TsioumprisSoftware & Systems EngineerCommented:
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
 
correlateAuthor Commented:
Hi John,

I keep getting a syntax error (missing operator) with this, not sure what's missing though
0
 
Gustav BrockCIOCommented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
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
 
Gustav BrockCIOCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Gustav BrockCIOCommented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
In Greece the regional settings are dd/mm/yyyy so my solution works just fine....fyi
0
 
correlateAuthor Commented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
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
 
aikimarkCommented:
@correlate

Is this a date field or a text field?
1
 
correlateAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
@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
 
aikimarkConnect With a Mentor Commented:
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
 
correlateAuthor Commented:
Hi Rey, re the sort - that works all ok
0
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
Gustav BrockCIOCommented:
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
 
Rey Obrero (Capricorn1)Commented:
@gustav,
I suggest that you try things first before posting something
0
 
Gustav BrockCIOCommented:
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
 
correlateAuthor Commented:
Thanks for your help on this & useful tot know for the future re the way dates are handled in selection queries
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.