MS Access 97, VB6, dbDate Format

Question

Hi All.

I have a snip-it of VB6 / SQL code that reads a date from a GUI and stores it into MS Access 97.

However the date is being stored in the incorrect format.

The GUI has a format of DD/MM/YYYY, i enter 09/12/2015 (9th December 2015)

In MS Access this is stored as 12/09/2015.

I've narrowed it down to the SQL part of the VB Code.

            strSQL = "UPDATE [tblDates] SET BestBefore = #" & dteBBDate & "# WHERE MyID = "        '*  '

            strSQL = strSQL & vsfConfirm.TextMatrix(Row, vsfConfirm.ColIndex("MyID"))                      '*  '

            gdbJSASN.Execute strSQL                                                                                 '*  '

I believe that by default SQL uses the US standard MM/DD/YYYY, so when entering 09/12/2015 it assume 09 is the month

Is there a way to override this default ?

Thank you

Answer 1

Hi All,

Thank you very much all of you.  I have tested Sandro's 'fix' before reading all the other replies and this did resolve the issue.  As i not playing about with times, so losing that is no issue.

Note when running in code, and placing a break point 

strSQL = "UPDATE [tblDates] SET BestBefore = #" & dteBBDate & "# WHERE MyID = "        '*  '

dtebbdate does return the correct format 09/12/2015 dd/mm/yyyy

I will test this on another system to be sure, but looks like its done the job.  Thanks all.

Answer 2

Sandro,

Sorry I wasn't clear, I didn't mean it won't work at all, but it won't work for the OP. The problem is that he is getting the date from somewhere. Where he assigns the date to dteDDBate is where it is being interpreted as mm/dd/yyyy. So your suggestion will not change the date value being stored. He has to make sure the date value he is assigning is formatted properly. He should probably be doing something like:

dteBBDate = DateValue(Format(datecontrol,"dd/mm/yyyy"))

Once that is done, dteBBDate will have the correct date value.

Answer 3

ciao Scott, good afternoon,

So I'm afraid Sandro's suggestion won't work plus if there is a time component to the date it will eliminate the time component.

I tested it and it seems working I got the same result than applying format function.

Sure, if OP has the time to be managed the suggestion needs to be revised, I notified this in my first post.

database engine interprets the data like long if you have not got the time so it should work.

Anyway, let me know, I'm happy to learn more and more about it.

Ciao!

Sandro.

Answer 4

The format of a data is a display item only. The code you are using will correctly store a date value in the table. Access stores dates as a double precision number where the integer portion is the number of days since 12/30/1899 and the decimal portion is a fraction of a day (i.e. 6AM = .25). So assuming that Best Before and dteBBDate are defined as Date Datatypes. Then it is storing the date properly.

However, the problem is in your capture. What we need to see is how you capture the date and assign it to dteBBDate. That's where you need to format the value. 

So I'm afraid Sandro's suggestion won't work plus if there is a time component to the date it will eliminate the time component.

Answer 5

ciao The Stone,

try to change like this,m if you have not got the time :

[...] BestBefore ="  & clng(dteBBDate) & " [...] 

ciao, Sandro.