Help with update record using runsql

[pirate666]

Hello all.

Im trying to update a record in a table based on some values from a from.
The idea is to update an issue status on a table where issue reference no. & assigned date/time matches between the table & form data.

Here's my sql-
DoCmd.RunSQL ("UPDATE [workload] SET [workload].issue_status= forms![main]!issue_status WHERE [workload].issue_ref ='" & Forms![main]!issue_ref & "' And [workload].issue_assigned ='" & Forms![main]!issue_dist_date & "';")

for your reference, issue_assigned & issue_dist_date are both in date/time format.
problem is- when i run this, it shows error Data type mismatch in criteria expression. is there anything wrong with my syntax?
on a deadline here so w'd really appreciate a prompt solution.
Thanx in advance.

 

[Beetle]

What is the data type of issue_status and issue_ref?

 

[pirate666]

both r texts

 

[Beetle]

A couple of things to note;

1) All form references need to be outside of the string.

2) Non-numeric values need to be properly delimited ("" for strings, # for dates)

Corrected SQL (I also added continuation characters to make I easier to read);

Dim strSQL As String

strSQL = "UPDATE [workload] SET [workload].issue_status=""" _
          & Forms![main]!issue_status & """ WHERE [workload].issue_ref =""" _
          & Forms![main]!issue_ref & """ And [workload].issue_assigned =#" _
          & Forms![main]!issue_dist_date & "#;"

CurrentDb.Execute strSQL, dbFailOnError

 

[JANR]

1) All form references need to be outside of the string.

Not completly true this statement, granted that when you use .Execute metode then yes form refrences must be outside the quotes.

But since the OP uses DoCmd.RunSQL then this would work fine:

DoCmd.RunSQL (" UPDATE [workload] SET [workload].issue_status = forms![main]!issue_status" & _
              " WHERE [workload].issue_ref = Forms![main]!issue_ref" & _
              " AND [workload].issue_assigned = Forms![main]!issue_dist_date;")

@Pirate666

The reason for the error is mainly this:

 And [workload].issue_assigned =[B][COLOR="Red"]'[/COLOR][/B]" & Forms![main]!issue_dist_date & "[COLOR="red"][B]'[/B][/COLOR];")

You are putting qoutes around a date and that is no good.

JR

 

[Beetle]

Not completly true this statement, granted that when you use .Execute metode then yes form refrences must be outside the quotes.

But since the OP uses DoCmd.RunSQL then this would work fine:

You're right. I forget about that sometimes because I never use RunSQL.

 

[JANR]

Yeah I usually talk directly to the DBEngine as this is faster and you don't have to deal with all those update warnings. but for a novice who struggle with delimiters and dates that is not US dates it is a good solution to let the Application handle those issues.

JR