CODEWITHSUNDEEP
ms-access
ashveli
ashveli

Reputation: 288

MS Access Date/Time format issue?

This might be a possible duplicate, but I have a strange issue with MS Access DB.

When I update a Date/Time value for my column req_date, I'm facing a problem.

10-03-2017

is getting updated as below

10-03-2017

where as 

10-18-2017

is getting updated as below

18-10-2017

I'm using the following code in c# to Execute the query:

query = "update gb_jobs set req_delivery=myDate"
myCMD = new OleDbCommand(query, con, trans);
int tempCnt = myCMD.ExecuteNonQuery();

where as myDate is already converted to string from date time,

As per the solution by Albert, I concatenated my myDate to #myDate# but it is throwing following error: 

query = "update gb_jobs set req_delivery=#myDate#"

Error : Data type mismatch in criteria expression.

Upvotes: 1

Views: 2339

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

You don’t mention where/when/how you are updating that column in question.

As a general rule, if the VBA variable type is an actual DATE type variable then you can assign such values directly to a control or recordset in code.

However if ANY of your code uses a string result, then you MUST format the string as USA format regardless of your computer's regional settings. Your regional settings will thus transform the date display to whatever floats your boat.

So any of your date formats have to be of mm/dd/yyyy. Given your examples, it looks like you are following that format. This suggests that you have your DISPLAY set to DD/MM/yyyy. So in theory what you have given so far is correct behaviour.

What this suggests is that your result of 10-03-2017 ACTUALLY means 03/10/2017. So it is in fact March and not October.

Thus in VBA code to update (or query) some data, you have to go:

dtStart    as date
dtEnd      as date

If you set the value of above two dates, then to query the data, you MUST go:

strWhere = "InvoiceDate >= #" & format(dtStart,'mm/dd/yyyy') & "#" & _
       " and InvoiceDate <= #" & format(dtEnd,"mm/dd/yyyy") & "#"

Docmd.OpenReport "rptInvoice",acViewPreview,,strWhere

So any code that will query, or update values with SQL has to re-format the data to USA format (mm/dd/yyyy). What the control and forms will display is as noted whatever you have in windows regional panel, but ALL internal code must format strings as mm/dd/yyyy.

So it not clear how you are making the change, but from what you have given so far, your DISPLAY of info is dd/mm/yyyy, but you are entering the data as mm/dd/yyyy which is correct. If you are entering this data on a form and not using code, then from what you have given your date format as set by windows is dd/mm/yyyy.

Upvotes: 3

Related Questions