Year to Date Calculations

[Guest]

I am trying to compare sales data from the current year to the sales YTD last year. My query used the format function on the invoice date field to calculate the day of the year, i.e. Format([datInvoiceDate], "y"). The criteria for the field is <=Format(Date(), "y"). This didn't work because the day of the year is a text field and the <= works as text, not numbers. I tried to use the CInt function to change the text to an integer, but it will not accept this in the criteria section. I get an error message saying that data types don't match. Weird. Does this make sense to anyone? This has to be a very common calculation. I hope that someone has crossed this bridge before me. Thanks.

 

[Wayne Morgan]

You would need to use CInt in both equations to make both of them Integers.
As you noticed, Format returns a string. Alternatively, you may want to use
DatePart instead of Format. DatePart will return a Variant of Interger type.

--
Wayne Morgan
MS Access MVP

I am trying to compare sales data from the current year to the sales YTD

last year. My query used the format function on the invoice date field to
calculate the day of the year, i.e. Format([datInvoiceDate], "y"). The
criteria for the field is <=Format(Date(), "y"). This didn't work because
the day of the year is a text field and the <= works as text, not numbers.
I tried to use the CInt function to change the text to an integer, but it
will not accept this in the criteria section. I get an error message saying
that data types don't match. Weird. Does this make sense to anyone? This
has to be a very common calculation. I hope that someone has crossed this
bridge before me. Thanks.