VBA DATE issue; DD-MM-YY ?

[easyit]

Hi,

I'm missing the point with adding dates from VBA to tables.
I try to add data from a form icn combination with a table:

For i = Me.dtDatumVan To Me.dtDatumTot
strSQL = "INSERT INTO tbl_TEMP_meer_minder ( FK_medewerker, datum_van, meerwerk, FK_afwezig_reden, reden ) " & _
"SELECT tbl_TEMP_huidige_gebruiker.key, format(#" & i & "#,'yy-mm-dd') AS datum_van, " & Me.cmbRedenAfwezig.Column(2) & " AS MeerWerk, [Forms]![frmMeerMinderWerk].[cmbRedenAfwezig] AS FK_afwezig_reden, '" & Me.cmbRedenAfwezig.Column(1) & "' AS Reden " & _
"FROM tbl_TEMP_huidige_gebruiker;"
DoCmd.RunSQL strSQL
Next i

The dates are however not properly stored. I'v tried format it differently: with or without #, "dd-mm-yy", "yy-dd-mm", "mm-dd-yy" etc.

adding 8-6-05 (8-jun-05) to 14-6-05 (14-jun-05) is added like:

5-8-06
5-9-06
5-10-06
5-11-06
5-12-06
13-6-05
14-6-05

So, I'm mising something...

Could anybody help me explain?

BTW the datefields form the form are datepickerfields

Maaten

 

[KenReay]

Hi

Have you tried

For i = Me.dtDatumVan To Me.dtDatumTot
strSQL = "INSERT INTO tbl_TEMP_meer_minder ( FK_medewerker, datum_van, meerwerk, FK_afwezig_reden, reden ) " & _
"SELECT tbl_TEMP_huidige_gebruiker.key, format(#" & i & "#,'yyyy-mm-dd') AS datum_van, " & Me.cmbRedenAfwezig.Column(2) & " AS MeerWerk, [Forms]![frmMeerMinderWerk].[cmbRedenAfwezig] AS FK_afwezig_reden, '" & Me.cmbRedenAfwezig.Column(1) & "' AS Reden " & _
"FROM tbl_TEMP_huidige_gebruiker;"
DoCmd.RunSQL strSQL
Next i

Either US style dates (mm/dd/yyyy) or yyyy/mm/dd should work


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RoyVidar]

I would try formatting prior to assigning the date to the string:

[tt]"SELECT tbl_TEMP_huidige_gebruiker.key, #" & format$(i,"yyyy-mm-dd") & "# AS datum_van, "... [/tt]

See for instance International Dates in Access for more info.

Roy-Vidar

 

[easyit]

..that was quick!
, yes I've tried that too

I forgot a part:

INSERT INTO tbl_TEMP_meer_minder ( FK_medewerker, datum_van, meerwerk, FK_afwezig_reden, reden ) SELECT tbl_TEMP_huidige_gebruiker.key, format(#13-6-05#,'yy-mm-dd') AS datum_van, -1 AS MeerWerk, [Forms]![frmMeerMinderWerk].[cmbRedenAfwezig] AS FK_afwezig_reden, 'dagen geruild (meer)' AS Reden FROM tbl_TEMP_huidige_gebruiker;

This is the debug.print of strSQL.

When pasted into a qsl querypane and executed it works fine!

I really don't understand....

Maarten

 

[KenReay]

You are still using (#13-6-05#,'yy-mm-dd'), not the (#13-6-05#,'yyyy-mm-dd') suggested

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[easyit]

Roy,

Tried that as well:

strSQL = "INSERT INTO tbl_TEMP_meer_minder ( FK_medewerker, datum_van, meerwerk, FK_afwezig_reden, reden ) " & _
"SELECT tbl_TEMP_huidige_gebruiker.key, #" & Format(i, "dd-mm-yyyy") & "# AS datum_van, " & Me.cmbRedenAfwezig.Column(2) & " AS MeerWerk, [Forms]![frmMeerMinderWerk].[cmbRedenAfwezig] AS FK_afwezig_reden, '" & Me.cmbRedenAfwezig.Column(1) & "' AS Reden " & _
"FROM tbl_TEMP_huidige_gebruiker;"

 

[RoyVidar]

NOT dd-mm-yyyy BUT -> yyyy-mm-dd - look at Allen Brownees article

Roy-Vidar

 

[easyit]

Thought I had tried thios is well:

format(#" & i & "#,'yyyy-mm-dd')
But gave it a shot anyway - no result.

Added may 25 to 16- june:

datum_van
5-5-30
5-5-31
6-1-05
6-2-05
6-3-05
6-4-05
6-5-05
6-6-05
6-7-05
6-8-05
6-9-05
6-10-05
6-11-05
6-12-05
5-6-13
5-6-14
5-6-15
5-6-16
5-5-26
5-5-27
5-5-28
5-5-29

 

[KenReay]

Hi

have you tried

"SELECT tbl_TEMP_huidige_gebruiker.key, #" & Format(i, "yyyy-mm-dd") & "# AS datum_van

as requested ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RoyVidar]

I'm giving up after this, easyit, I doubt you've tried what KenReay shows (my initial suggestion), cause I know that works on all settings I've been using (Norwegian, German, UK, US, Sweedish, Danish...)

Here's another sample

[tt]...were somedate = #" & format$(date,"yyyy-mm-dd") & "#"[/tt]

Can you see the difference?

1 - using the format function in VBA - not in the string
2 - creating a string representation of the date in an unambiguous format (yyyy-mm-dd or of course mm\/dd\/yyyy could be used), which is understood by the jet engine
3 - this string is concatenated into the sql string, between two #'s

In the immediate pane (ctrl+g), do try:

[tt]? format$(date,"yyyy-mm-dd")
? "format$(" & date & ",'yyyy-mm-dd')"[/tt]

Which of these, do you think the Jet engine will interpret as the correct date?

For even another sample, also look here Return Dates in US #mm/dd/yyyy# format or have a look at the numerous threads here that contains information about how to ensure unambiguous dates when passing dates to the jet engine...

But then, why don't we all use the parameters collection in stead, much easier, no bleeding formatting, no hassle, though a line or two more of code...

Roy-Vidar

 

[JerryKlmns]

Just to add that RoyVidar 's suggestion

were somedate = #" & format$(date,"yyyy-mm-dd") & "#"

works for Norwegian, German, UK, US, Sweedish, Danish...
as he states and also for Greek where i had exactly the same issue.

 

[denosaur]

Try using the DateValue function with the DTPicker fields. I had a similar issue with DTPicker fields and it worked for me.

Ex: DateValue([Forms]![frmMeerMinderWerk].[cmbRedenAfwezig]) AS FK_afwezig_reden

I hope it works for you.

Deno

 

[KenReay]

Hi

I give up too, I am just repeating my first post

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[easyit]

Hi everybody,

..sorry, I misread your suggestions! Formatting before assigning to the string is what was needed. I guess I was blindstaring at it and needed the night of sleep.

Thank you!

 

[RoyVidar]

;-)

Roy-Vidar