Query to ODBC problem

[maincomputer]

Hi

Complete n00b here so please be gentle.

I run a query to an SQL Server across our network from Access. My query has date criteria: between #date# and #date#.

I use this every day so wanted to get a user-input date with: between [start date] and [end date].

This causes an ODBC connection fail, however. I've tried every date format possible and I'm completely confused as to why it won't work.

Could anyone help, please?

 

[Galaxiom]

The date format depends on the query.

#mm/dd/yyyy# should work if the query is in Access SQL against linked tables. If it is in the designer it should be in your system's regional date format.

However if the query is passthrough the date format should be 'mm/dd/yyyy' (single quotes as delimiters).

 

[maincomputer]

Thanks for the help, but still not working.

-I tried the criteria: between [start date] and [end date]
-Then entered in format mm/dd/yyyy when prompted, but still got "ODBC--call failed" message

-If I replace the criteria with: between #05/01/2012# and #05/03/2012#, the query works again, connecting via ODBC

Still confuzed. Grrr.

 

[Pat Hartman]

If you don't format the date at all, you won't have to worry about interpretation.

Select ... From ... Where SomeDate Between Forms!yourform!txtFromDate AND Forms!yourform!txtThruDate;

Make sure the form fields are defined as dates. The interface will show the date in the format you choose or as your default format and the query will be passed double precision numbers so format isn't an issue.

 

[maincomputer]

I'll try this on Monday. Sounds like it will work. Many thanks.