Tom Crowfoot
asked on
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()),M onth(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(Da te()),Mont h(Date())- 1,1) And (AccountsQuery.[Bill Date])<DateSerial(Year(Dat e()),Month (Date())+2 ,1)));
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()),
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,
FROM AccountsQuery LEFT JOIN [MCIProj-Status] ON AccountsQuery.Status = [MCIProj-Status].Status
WHERE (((AccountsQuery.[Bill Date])>=DateSerial(Year(Da
That will be:
WHERE (((AccountsQuery.[Bill Date])>=DateSerial(Year(Da te()),Mont h(Date())- 1,1)
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Da te()),Mont h(Date())+ 3,0)));
/gustav
WHERE (((AccountsQuery.[Bill Date])>=DateSerial(Year(Da
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Da
/gustav
ASKER
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"?
Is your regional setting one with a dd/mm/yyyy setting?
does this work better ?
WHERE AccountsQuery.[BillDate] BETWEEN #" & dateAdd("m",-1,Date()) &"# AND #" & dateAdd("m",2,Date()) &"#""
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") &"#""
ASKER
Hi John,
I keep getting a syntax error (missing operator) with this, not sure what's missing though
I keep getting a syntax error (missing operator) with this, not sure what's missing though
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.[B ill Date])>=DateSerial(Year(Da te()),Mont h(Date())- 1,1)
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Da te()),Mont h(Date())+ 3,0);
/gustav
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.[B
And (AccountsQuery.[Bill Date])<=DateSerial(Year(Da
/gustav
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 ";"
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
/gustav
try this
Where DateSerial(Year([AccountsQ uery].[Bil l Date]),Month([AccountsQuer y].[Bill Date]),day([AccountsQuery] .[Bill Date])) berween
DateSerial(Year(Date()),Mo nth(Date() )-1,1) and DateSerial(Year(Date()),Mo nth(Date() )+3,0)
Where DateSerial(Year([AccountsQ
DateSerial(Year(Date()),Mo
typo
try this
Where DateSerial(Year([AccountsQ uery].[Bil l Date]),Month([AccountsQuer y].[Bill Date]),day([AccountsQuery] .[Bill Date])) between
DateSerial(Year(Date()),Mo nth(Date() )-1,1) and DateSerial(Year(Date()),Mo nth(Date() )+3,0)
try this
Where DateSerial(Year([AccountsQ
DateSerial(Year(Date()),Mo
Oh no, even more confusion added.
DateSerial(Year([AccountsQ uery].[Bil l Date]),Month([AccountsQuer y].[Bill Date]),day([AccountsQuery] .[Bill Date]))
will return nothing but .... [Bill Date]
There are many way to complicate matters.
/gustav
DateSerial(Year([AccountsQ
will return nothing but .... [Bill Date]
There are many way to complicate matters.
/gustav
In Greece the regional settings are dd/mm/yyyy so my solution works just fine....fyi
ASKER
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
@correlate
Is this a date field or a text field?
Is this a date field or a text field?
ASKER
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)
@correlate
try creating a new query and place this
Field Expr1:DateSerial(Year([Bil l Date]),Month([Bill Date]),Day([Bill Date]))
Sort Ascending
see if you will get a correct listing order
try creating a new query and place this
Field Expr1:DateSerial(Year([Bil
Sort Ascending
see if you will get a correct listing order
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rey, re the sort - that works all ok
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rey, if [Bill Date] is a date (which is now appears not to be)
DateSerial(Year([AccountsQ uery].[Bil l Date]),Month([AccountsQuer y].[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
DateSerial(Year([AccountsQ
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
@gustav,
I suggest that you try things first before posting something
I suggest that you try things first before posting something
Oh, I noticed the missing DateValue. Couldn't you just have made a note on that?
So:
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);
/gustav
ASKER
Thanks for your help on this & useful tot know for the future re the way dates are handled in selection queries
Open in new window