pass correct date in sql string, tried all the obvious

[dexcelle]

Hi,

having queried and tested endless options, I can't get a succesfull entry of dates in their correct format into a linked SQL Server table via Access VBA.

Situation is that I have an Access table ("AntroImport") which holds data imported from an Excel sheet. First colum holds a date/time in this format:
"8/17/2015 16:39:52"
This field "F1" itself is not formatted as a date but as text (the table is created on the fly via TransferSpreadsheet)

Via a strsql I'm trying to insert data from this table into the linked SQL Server table, in a field set as DateTime, using the following

strsqlA4 = "insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2, F1, F3, Replace(F4,""."","",""), Replace(F5,""."","",""), F6, Replace(F7,""."","",""), F8 from AntroImport"
db.Execute (strsqlA4)

The issue is the classical issue of MS expecting dd/mm/yyyy and reversing day and month:
"8/17/2015 16:39:52" is ok
"8/10/2015 16:39:52" results in reversed day and month value.
A classic issue, however, the classic solutions I can't get to work.

I've tried the JetDateTimeFmt constant (continueous syntax errors) and had a look at Browne's SQLdate function but in the above string I can't call this function (if I can, let me know). I've tried dozens of different Format statements.

Reason for using a sql string is that further down in the code I start using similar statements but then with dynamic table and field names, which prevent me from building Access queries.

Thanks for some advice on passing on the correct formatted date in a sql string.

 

[Minty]

So I'm stabbing a guess that Datum is your table field and that F1 is the date field... I can't test this unfortunately

strsqlA4 = "insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) "
strsqlA4 = strsqlA4 & "SELECT F2,  # & Format(F1, 'mm\/dd\/yyyy') & # , F3, Replace(F4,""."","",""), Replace(F5,""."","",""), F6, Replace(F7,""."","",""), F8 from AntroImport"

Debug.Print strsqlA4  [COLOR="YellowGreen"] ' This will show you your SQL statement in the immediate window for debugging[/COLOR]

db.Execute (strsqlA4)

 

[dexcelle]

Hi, thanks for replying. I test your code and got a syntax error: syntax error in date in query expression '# & format(F1, 'mm\/dd\/yyyy') &'. (note that the message is excluding the second hash from the code)

 

[Minty]

I'll need to test it - I'm on my tablet hence not able to.

When I am struggling with this type of syntax I would create a query in the design window that formats the date as you need it and the switch to design view to see how it sets it up?

 

[arnelgp]

have you first inspected your source data, if indeed the day/month is correct, since the first date passed, ok.

 

[spikepl]

strsqlA4 = "insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2, "#" & F1 & "#", F3, Replace(F4,""."","",""), Replace(F5,""."","",""), F6, Replace(F7,""."","",""), F8 from AntroImpo

since your date is formatted correctly for SQL.


What is the function of all the Replace?

and btw

The issue is the classical issue of MS expecting dd/mm/yyyy and reversing day and month:

Nope. SQL wants mm/dd/yyyy.

 

[dexcelle]

Source data has been checked. In fact, it has been altered to have a complete test scenario.

"#" & F1 & "#"

results in a Expected: end of statement

""#"" & F1 & ""#""

passes NULL values into the table

the replaces are to deal with wrong decimal notification (from source data being a Google sheet originally)

indeed, mm/dd/yyyy

 

[spikepl]

IF F1 is text then "#" & F1 & "#" should pass the text wrapped in #, which is what is required for SQL.

Please show an SQL string.

debug.print strsqlA4

 

[spikepl]

Sorry, it should be (I removed superfluous "):

strsqlA4 = "insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2, #" & F1 & "#, F3, Replace(F4,""."","",""), Replace(F5,""."","",""), F6, Replace(F7,""."","",""), F8 from AntroImpo

 

[dexcelle]

IF F1 is text then "#" & F1 & "#" should pass the text wrapped in #, which is what is required for SQL.

Please show an SQL string.

debug.print strsqlA4

I've copied your code:

strsqlA4 = "insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2, "#" & F1 & "#", F3, Replace(F4,""."","",""), Replace(F5,""."","",""), F6, Replace(F7,""."","",""), F8 from AntroImport"

Access VBA editor doesn't allow me to run this code due to a compile error (as previously mentioned) so I can't get nowhere near a debug.print. The reason is that the double quote before the hash is seen as end of string.

 

[spikepl]

Look at my latest corrected version please.

As to your decimal numbers: I think that will be wrong too. SQL requires " ." as decimal separator, even if you locally use "," to say 1,5 =(1½)

 

[dexcelle]

Look at my latest corrected version please.

As to your decimal numbers: I think that will be wrong too. SQL requires " ." as decimal separator, even if you locally use "," to say 1,5 =(1½)

Ah, didn't see that one appearing while replying. No luck:

Syntax error in date in query expression '#'.

 

[spikepl]

debug.print sql, I will not fight blind

 

[dexcelle]

insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2, ##, F3, Replace(F4,".",","), Replace(F5,".",","), F6, Replace(F7,".",","), F8 from AntroImport

"& F1 &" are gone??

 

[Minty]

I don't think #" & F1 & "# will work as F1 is a reference to the source not a variable in any other part of the code.

The debug.print won't work as the SQL statement isn't getting formatted correctly.

Could you upload a simple sample DB of the excel data and the query and table you are trying to insert to.

 

[spikepl]

Ok I know ... the silly thing:

strsqlA4 = "insert into dbo_Antropometrie (SpelerID, Datum, Lengte, Gewicht, Schoenmaat, Voetbreedte, SitReach, Zithoogte) select F2, ""#"" && "F1" && ""#"" , F3, Replace(F4,""."","",""), Replace(F5,""."","",""), F6, Replace(F7,""."","",""), F8 from AntroImpo

 

[dexcelle]

Sorry.....Compile error, double quote before F1 is seen as end of string. (can't run debug)

 

[dexcelle]

Dispite all the suggestions, the issue remains unresolved. Any further suggestions are very welcome.

 

[leanpilar]

I had this issue because my country, my sistem and vba have diffenrent format date
I solved this way you can try too
in module:
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
and then every date use format(yourdate, JetDateFmt )

my system cdate(date) = 02.11.2015. = format(cdate("02.11.2015."),JetDateFmt )

 

[dexcelle]

Thanks. I've seen this one before but could not make it work. I see you mention "module" while my code is launched from a form and button.
Should the Global Const be set up somewhere else? Where to call it from then?