Insert into (dates format) VBA

W

Wayne-I-M

Hi I am using INSERT INTO to send a date from an unbound text box to a table
field
text box = short date format
table field - time date format

this is producing incorrect data (the date is not being sent correctly)

Any ideas would be really helpful on formating the
#" & Format(rs!FlightDate, "yyyy/m/d") & "#
to the table


'code is

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do Until rs.EOF
CurrentDb.Execute "INSERT INTO tblflights " & "(BookingRef ,
FlightDate) VALUES (" & rs!BookingRef & ", #" & Format(rs!FlightDate,
"yyyy/m/d") & "#)"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Requery

Many thanks
 
K

Ken Snell [MVP]

Try using the US format in that code line:

#" & Format(rs!FlightDate, "mm\/dd\/yyyy") & "#
 
P

Peter Hibbs

Wayne,

I think you will find that you DO need the formatting function to
convert the UK style date into the USA format (assuming you are doing
this in the UK). You can either use Ken's suggestion or mine which
uses the Universal Date format which should work anywhere in the world
(including Italy). If you don't, then a date in the original record
such as 7th August 2009 (07/08/2009) will be saved to the new record
as 08/07/2009 which is 8th July 2009. I have just tried it and it does
just that with no formatting. I suggest you look at this again.

Peter Hibbs.
 
W

Wayne-I-M

It seemd to work the wayne it was - provideing the table and the form were
both formated with the same date format.

But of course will take your advice.

Many thanks

Can you belive this will be freeWare for download by anyone (include the
oposition companies) that want it. Marketing dept's idea. hhhmmmmmm.

: - )

oh well


in case your interested the button will be like this (works fine now) oh and
the lack of rs in the code box names is as they are not part of the recordset
- just unbound boxes so there is no creation

As you can see I have added the date format

Friday afternoon 5:15pm - only 15 mins to go then going for a beer !!




Private Sub cmdUpdate_Click()

If IsNull(Me.txtCarrier) _
Or IsNull(Me.txtFlight_ETicket) _
Or IsNull(Me.txtFlightArrive) _
Or IsNull(Me.txtFlightDate) _
Or IsNull(Me.txtFlightDepart) _
Or IsNull(Me.txtFlightFrom) _
Or IsNull(Me.txtFlightNumber) _
Or IsNull(Me.txtFlightTo) Then
MsgBox "You must complete all the flight details " & vbCrLf & vbCrLf
& " If there is any information you don't know " & vbCrLf & "(like E Ticket
number), insert Not Known into the box", vbInformation, "Incomplete details"
Else
Dim WayneResponce As Integer

WayneResponce = MsgBox("This will ADD the flight(s) you have entered" &
vbCrLf & _
"on to the records of ALL group members." & vbCrLf & "Are you SURE you want
to do this ?" & vbCrLf & vbCrLf & _
"You will not be able to undo this operation" & vbCrLf & vbCrLf & "To
proceed click YES" & vbCrLf & "To cancel this operation click NO", vbYesNo,
"Appewnd Flights ?")
If iResponce = vbYes Then '

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
Do Until rs.EOF
CurrentDb.Execute "INSERT INTO tblflights " & _
"(BookingRef, FlightDate, FlightFrom, FlightTo, FlightDepart,
FlightArrive, FlightNumber, Flight_ETicket, Carrier) VALUES (" &
rs!BookingRef & ",#" & Format([txtFlightDate], "mm\/dd\/yyyy") & "#," &
[txtFlightFrom] & "," & [txtFlightTo] & ", #" & [txtFlightDepart] & "# , #" &
[txtFlightArrive] & "# , " & [txtFlightNumber] & ", " & [txtFlight_ETicket] &
"," & [txtCarrier] & ")"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Requery
End If
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top