Calculating a date in access reports?

0
votes

I have a report where the user enters a FROM and TO date. What I'm trying to do is allow the user to view the inventory of the last day of the previous month. So lets take for example: user enters 12/7/2015, I want to be able to show the beginning inventory between 1/1/2000 and the last day of this dates (12/7/2015) which would be 11/30. I'm running this in VB6 just to make sure the number are correct. I've seen other answers on SO but for some reason I'm getting different results.

I try to run this but get a weird date...

?DateSerial(Year(12/1/2015),Month(12/1/2015),0)

Thsi gives me the following results...

11/30/1899

So the date seems to be okay, but the year is way off. Why?

1
ms-accessreport
Delimit date values with # characters. Access interprets 12/1/2015 to mean 12 divided by 1 divided by 2015, which is 5.95533498759305E-03. And then Year(12/1/2015) returns 1899. Access understands #12/1/2015# to be the date Dec 1, 2015. So Year(#12/1/2015#) returns 2015. - HansUp

1 Answers

2
votes

Use Date:

DateSerial(Year(Date()),Month(Date()),0)

or, for a specific month:

DateSerial(Year(#12/1/2015#),Month(#12/1/2015#),0)