Stuck wth dates format in SQL 1

[DenisB]

Trying desperatly to open a form with an SQL statement using 'Between #Today# And #DateField#'
If I use the format DD/MM/YY for the dates, the result is erratic, not all the matching records in the underlying table are found.
When I reformat both #Today# and the field (with the format property in the table, wich does not change the display in a form, by the way) to MM/DD/YY, the result is zilch. No records are found.
Tried with a query instead of a direct SQL in the form. Same result.
Any tip?

 

[ide]

do you mean you open a data form in excel, or open a form in a VB application, or open a form in MS Access and try to manipulate it's recordset?
- If the latter is true you can open a form with filtering:
DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]
for instance:
DoCmd.OpenForm "Employees", , ,"LastName = 'King'"
- or You can use the RecordSource property to specify the source of the data for a form or report. You can display data from a table, query, or SQL statement.
- or you can change the recordset of an opened form; fill up a recordset or array and set/fill with that the recordset of the form.

 

[nickjar2]

Also, all SQL uses dates in the format mm/dd (the american format) which is a pain as i am in uk and i use dates in the format dd/mm. According to the Access help there isn't anything that can be done about this. I will normally take a date and convert to the American format before putting it into an SQL query.

Nick