DateSerial Question

G

Guest

Hi everyone,

I’ve created a query to display only records between Jan 1 of Year(Date())
and Dec 31 of Year(Date()). The problem is that the query will not display
records before Jan 30th. I’m not sure why it’s doing this, I’ve included the
SQL I’m using below. Any help or ideas would be greatly appreciated.

SELECT [Maintenance Info - Inspection Info].[PID/Maplot], [Maintenance Info
- Inspection Info].[Most Recent Inspection Date], [Maintenance Info -
Inspection Info].[Results of Most Recent Inspection], 1 AS [Fail Value]
FROM [Maintenance Info - Inspection Info]
WHERE ((([Maintenance Info - Inspection Info].[Most Recent Inspection Date])
Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))));

MFranz
 
K

Ken Snell \(MVP\)

Look at your WHERE expression:

Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))

Do you really mean to use a constant in the argument for the Month and Day
functions?

Month(2-1) produces a value of 12.
Day(2-1) produces a value of 31.
Month(12) produces a value of 1.
Day(31) produces a value of 30.

Thus, your expression is essentially this:

Between DateSerial(Year(Date())-1, 12, 31) And
DateSerial(Year(Date())-1, 1, 30)

or, more specifically, dates between December 31, 2005 and January 30, 2005
(based on today's date of March 1, 2006). Jet will reverse the order for you
and will select records between January 30, 2005 and December 31, 2005.

So, the question is, what did you want the query to select?
 
G

Guest

The reason is that your criteria is not correct. You can see this by opening
the Immediate window (Ctrl G) and entering the following two commands:

? DateSerial(Year(Date())-1,Month(2-1),Day(2-1))
12/31/2005

? DateSerial(Year(Date())-1,Month(12),Day(31))
1/30/2005

It sounds like you want to return all records for the current year. Is this
correct? If so, try this criteria instead:

Between Date() And "1/1/" & Year(Date())

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
F

fredg

Hi everyone,

I¢ve created a query to display only records between Jan 1 of Year(Date())
and Dec 31 of Year(Date()). The problem is that the query will not display
records before Jan 30th. I¢m not sure why it¢s doing this, I¢ve included the
SQL I¢m using below. Any help or ideas would be greatly appreciated.

SELECT [Maintenance Info - Inspection Info].[PID/Maplot], [Maintenance Info
- Inspection Info].[Most Recent Inspection Date], [Maintenance Info -
Inspection Info].[Results of Most Recent Inspection], 1 AS [Fail Value]
FROM [Maintenance Info - Inspection Info]
WHERE ((([Maintenance Info - Inspection Info].[Most Recent Inspection Date])
Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))));

MFranz
Click to expand...

Your opening line refers to "between Jan 1 of Year(Date())
and Dec 31 of Year(Date()". Yet your code refers to Year(Date())-1),
last year. I'll assume that is what you actually meant.

From the debug window:

?Day(2-1)
31
? Month(2-1)
12
? DateSerial(Year(Date())-1,Month(2-1),Day(2-1))
12/31/2005


You are confused as to the actual values returned by Month(2-1) and
Day(2-1).
Month returns the month number of a VALID date.
Your expression equates to Month(1).
1, as a valid date is 12/31/1899, (a December).
Day(1) is December 31, 1899, (the 31st).

Your starting DateSerial then will become
12/31/2005 (Not 1/1/2005)

The same is true with your ending date expression.
From the Debug window:

? DateSerial(Year(Date())-1,Month(12),Day(31))
1/30/2005

Note: Month(12) is January (as 12 as a date is 1/11/1900).
Day(31) is 30 (as 31 as a date is 1/30/1900).

So according to your criteria Access will return records between
12/30/2005 and 1/31/2005.
Note that it makes no difference the order in which the criteria is
written.

To correctly write the criteria, use:

Between DateSerial(Year(Date())-1,1,1) and
DateSerial(Year(Date())-1,12,31)

This should return records between 1/1/2005 and 12/31/2005
 
G

Guest

Hi Ken,

I really meant to say Between DateSerial(Year(Date()),Month(1),Day(1)) And
DateSerial(Year(Date()),Month(12),Day(31)) but I was able to fix my problem
with what you had written so thank you for that. I have a quick question for
you though in regards to the Month and Day numbers. If I understand you
correctly in how numbers are read for Month and Day would the following be
correct:
Month Day
1 12 1 31
2 11 2 1
3 10 3 2
4 9 4 3
5 8 5 4
6 7 6 5
7 6 7 6
8 5 8 7
9 4 9 8
10 3 10 9
11 2 11 10
12 1 12 11
13 12
14 13
15 14
16 15
17 16
18 17
19 18
20 19
21 20
22 21
23 22
24 23
25 24
26 25
27 26
28 27
29 28
30 29
31 30
Thank you again for help.

MFranz




Ken Snell (MVP) said:
Look at your WHERE expression:

Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))

Do you really mean to use a constant in the argument for the Month and Day
functions?

Month(2-1) produces a value of 12.
Day(2-1) produces a value of 31.
Month(12) produces a value of 1.
Day(31) produces a value of 30.

Thus, your expression is essentially this:

Between DateSerial(Year(Date())-1, 12, 31) And
DateSerial(Year(Date())-1, 1, 30)

or, more specifically, dates between December 31, 2005 and January 30, 2005
(based on today's date of March 1, 2006). Jet will reverse the order for you
and will select records between January 30, 2005 and December 31, 2005.

So, the question is, what did you want the query to select?

--

Ken Snell
<MS ACCESS MVP>



MFranz said:
Hi everyone,

I've created a query to display only records between Jan 1 of Year(Date())
and Dec 31 of Year(Date()). The problem is that the query will not display
records before Jan 30th. I'm not sure why it's doing this, I've included
the
SQL I'm using below. Any help or ideas would be greatly appreciated.

SELECT [Maintenance Info - Inspection Info].[PID/Maplot], [Maintenance
Info
- Inspection Info].[Most Recent Inspection Date], [Maintenance Info -
Inspection Info].[Results of Most Recent Inspection], 1 AS [Fail Value]
FROM [Maintenance Info - Inspection Info]
WHERE ((([Maintenance Info - Inspection Info].[Most Recent Inspection
Date])
Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))));

MFranz
Click to expand...
Click to expand...
 
G

Guest

Hi Tom,

Thank you for your help. Your criteria worked perfectly.

MFranz

Tom Wickerath said:
The reason is that your criteria is not correct. You can see this by opening
the Immediate window (Ctrl G) and entering the following two commands:

? DateSerial(Year(Date())-1,Month(2-1),Day(2-1))
12/31/2005

? DateSerial(Year(Date())-1,Month(12),Day(31))
1/30/2005

It sounds like you want to return all records for the current year. Is this
correct? If so, try this criteria instead:

Between Date() And "1/1/" & Year(Date())

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MFranz said:
Hi everyone,

I’ve created a query to display only records between Jan 1 of Year(Date())
and Dec 31 of Year(Date()). The problem is that the query will not display
records before Jan 30th. I’m not sure why it’s doing this, I’ve included the
SQL I’m using below. Any help or ideas would be greatly appreciated.

SELECT [Maintenance Info - Inspection Info].[PID/Maplot], [Maintenance Info
- Inspection Info].[Most Recent Inspection Date], [Maintenance Info -
Inspection Info].[Results of Most Recent Inspection], 1 AS [Fail Value]
FROM [Maintenance Info - Inspection Info]
WHERE ((([Maintenance Info - Inspection Info].[Most Recent Inspection Date])
Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))));

MFranz
Click to expand...
Click to expand...
 
G

Guest

You're welcome. I'm glad to hear that it worked for you.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MFranz said:
Hi Tom,

Thank you for your help. Your criteria worked perfectly.

MFranz

Tom Wickerath said:
The reason is that your criteria is not correct. You can see this by opening
the Immediate window (Ctrl G) and entering the following two commands:

? DateSerial(Year(Date())-1,Month(2-1),Day(2-1))
12/31/2005

? DateSerial(Year(Date())-1,Month(12),Day(31))
1/30/2005

It sounds like you want to return all records for the current year. Is this
correct? If so, try this criteria instead:

Between Date() And "1/1/" & Year(Date())

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

MFranz said:
Hi everyone,

I’ve created a query to display only records between Jan 1 of Year(Date())
and Dec 31 of Year(Date()). The problem is that the query will not display
records before Jan 30th. I’m not sure why it’s doing this, I’ve included the
SQL I’m using below. Any help or ideas would be greatly appreciated.

SELECT [Maintenance Info - Inspection Info].[PID/Maplot], [Maintenance Info
- Inspection Info].[Most Recent Inspection Date], [Maintenance Info -
Inspection Info].[Results of Most Recent Inspection], 1 AS [Fail Value]
FROM [Maintenance Info - Inspection Info]
WHERE ((([Maintenance Info - Inspection Info].[Most Recent Inspection Date])
Between DateSerial(Year(Date())-1,Month(2-1),Day(2-1)) And
DateSerial(Year(Date())-1,Month(12),Day(31))));

MFranz
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display query results if condition is true 7
Show all records in selected by query 2
DMax expression 2
DateDiff Question 4
Data Type Mismatch in Criteria Expression 17
Expression Question 2
converting year to date data to month to date 3
complex querey 2

Top