BETWEEN date AND date filter in query excludes year in result set (1 Viewer)

yaya

Registered User.
Local time
Today, 14:49
Joined
Dec 4, 2018
Messages
18
Hello,

I have the following crosstab query to sort grouping of transactions and list the total number of transactions by group:
Code:
TRANSFORM Count(sourceQuery.Grouping) AS CountOfGrouping
SELECT sourceQuery.[Major Grouping] AS [Transaction Type]
FROM sourceQuery
WHERE (((sourceQuery.ProcessDate) Between DateSerial(Format(DateAdd("m",-1,Now()),"yyyy"),Format(DateAdd("m",-1,Now()),"mm"),1) And DateSerial(Format(Now(),"yyyy"),Format(Now(),"mm"),0)))
GROUP BY sourceQuery.[Major Grouping]
PIVOT sourceQuery.Grouping;

According to the result set, which as of today, should be all of the corresponding transactions for last month (11/1/2018 - 11/30/2018), all records from 11/1-30 are being returned but it seems to not care about the year. I am seeing records dating back to 2013 which isnt right.

I tried outputting the formatted dateserial functions in the WHERE clause above via a messagebox and everything looks good to me (the years are included). I am not sure why the 2018 part is being left out of the filtering.

Any help would be greatly appreciated!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:49
Joined
Oct 29, 2018
Messages
21,358
Hi,

What is the data type of the field “ProcessDate?”
 

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
Try these much simpler methods

The first day of last month
FirstDay = DateSerial(Year(Date),Month(Date)-1,1)

The last day of last month is the zero day of this month
LastDay = DateSerial(Year(Date),Month(Date),0)

Use those in your Between .... And.
Depending on your location, you may need to use Format(....,"mm/dd/yyyy")
 

yaya

Registered User.
Local time
Today, 14:49
Joined
Dec 4, 2018
Messages
18
"ProcessDate" is a date/ time field.

DateSerial(Year(Date),Month(Date)-1,1) and DateSerial(Year(Date),Month(Date),0) arent working in the query builder because the (Date) part keeps getting resolved to ("Date") instead and I am receiving a type mismatch error.

I also tried using (Date()) instead which ran but unfortunately did not filter the data properly; it still is ignoring the year :/
 

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
Both of these should definitely work

The first day of last month
FirstDay = DateSerial(Year(Date()),Month(Date())-1,1)

The last day of last month is the zero day of this month
LastDay = DateSerial(Year(Date()),Month(Date()),0)


Code:
TRANSFORM Count(sourceQuery.Grouping) AS CountOfGrouping
SELECT sourceQuery.[Major Grouping] AS [Transaction Type]
FROM sourceQuery
WHERE (((sourceQuery.ProcessDate) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) 
GROUP BY sourceQuery.[Major Grouping]
PIVOT sourceQuery.Grouping;

Or try this

Code:
TRANSFORM Count(Grouping) AS CountOfGrouping
SELECT [Major Grouping] AS [Transaction Type]
FROM sourceQuery
WHERE ((ProcessDate Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) 
GROUP BY sourceQuery.[Major Grouping]
PIVOT sourceQuery.Grouping;

It should also work with Now() instead of Date() but be a little slower to process

Does your query work without the date filters?
 
Last edited:

yaya

Registered User.
Local time
Today, 14:49
Joined
Dec 4, 2018
Messages
18
Thanks for the quick response.

I tried those but still see the old records included. Do you have any idea of where else this issue could be coming from? I feel like something is wrong with the table itself or something.
 

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
It certainly sounds like it. Perhaps corruption or another error
Check your project compiles. Look for missing references
Try decompiling then recompile and compact.

If that fails, perhaps upload a cut down version of your db with all confidential data removed
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:49
Joined
Sep 21, 2011
Messages
14,047
I *thought* sql dates had to be in mm/dd/yyyy format?

Code:
SELECT IIf([Num]="NEW","Yes","No") AS expr, Test.Num, Test.Date
FROM Test
WHERE (((Test.Date) Between #11/1/2018# And #11/30/2018#));

this is from me using Between #01/11/2018# And #30/11/2018# in the query designer?
 

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
They do indeed as I mentioned in post #3 but the examples in post #1 indicated that is the format in use
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:49
Joined
Sep 21, 2011
Messages
14,047
Now thoroughly puzzled. :D
If I use the code copied from post 5

Code:
? DateSerial(Year(Date()),Month(Date())-1,1)
01/11/2018 
? DateSerial(Year(Date()),Month(Date()),0) 
30/11/2018

is that because of my UK date settings?

Edit: Not to worry, I changed to US format and they were presented as mm/dd format.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
To clarify for the benefit of anyone else reading this....
SQL requires dates to be in an accepted format mm/dd/yyyy or yyyymmdd

However the results of that will be displayed in your default date format - so for the UK that will be dd/mm/yyyy
 

yaya

Registered User.
Local time
Today, 14:49
Joined
Dec 4, 2018
Messages
18
I created a new query using the tables themselves and it works now.

I'm not sure why exactly the other query didnt work, though i suppose it has something to do with it being a query from using a query inside another existing query! Too many nested fields from all over the place grouped up together made sorting in that way not possible I guess.

Regardless, thanks for all of the responses and insight!
 

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
Sometimes starting again is the only solution. Possibly your query was corrupted.

If you didn't do so earlier, I would strongly recommend you now do the following to ensure there are no other gremlins waiting to pounce

1. Make a backup just in case
2. Decompile your database to remove any corrupt code you may have
See http://www.fmsinc.com/microsoftaccess/performance/decompile.asp
2. In the VBE, go to Tools...Options and tick Require Variable Declaration.
That will add the line Option Explicit to all new code modules but not to existing ones.
3. Go through each code module and add Option Explicit as the second line usually after Option Compare Database
4. Still in the VBE, click Debug.Compile. If any errors occur, fix them one at a time until it is fully compiled - the Compile menu item will then be disabled
5. Compact the database

Good luck from now on.
 

Users who are viewing this thread

Top Bottom