Solved

Access Date Query

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

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 45

Expert Comment

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

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 15

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 15

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 15

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 15

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

828 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