Hi All,

I am try to add the current date into my access database. I only want dd/mm/yyyy so i format my date like this.



It enters the date as 30/12/1899 which must be a default date.

The funny thing is that when i output showDate on the screen it displays correctly but not when adding to the DB.

I have also check that it is a valid date.

Any suggestions.

Thanks Tim

TDA

__________________
TDA

Hello TDA,

  Check your system date whether it is correct or not. Or

showDate=day(date) & "/" & month(date) & "/" & year(date)

It is wise to enter dates into the database as yyyy/mm/dd.
This way the database will get it right. (at least MSSQL,ACCESS,MSDE)


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Hi,

My system time is correct, ive tried all combinations and also tried asigning the database field as Date and Now() and the only difference when i go yyyy/mm/dd is that it gives me 18/1/1900

Any ideas what im doing wrong.

TDA

What DB are you using?
Can you post the entire SQL statement that is being sent to the DB?
Check your regional settings are set to Australia.


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Yes my regional settings are set to Australia. dd/mm/yyyy

Im using MS Access2000 and my datatype for DeedDate is Date/Time (Medium)

Here is my code

' Format Date
showDate=day(date) & "/" & month(date) & "/" & year(date)

' enable opportunity
strUpdate = "UPDATE Registrations SET DeedStatus = " & 1 & ", DeedDate = "& showDate &" WHERE RegistrationID = " & ID
        objConn.Execute(strUpdate)

No matter how i change the format of the showDate or put in Date or Now() directly into the SQL it doesn't work.

thanks Tim


TDA

Tim,

You will need to enclose the date in single quotes.

This will fix the problem but I still recommend using yyyy/mm/dd for the format when putting into the database.

CARN' THE DOCKERS



======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Actually MS ACCESS may require a hash, try both

DeedDate = #"& showDate &"# WHERE

But I think it should be ok through ODBC.

ANY ACCESS GURU's out there may confirm.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Thanks Rod,

It worked! DAM THOSE SINGLE QUOTES

I have taken your advice about formatting dates yyyy/mm/dd

BTH: I tested both ways and you can use the # aswell, but from memory mssql chucks the sads if you use #

Thanks again mate

Tim :)

TDA

No worries, anytime.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================