Date Functions in Access

[CaitlinWhybrow]

Hi

I am trying to work out an expression in Access that calculates the date of the first day of the next month (e.g. for today, it would return 01/10/03). I'm tying myself in knots with datediff and datepart functions at the moment, but not getting very far. Any help would be really appreciated.

Thank you!

Cait

 

[SQLBI]

Cait

Month(Date())+1 will return the next months serial number 1 = Jan to 12 = Dec.

Can you build up a date string based on the result of this?

Not too helpful i know, but it may point you in the right direction.





Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[Bowers74]

Try this:

Date(year(Your_Date),month(Your_Date),1)

I hope this helps! ;-)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[CaitlinWhybrow]

Thank you Leigh! I used the expression:

"01/" & Month(Date())+1 & "/" & DatePart("yyyy",Date())

and it seems to be working OK. I wasn't convinced that Access would recognise it as a date rather than a string, but I am using it is the defualt value for a date field, and it seems OK.

Thanks for your tip!

 

[Bowers74]

Ooops,

Sorry!

I just realized that I forgot something in my solution.

You wanted the first date of the NEXT month, and I was a little to fast on the Submit Button. My post should have read:

Date(year(Your_Date),month(Your_Date)+1,1)

Sorry for the confusion that this may have caused!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SQLBI]

Cait,

Be careful with the last bit of your code.

"01/" & Month(Date())+1 & "/" & DatePart("yyyy",Date())

If your in December 03, the next month will be Jan 04, however your code will use the Date function and set your field value to 01/01/2003... change it to the following to deal with this.

IIF(Month(Date())=12,"01/" & Month(Date())+1 & "/" & DatePart("yyyy",Date())+1,"01/" & Month(Date())+1 & "/" & DatePart("yyyy",Date()))

This will increment the year by 1 for Jan dates.

Hope this helps.




Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[SQLBI]

Cait,

Also, if you're in Dec, Month(Date())+1 = 13, not 1...

Your code needs to be:

IIF(Month(Date())=12,"01/" & "01" & "/" & DatePart("yyyy",Date())+1,"01/" & Month(Date())+1 & "/" & DatePart("yyyy",Date()))

Sorry for any confusion, hope this helps.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[Bowers74]

Taking Leigh's very good points into account, you could change my solution to:

IIF(Month(Your_Date)=12,Date(Year(Your_Date)+1,1,1),Date(Year(Your_Date),Month(Your_Date)+1,1))

I prefer to use the DATE() function because, you can't know which Date Format the user's system will have, and Access might not accept the "mm/dd/yyyy" format (as in Leigh's example), if the user's system date format is set to "dd.mm.yyyy" for example.

The syntax for the DATE() function is: Date(year, month, day)

;-)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!