How to resolve 30/12/1899 as Date??

[PNGBill]

Hi forum, I have included in my code to append records to a table as the code Loops.

In the Immediate Window, the records show correctly with correct dates.
But the Appended Records all show as 30/12/1899

I am pretty sure this date is incorrect as I wasn't even born then although sometimes my children would have thought so

Here is the Insert into sql which results in 30/121899

                sqlString = "INSERT INTO TblLateFeeCalculated ( LDPK, LateFeeAmount, LateFeeDate ) " & vbCrLf & _
                    "VALUES (" & LoanID & ", " & LateFeeNow & ", " & CommenceDate & ");"
                DoCmd.RunSQL sqlString

yet this Debug.Print results in the correct Non US date

Debug.Print LoanID & " " & LateFeeNow & "  " & CommenceDate

Appreciate any advice.

 

[pbaldy]

That's the Access date equivalent of 0. In your SQL, the date value needs to be surrounded by #. VBA also likes date values in US format:

http://www.mvps.org/access/datetime/date0005.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

 

[PNGBill]

Thanks Paul, Problem solved.

Code is now:

sqlString = "INSERT INTO TblLateFeeCalculated ( LDPK, LateFeeAmount, LateFeeDate ) " & vbCrLf & _
                    "VALUES (" & LoanID & ", " & LateFeeNow & ", #" & CommenceDate & "#);"

A bit lost with the two links.

How would I set these up and is this a Once per database task ?

 

[Galaxiom]

In Access I normally use Format(\#mm/dd/yyyy\#)

However I'm beginning to think that the best way to use dates might be #yyyy/mm/dd#

At least in ISO format it is then clear what date is intended. Ever tried to set a variable to a date in the VBA editor in a dd/mm/yyyy region? It seemed whatever I typed the editor wanted to deal with it some unexpected way when it was included in the sql. The only sensible way seemed to use a string to store the date but I think moving to ISO might be worth a try.

 

[smig]

I just had a similar problem.

I want the date to be formated as dd/mm/yyyy
but MS want the date to be formated as mm/dd/yyyy (US date format)

after reading these two links I had an idea )
I format the date I insert as the US format (mm/dd/yyyy) and as a result I get the format I want

 

[namliam]

On SQL level you do and always will need the US Format.

However since you can put format's on forms and tables to suite your needs, you can display it any way shape or form you like.

 

[smig]

problem is I take the date from a field on a form, that is defined as dd/mm/yyyy.
I set the SQL format to mm/dd/yyyy to trick it and save it in the table in the correct dd/mm/yyyy format.
If I don't set the format for the SQL it will save it to the table in the US format, even though the field on the table is set to be dd/mm/yyyy

 

[namliam]

If your field on the form is a date/time field, the formatting will not matter. It is still a date/time you can format in any way shape or form you want it.

If it is a text field, then you will need to use left/right/Mid functions to cut up the DD/MM/YYYY format and force the MM/DD/YYYY format...
Otherwize pushing 01/09/2010 will be Jan 09, 2010 not Sept 01, 2010

 

[ChrisO]

In the query builder you should be okay.

This question is centred on Modules and VBA and they are two different beasts.

The problem in VBA is that we are not building an SQL statement we are building a string.
It will subsequently be executed as an SQL statement but until that happens it is a string.

While the string is being formed regional settings come into play.
Access seems to think we will be displaying it not executing it as SQL.

Access therefore attempts to format the string based on local machine regional setting.

If no formatting is applied then we will end up with a string containing a date based on regional settings.
If the format function is applied then Access will still try to format the date based on regional settings.

Example in VBA:
Format(Date, “mm/dd/yyyy”)
Wrong, the forward slash is a replacement marker and will be replaced with whatever is in regional settings.

Format(Date, "yyyy\-mm\-dd")
Just try it on different regional settings.

 

[JANR]

Since the dates are really a number you can convert it to Long or Double and pass the numbervalue of the date to VBA.

ex

Function testDate(anyDate As Long, xDate As Date)
Dim sSql As String
Dim strSQL As String
 
Debug.Print anyDate & " | " & xDate
 
sSql = " INSERT INTO tblName (txtfelt, Datefield) values ('qqqtest sSql', " & CLng(xDate) & ")"
CurrentDb.Execute sSql, dbFailOnError
 
strSQL = "INSERT INTO tblName (txtfelt, DateField) values ('qqqtest strSQL', " & anyDate & ")"
CurrentDb.Execute strSQL, dbFailOnError
End Function

the debug print gives you this in a non USdate setting:
40443 | 22.09.2010

Both parameters are from the same control on a form set as shortDate, called from the click_event of a button

=testDate([txtStartDate],[txtStartDate])


JR