Date type conversion -

[bnd_ck]

I am access vba developer. my client uses european system date in his computers. he doesnt want to change his date settings(dd.mm.yyyy). i am facing problem now inserting Date fields to access tables since it uses US date format "dd/mm/yyyy". giving me run time error in insertion. is there any ways that i can change the "dd.mm.yyyy" date value store as "dd/mm/yyyy" format. (format function i tried,but i know it should not the solution for my case)..any help appreciated

 

[Galaxiom]

Dates are stored as real numbers. Access displays them according to the local settings.

You don't need to do anything unless you want to insert date values using SQL commands. In that case you will need to format them to mm/dd/yyyy into the SQL string using the Format function.

 

[bnd_ck]

thanks, i want to store it using SQL Inser command
INSERT INTO tb1(field1,field2,,,,) VALUES (" & var1 & ", #" & userdate & "#,,,,)
that user date giving me syntax error cuz user enterd date as "dd.mm.yyyy"format . i tried sql format function. but no luck. how do i do it?..

 

[pr2-eugin]

Try something like..

INSERT INTO tbl (field1, field2) VALUES (" & var1 & ", "& [COLOR=Red][B]Format([/B][/COLOR]userdata[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & ")

 

[bnd_ck]

thanks, but same error
run time error 3075, syntax error in date in query expression.

 

[pr2-eugin]

How about this?

INSERT INTO tbl (field1, field2) VALUES (" & var1 & ", "& Format([COLOR=Red][B]Replace([/B][/COLOR]userdata[COLOR=Red][B], ".", "/")[/B][/COLOR], "\#mm\/dd\/yyyy\#") & ")

 

[bnd_ck]

many thanks pre2
same error
run time error 3075,
syntax error in date query expression '#11.07.2014'

NOTE: user enter this date via date picker control.(short date format)

 

[pr2-eugin]

Okay let us do some debugging. Try using the immediate window and place the generated SQL here. This is how you do it : http://www.baldyweb.com/ImmediateWindow.htm

 

[JHB]

Maybe you need the DateValue function.

INSERT INTO tbl (field1, field2) VALUES (" & var1 & ", " & Format(DateValue(userdata), "mm\dd/yyyy\") & ")

Or

INSERT INTO tbl (field1, field2) VALUES (" & var1 & ", #" & Format(DateValue(userdata), "mm\dd/yyyy\") & "#)

 

[bnd_ck]

this my code part with your last suggestion format & replace
Set db = CurrentDb()
Set rs = db.OpenRecordset("qry_plan_workorders_for_report")

' delete all recs in temp table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblTempPlan"
DoCmd.SetWarnings True
Do While (teDate >= tsDate)

With rs
.MoveFirst
Do While Not .EOF

If (tsDate >= rs!Startdato) And (tsDate <= rs!Sluttdato) Then
db.Execute "INSERT INTO tblTempPlan(tDate,Prosess,Beskrivelse,Startdato,Sluttdato,Sistedato,Lokasjon) VALUES (" & Format(Replace(tsDate, ".", "/"), "\#mm\/dd\/yyyy\#") & ", '" & rs!tblProsesser_Prosess & "' ,'" & rs!Beskrivelse & "', #" & rs!Startdato & "#, #" & rs!Sluttdato & "#, #" & rs!Sistedato & "#, '" & rs!Lokasjon & "' )"
End If
'tsDate1 = DateAdd("d", 1, tsDate1)
rs.MoveNext
​

Loop
​

End With
tsDate = DateAdd("d", 1, tsDate)
Loop

 

[pr2-eugin]

Grumbling !

A) You need to format the code : Please use Code Tags when posting VBA Code
B) I only wanted what the Debug window produced : Debugging SQL in VBA

You have only formatted one date, what about all the other dates? Why not simply use Galaxioms suggestion of string as Double? Playing with Dates

 

[bnd_ck]

@pre2
other dates straight away getting from query. this is the date only input

 

[pr2-eugin]

Humor me and give it a try?

 

[bnd_ck]

debug gives me this run time error 3075
syntax error in date in query expression '(([startdato]<=#26.08.2014# AND [sluttdato]>=14.07.2014#)
both dates appear are user inputs via form(dt picker date control)

 

[pr2-eugin]

Try ! Without changing a bit..

Set db = CurrentDb()
Set rs = db.OpenRecordset("qry_plan_workorders_for_report")

' delete all recs in temp table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblTempPlan"
DoCmd.SetWarnings True

Do While (teDate >= tsDate)
    With rs
        .MoveFirst
        Do While Not .EOF
            If (tsDate >= rs!Startdato) And (tsDate <= rs!Sluttdato) Then
                db.Execute "INSERT INTO tblTempPlan (tDate, Prosess, Beskrivelse, Startdato, Sluttdato, Sistedato, Lokasjon) VALUES (" & _
                Format(Replace(tsDate, ".", "/"), "\#mm\/dd\/yyyy\#") & ", '" & rs!tblProsesser_Prosess & "' ,'" & rs!Beskrivelse & "', " & _
                CDbl(rs!Startdato) & ", " & CDbl(rs!Sluttdato) & ", " & CDbl(rs!Sistedato) & ", '" & rs!Lokasjon & "')"
            End If
            'tsDate1 = DateAdd("d", 1, tsDate1)
            rs.MoveNext
        Loop
    End With
    tsDate = DateAdd("d", 1, tsDate)
Loop

 

[ZikO]

Hi

I have ACCESS2010 and reside in UK so I use European format.

How about extracting particular digits corresponding to days and months and convert them to proper date format? I tried the code below and it worked but the function Format() in my ACCESS version accepts dd/mm/yyyy as a default system format. The code uses Left/Mid/Right functions to extract characters from a string. If I assume the date is the December of 15, 2005 (12/15/2005), the code could be as follows:

strDate = "15.12.2005"
newFormatDate = Format(Left(strDate, 2) & "/" & Mid(strDate, 4, 2) & "/" & Right(strDate, 4), "\#mm/dd/yyyy\#")

converts the date 15.12.2005 into #12/15/2005#

The only thing I don't know (beginner here) is how Format would interpret the string "15/12/2004" in ACCESS installed in US systems with different default date format preferences but one could play with this IMO.

 

[pr2-eugin]

I just checked your code and this..

syntax error in date in query expression '(([startdato]<=#26.08.2014# AND [sluttdato]>=14.07.2014#)

I do not see this sort of statement anywhere in the code you presented. Are you sure you are not getting confused with something else?

 

[bnd_ck]

@Pr2
that's spot on..it worked. i have many more report calling in the program,all had similar issue.you saved my time..thumbs up
keep it up good work