TEMPVARS - Runsql (1 Viewer)

[Joaofigu]

Hi, what am i doing wrong?

DoCmd.RunSQL "INSERT INTO TableA([FIELD1],[FIELD2]) VALUES ('" & [TempVars]![val1] & "'& ", " & '" & [TempVars]![val2] & "')"

i want to update a table with 2 tempvars values, but get me error 2498

val1 = 10000124
val2 = 2019/09/10 10:10:11

thanks

 

[Gasman]

I would expect the first needs no quotes as it is numeric, and the second needs # as it is a date & time.?

VALUES (" & [TempVars]![val1] & ", " & #" & [TempVars]![val2] & "#)"

Best way would be to construct the whole string and then debug.print it to see what is produced.?

HTH

 

[Micron]

I don't know about anyone else, but I've stopped looking up the descriptions for error numbers. You obviously know what that is, so why not post it? There are probably over 3,000 of them - no way we can know them all. Anyway this
('" & [TempVars]![val1] & "'& ", " & '" & [TempVars]![val2] & "')"
doesn't look right. Maybe

("'" & [TempVars]![val1] & "', '" & [TempVars]![val2] & "')"

In other words, the single and double quotes look like they're backwards in your post.
EDIT - I didn't really look at the variables but Gasman seems to be correct (except that the 1st might be text) so maybe

("'" & [TempVars]![val1] & "', #" & [TempVars]![val2] & "#)"

 

[Joaofigu]

i´ve tryed with No Quotes, and put the # for date value.

VALUES (" & [TempVars]![val1] & "& ", " & "# & [TempVars]![val2] &# ")"

 

[Gasman]

i´ve tryed with No Quotes, and put the # for date value.

VALUES (" & [TempVars]![val1] & "& ", " & "# & [TempVars]![val2] &# ")"

Put it all in a string and debug.print that string.

 

[Joaofigu]

DoCmd.RunSQL "INSERT INTO TableA([FIELD1],[FIELD2]) VALUES (" '& [TempVars]![val1] & "'", " & #" & [TempVars]![val2] & "#)"

val1 Is text not number
val2 is date long

 

[Gasman]

OK, put the single quotes back for the first one.
Also pay attention to your date format.

https://stackoverflow.com/questions/40805573/inserting-dates-in-access-using-vba

However you will spot mistakes quicker and much easier if you print out the string.?
Then you know the syntax is correct and will see what is being produced rather than hoping for the best?

 

[Micron]

So the edit from post 3 was no help? I saw no comment on that. If you're still stuck post back with what the sql variable looks like when printed in the immediate window.

Dim sql As String
'your code

sql = "INSERT INTO TableA([FIELD1],[FIELD2]) VALUES ('" & [TempVars]![val1] & "', #"  & [TempVars]![val2] & "#)" 
Debug.Print sql

DoCmd.RunSQL, sql

 

[Joaofigu]

Ok, i already change date to a OK date, TEMPVARS!Val2 = 08-01-2020 16:58:30.
and TEMPVARS!val1 = "ZB999" (text)

DoCmd.RunSQL "INSERT INTO TableA([FIELD1],[FIELD2]) VALUES ('" & [TempVars]![val1] & "' ", " & # & [TempVars]![val2] & #)"

 

[Joaofigu]

So the edit from post 3 was no help? I saw no comment on that. If you're still stuck post back with what the sql variable looks like when printed in the immediate window.

Dim sql As String
'your code

sql = "INSERT INTO TableA([FIELD1],[FIELD2]) VALUES ("'" & [TempVars]![val1] & "', #"  & [TempVars]![val2] & "#)" 
Debug.Print sql

DoCmd.RunSQL, sql

Sorry!, thanks for helping of course!! i will try your code!!

 

[Micron]

I had to make a correction in post 8. Sorry for the confusion.

 

[Joaofigu]

I had to make a correction in post 8. Sorry for the confusion.

It´helps !! many many thanks!!!


i was already!!:banghead::banghead::banghead:

thank you all!!

 

[Micron]

You're welcome& glad we found you a solution.
Kudos to Gasman for paying more attention to the details than what I did!

 

[NauticalGent]

Joaofigu,

I am glad you got it sorted, I knew you would with Gasman and Micron helping you, they are among the best AWF has to offer...

That being said, you seemed to ignore the advice Gasman was giving you about doing a Debug.Print to determine what your actual SQL statement was.

I would suggest you educate yourself to this handy concept especially if you plan to be using variables in your SQL statements

Best of luck to you in your future projects!

 

[Joaofigu]

Joaofigu,

I am glad you got it sorted, I knew you would with Gasman and Micron helping you, they are among the best AWF has to offer...

That being said, you seemed to ignore the advice Gasman was giving you about doing a Debug.Print to determine what your actual SQL statement was.

I would suggest you educate yourself to this handy concept especially if you plan to be using variables in your SQL statements

Best of luck to you in your future projects!

hi there!

i the code was wrong and din´t get do line of print debug.
i will learn more how to print debug, it seems useful.
thanks for all.

 

[The_Doc_Man]

Joaofigu

i will learn more how to print debug, it seems useful.

Sometimes the thing that drives you up the wall can be resolved by just looking at the result of your attempt to build something. Debug.Print is one way to do exactly that. So take it as a helpful tip for any future debugging efforts. It's an easy way to examine something, to see it as Access sees it.