SQL Blind

[Thales750]

Can anyone tell me why this works

        strSQL = "INSERT INTO tblLaborTrades ( ltLaborTradeDesc, ltLogDate, ltUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "',' & now() & ',' & getUserID() & ');"

And this does not.

        strSQL = "INSERT INTO tblLookupPhase ( phPhase,phUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "',' & getuserID() & ');"

Thanks

 

[arnelgp]

the date should be enclosed in "#":

        strSQL = "INSERT INTO tblLaborTrades ( ltLaborTradeDesc, ltLogDate, ltUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "',#" & now() & "#,'" & getUserID() & "');"

        strSQL = "INSERT INTO tblLookupPhase ( phPhase,phUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "','" & getuserID() & "');"

 

[June7]

Arnel, OP said that query worked without # delimiters. Don't have to concatenate Now() or Date() function - can be embedded between quote marks without delimiters and will work if saving to a date/time field. What is data type for ltLogDate field?

Issue is with second SQL.

Exactly what happens when you try to execute - error message, wrong result, nothing?

 

[The_Doc_Man]

Can anyone tell me why this works

        strSQL = "INSERT INTO tblLaborTrades ( ltLaborTradeDesc, ltLogDate, ltUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "',' & now() & ',' & getUserID() & ');"

And this does not.

        strSQL = "INSERT INTO tblLookupPhase ( phPhase,phUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "',' & getuserID() & ');"

Thanks

The best method is to put a breakpoint on the second "strSQL = " line, then single-step and see what is in strSQL. But another question is, what - if any - error message is associated with the failing attempt? Do you have messages disabled? Because when debugging, you NEVER want to turn off the messages. They are among your best debugging friends. When it all is working? Turn off messages. Still debugging? Messages stay on.

 

[ebs17]

In a SQL statement, you should always pay attention to the data types used for the fields.

        strSQL = "INSERT INTO tblLaborTrades ( ltLaborTradeDesc, ltLogDate, ltUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "', Now(), getUserID());"

        strSQL = "INSERT INTO tblLookupPhase ( phPhase,phUserID) "
        strSQL = strSQL & "VALUES('" & NewData & "', getuserID());"

THIS is what the instructions should look like.

Jet/ACE cannot evaluate VBA variables (=> NewData). Therefore an insertion is done by VBA, when composing the SQL statement the variable is evaluated first and the content is built directly into the statement. Thereby the inserted value must be formatted data type dependent, so that it can be recognized correctly. You will see this when you look at the generated statement:

Debug.Print strSQL

You should ALWAYS look at what you generate! Even more so when something doesn't work.

Now(), getUserID() ... These are functions that do not draw arguments "from outside". Jet/ACE can evaluate such functions (Access functions, VBA functions, self-defined functions) directly via the so-called Expression Service, i.e. they can be written directly into the statement like a field name. Do not forget brackets

Here in the tested cases the contents are transformed to strings with the apostrophes. If it works that a DateTime value is entered as string into a table field, you have to think about the table field.

getUser() ... probably returns Long as return. Now to come back to the original question:
ltUserID takes a string in the first case (because it is of type Text?).
phUserID (presumably Long) refuses to accept strings.

Dynamic SQL (assembling SQL statements via VBA) is a bit more than just throwing them together by feel and lottery wheel.

 

[cheekybuddha]

strSQL = "INSERT INTO tblLookupPhase ( phPhase,phUserID) "
strSQL = strSQL & "VALUES('" & NewData & "',' & getuserID() & ');"

This does not work because you haven't performed the concatenation in VBA.

It should be:

' ...
strSQL = strSQL & "VALUES('" & NewData & "','" & getuserID() & "');"
'                                            ^                 ^
'                                            |                 |

Use Debug.Print strSQL as Eberhard suggests after building your SQL string to inspect what you have built in the Immediate Window (Ctrl+G)