1

I Have been playing with variations of the Month... function in access query builder. I am having trouble building a date value from an expression. I am looking to create my own date that will be behind the scenes to perform some filtering and other tasks. My problem is that I cant seem to get the Month(number) function to do what I think it should be doing. Here is a summary of what I am looking for.

5/31/2012

Through something like this

DateSerial(Year(Date()),Month(5),Day(31))
Also
DateSerial(Year(Date()),Month("5"),Day("31"))

When I try these as an experssion the return is

1/30/2012

Im sure I am misunderstanding the structure. Please educate me.

Desert Spider
  • 744
  • 1
  • 13
  • 31

1 Answers1

2

DateSerial requires three integers, year, month, day:

 DateSerial(1992,5,2)

 02/05/1992 ''Euro locale

Year(Date()) returns an integer, so you can substitute:

 DateSerial(Year(Date()),5,31)

Interestingly, the zeroth day is the last day of the previous month:

 DateSerial(2012,12,0)=30/11/2012

-- http://office.microsoft.com/en-ie/access-help/HV080206953.aspx

As an aside, do not forget that all dates are numbers.

Month(5) will equal 1, but Month(41263)=12 !

Also 

?month(100)
 4 
?Year(100)
 1900
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Remou, so the DateSerial works off a sequential day value not calendar value. So March 10, 2012 is equivalent to Month(130)??? – Desert Spider Dec 20 '12 at 21:01
  • 1
    No. A year integer, a month integer between 1 and 12 and a day integer between 1 and 31. I included a link. However, you can mess about and get a result, just not the result you expected. The additional information is to do with the fact that all dates are numbers. 41263 is today, so the month is 12. – Fionnuala Dec 20 '12 at 21:06
  • Thanks Remou! I appreciate your assistance! – Desert Spider Dec 20 '12 at 21:31