Date Handling with Access FE and mySQL BE

[ollyculverhouse]

Hi,

I have a mysql database as a BE, it currently holds the date fields as
varchars.
I have created a search form which allows users to search the database,
they can use < and >.
Access doesnt return the correct dates though, is this because they are
stored as varchars in mySQL?
If it is what is the best way to store them so that access can perform
correct calculations on them and also display them in dd/mm/yyyy?

Thanks

 

[Brendan Reynolds]

Is this because the data is stored in text fields - yes. Here's a quote from
something that I posted recently in response to another question ...

? #01/01/2006# > #02/02/2005#
True

This returns True because any date in the year 2006 is greater than any date
in the year 2005.

? "01/01/2006" > "02/02/2005"
False

This returns False because any string that begins with "02" is greater than
any string that begins with "01".

The best data type to use for date values is a data type specifically
designed for that purpose. In a JET (MDB) database that would be Date/Time,
but I'm not familiar with MySQL so don't know what the MySQL equivalent
would be.

 

[ollyculverhouse]

So if i have the date equilivant in mySQL, Access will be able to
perform correct calculations on it?

 

[Brendan Reynolds]

As I said, I am not familiar with MySQL, but I would expect so, yes.

 

[Craig Hornish]

So if i have the date equilivant in mySQL, Access will be able to

perform correct calculations on it?

I belive only if it is the same as what access is used to - which is a
number. (Number of days since 12/31/1899)

From my brief (10 min) google search it looks like the Date Field of
mySQL Stores the Date as 'YYYY-MM-DD'
And you can use these SQL Statements
mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05'
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> INSERT INTO tbl_name (idate) VALUES (19970505);

So you will either
have to convert the dates from the application to the text or numerical
Values
Using the Year,Month,Day functions (Day won't add the 0))
or
convert the mySQL Dates -
DateSerial(Left([ idate ],4),Mid([ idate ],6,2),Right([ idate ],2))

To work with them.

And then use a corresponding format techneques.


--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

 

[Albert D. Kallal]

So if i have the date equilivant in mySQL, Access will be able to
perform correct calculations on it?

Yes, the odbc driver will correctly convert/use the date format.....

Once done, then date fields should work ok....