Date Formatting issues in Access

General Computing Coding and Design
1

OK, this one will tease you all (or make you go ’ oh not him again’) In access manipulating date data, I have a button that updates two records with data from two text fields, dead easy…

sqlstr = "UPDATE [Det_Done]SET [Date_Out] = #" & Me.Start_Date.Value & "#, [Date_In] = #" & Me.End_Date.Value & "# WHERE [Det_ID] = '" & Me.Det_ID.Value & "'"
DoCmd.RunSQL sqlstr

see simple, but when it transfers it it screws with the date formatting, is there someway I can either force a particular date format throughout the database or to make it put it a specific way round.

There is no point in adjusting anything on the local machine as this is going to be used on multiple machines and the IT section are Carp.

Please help, else I’m goin to be on for a long re-write session.

2

You could split [b]Me.Start_Date.Value[.b] into month, date, and year; then put #yyyy-mm-dd# into the SQL. There is only one way to interpret a date in that format.

OR

Can you use CONVERT in Access: … CONVERT(DATETIME, ‘" & Me.Start_Date.Value& "’, 103) …

(Watch the single and double quotes there).

3

HA HA, Bad skills I know but if I ignore the fact it’s a date, just use ’ to enclose it rather then # it works, the SQL code is happy dumping a text value into a date field. oh well, problem solved.

Cheers Kevin for your input BTW

4

You can also do the following:

sqlstr = “UPDATE [Det_Done]SET [Date_Out] = #” & Me.Start_Date.Value & “#, [Date_In] = #” & Me.End_Date.Value & “# WHERE [Det_ID] = '” & Me.Det_ID.Value & “’”
DoCmd.RunSQL sqlstr

Would be sqlstr = "Update [Det_Done] Set [Date_Out] = " & Chr(35) & Me.Start_Date.Value & & Chr(35) & …etc…

5

Is This Programming ? Im doing that school , just started in VB 5 , trying to use keys to move picture box, Its hard :frowning:

6

Private sub Form_KeyDown(KeyCode as integer, Shift as integer)

Select case KeyCode
case vbKeyRight

put what you want it to do here

end select
end sub