Solved RTE 3464 using Where Clause (1 Viewer)

[chizzy42]

Hi all , hope all is good. Could someone explain please where im going wrong with this where clause, initially it was ok as the criteria only had one argument, now that I am trying to use two criteria in the query I'm getting RTE 3464 datatype mismatch in criteria. The initial query I had that worked was this

strSQL = "Update tblRma set removed = true, dateremoved = date() where text4 = " & Me!text4

Then i tried to change it to

strSQL = "Update tblRma set removed = true, dateremoved = date() where text4 = '" & Me!text4 & "' "

and this is where i get the error I'm assuming its a formatting problem?? I've tried just using single single quotes around the 2nd where criteria but no joy. Bit confused as to why its a data mismatch by the 2nd query.

The idea being that it should be like this:

strSQL = "Update tblRma set removed = true, dateremoved = date() where text4 = '" & Me!text4 & "' AND Line = '" & Me!Line & "'"

to use text4 AND Line fields to obtain the correct field.

Any help is appreciated

 

[Minty]

Assuming text4 (Awfully unhelpful field name by the way) is a number then putting quotes around it would fail, and explain why query 1 worked.

What data type is Line? (Again if it's a Line number why not call it LineNo or LineID Then we'd have a better chance of guessing?)

 

[arnelgp]

you can post a snapshot of the structure of table tblRma?

 

[chizzy42]

Hi Thanks for the replies, here's the table they are from....and i do need to update the textx fields to a better desription

thanks

 

[CJ_London]

so have you tried

strSQL = "Update tblRma set removed = true, dateremoved = date() where text4 = " & Me!text4 & " AND Line = '" & Me!Line & "'"

you don't need a removed boolean - the fact that removeddate (or dateremoved?) has a value tells you that

 

[arnelgp]

always check your fieldType, so you will know when to use Delimeters:

for string:
[fieldname] = '" & [texboxname] & "'"

for numeric:
[fieldname] = " & [textboxname]

for date:
[fieldname] = #" & Format$([textboxname], "mm/dd/yyyy") & "#"

 

[chizzy42]

Thanks for that cl_london that seems to have done the trick and thanks arnelgp for the delimiter explanation clearest ive seen.

thanks to all that replied, as always well appreciated