How to Format Date parameter in Dsum

[misahil]

I m using Access 2010. I m Facing a problem in the undermentioned query



OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)

when i run the query it gives "Undefined Function 'Format' in Expression"

I will be very thankful if someone let me know where is the mistake and what is the correct express.

[June7]

Are LocationID and ProductID number data type? If yes, remove the apostrophes.

Why do you need to format the date parameter? Is LogDate a date type field? Access expects dates to be mm/dd/yyyy.

[misahil]

Thanks for your reply.

1. Are LocationID and ProductID number data type? No, These field are Text data type.
2. Yes LogDate is Date type field.
3. In our region the date format is dd/mm/yy that's why i need to change the format.

[June7]

Regardless of regional date display settings, Access still stores the date value as a number.

Have you reviewed http://allenbrowne.com/ser-36.html

The Format() function should work in a query. Format() results in a string, however, enclosing with # delimiters attempts to treat the string as a date value.

Since I use Access/US dates, never been an issue for me and I am not sure how to resolve. If you want to allow users to input parameters as dd-mm-yyyy, have to make sure Access doesn't misinterpret. A comparison of stored date (mm/dd/yyyy - 5/4/2014) will not match to input string value of dd-mm-yyyy - 4/5/2014). Don't set input boxes Format property as a date. Use InputMask to control user input. Then manipulate the string input to convert to date for saving to table or in filter criteria.

Try:

CDate(Format([FromDt],"dd-mm-yy"))

or

DateValue(Format([FromDt],"dd-mm-yy"))