Why is SQL Insert asking for parameter value?

[mikemck7]

Hello,

I have two tables, CLIENTS and TASKS that are linked by a field named CID in a one to many relationship. The Task records are displayed as a subform.

The main form contains the command button cmdAddTask with the code below in its Click event. When the button is clicked, a new record containing the client's id and the current date and time (both as string data type) is to be inserted into the task table.

Private Sub CmdAddTask_Click()
  Dim txtCid As String, txtSdate As String, txtStime As String, txtNow As String
  
    txtNow = Now
    txtCid = Forms!clients!cid.Value
    txtSdate = Trim(Left(txtNow, InStr(txtNow, " ")))
    txtStime = Mid(txtNow, InStr(txtNow, " ") + 1)
        
    Dim db    As Database
    Dim lsql  As String
    Set db = CurrentDb()
        
    lsql = "INSERT into tasks (cid, sdate, stime) VALUES (" & txtCid & ", " & txtSdate & ", " & txtStime & ")"
    
    DoCmd.RunSQL lsql
    
    'or
    'DoCmd.RunSQL "INSERT into tasks (cid, sdate, stime) VALUES (txtCid, txtSdate, txtStime)"
    
    Tasks_subform.Requery
End Sub

When I run the concatenated INSERT statement I get the syntax error: "Missing operator in Query Expression".

If I try to run it directly I have to manually enter parameter values for txtSdate and txtStime, (but not txtCid) before the record will be inserted.

Why are niether of these methods working?

Thanks,

Mike

 

[PHV]

If sdate and stime are really defined as tring in the tasks table:
lsql = "INSERT INTO tasks (cid, sdate, stime) VALUES (" & txtCid & [tt]",'" & txtSdate & "','" & txtStime & "')"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

hi,

Missing your tics...

lsql = "INSERT into tasks (cid, sdate, stime) VALUES (" & txtCid & ", '" & txtSdate & "', '" & txtStime & "')"

why would you store date or time as TEXT is beyond me!

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[vbajock]

I thought date and time data values required a "#" delimiter, not a ' delimiter. Perhaps he is missing those.

 

[VBslammer]

Without the single quotes the query engine thinks a date such as "3/7/2005" is a math expression:
[tt]
3 [÷] 7 [÷] 2005 = ~2.1375
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[mikemck7]

Hi,

Thank you all for your input. My problem is now resolved. VBSlammer, your example helped me to fully understand why concatenation is necessary in the first place.

As for why I'm storing dates and times as strings. Well I started out using date/time data types but since I was having such a hard time getting it right, I changed everything to text so I could be sure the problem didn't relate to mismatched data types. Vbajock, this also explains why the "#" delimiter did not apply.

Now that I know better what I'm doing, I'll will be changing back.

Regards,

Mike

 

[vbajock]

You can use the cdate and format functions to keep consistent date data types in your code.