DateFormat Issue in an Insert Into Routine (1 Viewer)

[iankerry]

Hi

I have a routine that writes to an excel sheet from my database in the exact format i need to import into Quickbooks UK.

It all works well except for this routine which inserts a line, but it puts the date in the american format rather than UK.

I can't seem to find out how to make the format of the [datefield] to remain as dd/mm/yyyy] - in other part of the code it inserts that date correctly.

Would appreciate any ideas - hope the code is readable.

Ian

Do While Not .EOF
                    
Application.DoCmd.RunSQL "INSERT INTO temp_tbl_QBData ([flicksfeedbackleft], class, [datefield], [cluster], [VENUE], [OverToQbooks], " & _
"[qNAME], [EventID], [film_name], [vatamount], [totalvat], [totalinv], [acct], [incomeAccount], [filmNameDescription], " & _
"[today], [WEBadultsold], [WEBchildsold], [VATCODE], [AdultTP], [ChildTP], [TOTALonlineSales]) VALUES (" & _
"'" & Replace(.Fields("flicksfeedbackleft").Value, "'", "''") & "'," & _
"'" & Replace(.Fields("class").Value, "'", "''") & "'," & _
"'" & Replace(.Fields("datefield").Value, "'", "''") & "'," & _
"'" & Replace(.Fields("cluster").Value, "'", "''") & "'," & _
"'" & Replace(.Fields("VENUE").Value, "'", "''") & "'," & _
"'" & Replace(.Fields("OverToQbooks").Value, "'", "''") & "'," & _
"'" & Replace(.Fields("qNAME").Value, "'", "''") & "'," & _
                        .Fields("EventID").Value & "," & _
"''," & _
                        .Fields("vatamount").Value & "," & _
                        .Fields("totalvat").Value & "," & _
                        .Fields("totalonlinesales").Value & "," & _
"'" & .Fields("acct").Value & "'," & _
"'Online Ticket Sales'," & _
"'Deduction of online ticket sales'," & _
"#" & Format(.Fields("today").Value, "mm\/dd\/yyyy") & "#," & _
                        .Fields("WEBadultsold").Value & "," & _
                        .Fields("WEBchildsold").Value & "," & _
                        "'O'," & _
                        .Fields("AdultTP").Value & "," & .Fields("ChildTP").Value & "," & .Fields("TOTALonlineSales").Value & ");"
                        
                .MoveNext
             Loop
            .Close

 

[sneuberg]

Replace(.Fields("datefield").Value, "'", "''")

doesn't have any formatting applied to it. Later you have

 Format(.Fields("today").Value, "mm\/dd\/yyyy")

is that being inserted correctly?


So far a readability is concerned you could make you code more readable and save yourself some typing by making a function for escaping the single quotes. Something like

Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

then for example

Replace(.Fields("datefield").Value, "'", "''")

would be

ESQ(Fields("datefield").Value)

 

[iankerry]

Thanks very much for replying.

Yes that date is being inserted correctly, but of course it only gets its value on that line - e.g. it is always equal to today.

The other date field [datefield] has a value already, from the database - a date in the format dd/mm/yyyy.

So I don't know why on this line in particular access decides to change the format.

I really like your function ESQ - I am going to have a play with that now!

Many thanks.

Ian

 

[MarkK]

The other major improvement you could make to this code is to create temp querydef, and parameterize the data you are feeding in. In that case the delimiters will be handled by the querydef based on the type of the field you are inserting to. A quick example would be . . .

Sub ParameterizedQueryDefInsert(rst As DAO.Recordset)
    Const SQL_INSERT As String = _
        "INSERT INTO temp_tbl_QBData " & _
            "( flicksfeedbackleft, class, datefield, cluster, VENUE, OverToQbooks ) " & _
        "VALUES " & _
            "( p0, p1, p2, p3, p4, p5 )"
    
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("", SQL_INSERT)
    With rst
        qdf.Parameters("p0") = .Fields("flicksfeedbackleft")
        qdf.Parameters("p1") = .Fields("class")
        qdf.Parameters("p2") = .Fields("datefield")
        qdf.Parameters("p3") = .Fields("cluster")
        qdf.Parameters("p4") = .Fields("VENUE")
        qdf.Parameters("p5") = .Fields("OverToQbooks")
        qdf.Execute
        qdf.Close
    End With
End Sub

With this approach your data is delimited automatically by the querydef, AND your data may contain delimiters without breaking your SQL. People I work for commonly need to keep track of dimensional lumber measured in feet and inches, so they want to store . . .

4 x 8' x 2"x4"

. . . so how do you delimit that as text? Use a querydef.
And to follow this line of thinking even further, since you are inserting from a recordset, you must have opened that recordset with SQL, right? So couldn't you do this all in one SQL command, like . . .

Sub QueryDefInsert()
    Const SQL_INSERT As String = _
        "INSERT INTO temp_tbl_QBData " & _
            "( flicksfeedbackleft, class, datefield, cluster, VENUE, OverToQbooks ) " & _
        "SELECT flicksfeedbackleft, class, datefield, cluster, VENUE, OverToQbooks " & _
        "FROM TheQueryYouUsedToOpenYourRecordset"
    
    CurrentDb.CreateQueryDef(SQL_INSERT).Execute
End Sub

See what's going on there?

 

[sneuberg]

Could this be a formatting problem in Excel? If Excel is recognizing [datefield] as a date then isn't it up to it to format it. I was just play with a date in Excel and I can format it either way, British or American. I suggest trying to format these in Excel.

 

[iankerry]

Wow, thanks Mark, there is lot going on in this post, and I am about to finish for the day.

But will have a good look tomorrow and see if i can put some of these suggestions into practice.

Really appreciate your time.

Ian

 

[iankerry]

Could this be a formatting problem in Excel? If Excel is recognizing [datefield] as a date then isn't it up to it to format it. I was just play with a date in Excel and I can format it either way, British or American. I suggest trying to format these in Excel.

Thanks for your reply, appreciate it.

I can see when i step through the code that before it goes into an excel file, it goes into a temp file and even here, with no formatting, this one line changes the format of the data.

Best regards

Ian

 

[PeterF]

In VBA all dates are handled in American date format, in the table the date is stored as a numeric value based on days after 1-1-1900.
If you want the date in the EU format you need to format it. The presentation in the table and in forms does not change the way VBA handles dates.