Problems displaying dates

[ollyculverhouse]

Hi,

I connect to a mysql db with access.

I have a few date fields, these have been given the 'date' type in
mysql (yyyy-mm-dd) and are recognised by Access as date/time.

The dates display properly however my once working search function no
longer works. I always get 'Data type mismatch in criteria expression'.

I have recently changed the format of the date fields in mysql from
text to dates.
The table which has dates stored as text works fine, but the table
which has them stored as date/time (and so using my regional settings,
dd/mm/yyyy) now fails on the search.

I format all the dates (before searching mysql) into yyyy-mm-dd,
however it still comes up as data mismatch.

Const conJetDate = "yyyy\-mm\-dd"
myBookDate = Format(Me.Controls("lastBookDate"), conJetDate)

I then add it to the criteria string:

strWhere = strWhere & "([lastBookDate] = '" & myBookDate & "') AND "

I also print out the strWhere as a debug however it prints out
correctly:

([lastBookDate] = '2006-01-01')

Does access or Jet convert the date again in the background without me
seeing it?

 

[Jerry Porter]

Keep in mind that the format is just for display purposes. When
querying data, you're dealing with the internal data type. If the field
is date/time, then your filter should compare to a date time value. Try
removing the use of Format in your SQL statement.

I think you just need to use the date field in your Where clause where
you now have the formatted string variable.

Where lastBookDate = '" & Me.Controls("lastBookDate") & "'

Jerry

 

[MarcHG]

l.s.

I suppose Access SQL needs a date-expression like
datevalue("21-8-2006") or dateserial(2006,6,21) or a date-contsant like
#21-8-2006# or some userdefined public function in a VBA-module
resulting in a date-value because the ODBC-drivers will present the
dates in date-format. Some representations depend on the global
windows-settings, so be careful to choose the most appropriate to avoid
different behaviour using different windows-environments.

Marc

(e-mail address removed) schreef:

 

[ollyculverhouse]

Hi,

Ive tried removing the format, however it still says data mismatch.
I can see its not querying the database properly as my debug line
shows:

([lastBookDate] > '01/01/2006')

As [lastBookDate] is stored as yyyy-mm-dd in the database and not as
mm/dd/yyyy (as shown above)

 

[Jerry Porter]

The date isn't stored as text, it's actually stored as a number. But
it's displayed in yyyy-mm-dd format.

But my statement that you should just send a date value doesn't really
make sense because the SQL statement you send ultimately includes your
date as text. The text has to be formatted so that the database engine
recognizes it as a date.

The next question is which database engine is translating your SQL
statement. If you're using a pass-through query, then mySQL is
interpreting your text. If not, for example if you're querying an
attached table, then Jet is interpreting your query, and you should use
Jet syntax. In that case, your date format should use # instead of ' to
delimit dates.

"...Where (lastBookDate > #" & Me.Controls("lastBookDate") & "#)"

which would like the following in the debug window:

(lastBookDate > #01/01/2006#)

Hi,

Ive tried removing the format, however it still says data mismatch.
I can see its not querying the database properly as my debug line
shows:

([lastBookDate] > '01/01/2006')

As [lastBookDate] is stored as yyyy-mm-dd in the database and not as
mm/dd/yyyy (as shown above)

Keep in mind that the format is just for display purposes. When
querying data, you're dealing with the internal data type. If the field
is date/time, then your filter should compare to a date time value. Try
removing the use of Format in your SQL statement.

I think you just need to use the date field in your Where clause where
you now have the formatted string variable.

Where lastBookDate = '" & Me.Controls("lastBookDate") & "'

Jerry

 

[ollyculverhouse]

Thanks Jerry. A few minutes before you posted it i worked it out.

Thanks for the help.

The date isn't stored as text, it's actually stored as a number. But
it's displayed in yyyy-mm-dd format.

But my statement that you should just send a date value doesn't really
make sense because the SQL statement you send ultimately includes your
date as text. The text has to be formatted so that the database engine
recognizes it as a date.

The next question is which database engine is translating your SQL
statement. If you're using a pass-through query, then mySQL is
interpreting your text. If not, for example if you're querying an
attached table, then Jet is interpreting your query, and you should use
Jet syntax. In that case, your date format should use # instead of ' to
delimit dates.

"...Where (lastBookDate > #" & Me.Controls("lastBookDate") & "#)"

which would like the following in the debug window:

(lastBookDate > #01/01/2006#)

Hi,

Ive tried removing the format, however it still says data mismatch.
I can see its not querying the database properly as my debug line
shows:

([lastBookDate] > '01/01/2006')

As [lastBookDate] is stored as yyyy-mm-dd in the database and not as
mm/dd/yyyy (as shown above)

Keep in mind that the format is just for display purposes. When
querying data, you're dealing with the internal data type. If the field
is date/time, then your filter should compare to a date time value. Try
removing the use of Format in your SQL statement.

I think you just need to use the date field in your Where clause where
you now have the formatted string variable.

Where lastBookDate = '" & Me.Controls("lastBookDate") & "'

Jerry