Insert into (dates format) VBA

[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

 

[Roger Carlson]

The Format() function will convert a date to a text value. If you're
entering a date value into a DateTime field, there's no need to format it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ken Snell [MVP]]

Try using the US format in that code line:

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

 

[Wayne-I-M]

Yep - you are right
--
Wayne
Manchester, England.

The Format() function will convert a date to a text value. If you're
entering a date value into a DateTime field, there's no need to format it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Wayne-I-M]

Hi Ken thanks for that - I just left off the formating and it's fine.

thanks

--
Wayne
Manchester, England.

Try using the US format in that code line:

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

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[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.

 

[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