formating issues

[slimjen]

All, using access 2003 frontend w/sql server 2008 backend. I am running a query. Some of the date fields are formatted so they would appear in the result as mmddyy and mmmmyyyy instead of SQLs yyyymmdd. This runs fine and returns the records until I want to put a parameter on the date: between[enter start date] and [enter end date]. Then when I run it; no records are returned. Can someone tell me why this is happening?
Thanks

[jameslarsen]

Since it sounds like the formatted dates are actually being returned as strings, have you tried converting the string back to an appropriately formatted date using CDate() in your between function? For example:

between CDate([enter start date]) and CDate([enter end date])

Let me know if that helps.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[slimjen]

Thank you for replying. This did not work.

[jameslarsen]

Can you try to expand on what is happening during your queries? So you are sending a command from Access to SQL Server to retrieve some dates. Where are you putting these dates once they are first returned to Access? A combo box? When you use your between function, are you running it inside Access with the local results pulled down from SQL Server? Or are you sending the query back to SQL Server for new results? You will definitely need some kind of conversion to force the date as it is displaying in Access into a format that the query will recognize as a date. I am fairly certain your problem is just that you are providing a string in your query when it is expecting a date, so it can't figure out what you want.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[slimjen]

Resolved. I had to take the formatting to the parameter:

Between Format([ENTER START DATE],"mmmm yyyy") And Format([ENTER END DATE],"mmmm yyyy")

This worked. Thanks for your help.