Date Format Changed

[Ashnolan]

Hi guys, I am hoping someone can help me. I have a module that splits quantity for an item from multiple to 1 and copy down the relevant number of records that have been split. Problem with this is that all the added records change the date format from dd/mm/yyyy to mm/dd/yyyy, affecting other queries I have reading off this. Is there a way that I can get this to stop being changed. I have posted the code below:

Function ChangeandUpdate2()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim q As Integer
Dim i As Integer
Dim SQL As String
Dim qd As QueryDef
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DealerInvoices.* FROM DealerInvoices WHERE (((DealerInvoices.LineQuantity)>1))")
Do While Not rs.EOF
q = rs!LineQuantity
SQL = "Insert into DealerInvoices ( InvoiceNumber, InvoiceName, InvoiceDate, ProductCode, ProductDescription, LineQuantity, LineValue, LineCost, SerialNumber, ExclDealer, RebateAmount ) " & _
"values ('" & rs!InvoiceNumber & "', '" & rs!InvoiceName & "', #" & rs!InvoiceDate & "#, '" & rs!ProductCode & "', '" & rs!ProductDescription & "', 1, " & rs!LineValue / q & ", " & rs!LineCost / q & ", '" & rs!SerialNumber & "', " & rs!ExclDealer & ", " & rs!RebateAmount & ")"
Set qd = db.CreateQueryDef("", SQL)
For i = 0 To (q - 2)
qd.Execute
Next i
Set qd = Nothing

rs.MoveNext
Loop
SQL = "UPDATE DealerInvoices SET DealerInvoices.LineValue = [LineValue] / [LineQuantity]"
Set qd = db.CreateQueryDef("", SQL)
qd.Execute
Set qd = Nothing
SQL = "UPDATE DealerInvoices SET DealerInvoices.LineCost = [LineCost] / [LineQuantity]"
Set qd = db.CreateQueryDef("", SQL)
qd.Execute
Set qd = Nothing

SQL = "UPDATE DealerInvoices SET DealerInvoices.LineQuantity = 1"
Set qd = db.CreateQueryDef("", SQL)
qd.Execute
Set qd = Nothing
MsgBox "Done"

End Function

Thanks

 

[pbaldy]

Does this help?

http://allenbrowne.com/ser-36.html