set criteria WHERE field is empty 2

[davyre]

Hi, I have a query

insertTmpTblAlcReportQry = "UPDATE TmpTblAlcReport SET Distributor='" & custName & "', PONumber='" & PONumber & "' WHERE OrderID=" & OrderID & " AND UnitID=" & UnitID & " AND isempty(Distributor)"

It is to update the field Distributor and PONumber in table TmpTblAlcReport, where field OrderID= variable orderID AND field UnitID= variable unitID AND field Distributor is empty (Otherwise it will replace the existing value in distributor field.)
But I got error message saying datatype mismatch.
I tried "AND Distributor=""" and it says error Syntax error in string query expression ....

anyone help? Thanks!

 

[davyre]

update: after I look deeper into it, I fixed the datatype of the field UnitID to Number, and now it does not give any errors. But still, the query does not insert the value custName and PONumber into the table where it should be.
I tried using Len([Distributor])=0 but it is still the same. Any help? Thanks

 

[dhookom]

Try "Distributor Is Null" or "Nz(Distributor,0) = 0"

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

You also have to be careful with custName like O'Brian and such.

Have fun.

---- Andy

 

[davyre]

I made a new variable called Blank and set it to blank (Blank=" "), then "AND Distributor='" & Blank & "'". It worked.
I haven't tried the ones Duane suggested, but maybe it will work.
Yeah, I have to check for names with single quotes Andy. The only character that I need to be aware of in VBA coding

 

[Andrzejek]

Don't confuse Blank " " with an empty string "" and with NULL
3 totally different animals.

And to deal with single quotes in custName consider:
[tt]
... SET Distributor = '" & [blue]Replace([/blue]custName[blue], "'", "''")[/blue] & "', ...
[/tt]

Have fun.

---- Andy

 

[dhookom]

I agree with Andy. Also, if Distributor is text, then I wouldn't use my suggested "Nz(Distributor,0) = 0" since it would expect numeric. To capture NULL, "", or " " consider using

Trim(Distributor & "") = ""

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

@Andy: I know that NULL is a whole different thing. But if I use Blank=" " to be inserted to a field, will that be a problem/is there any possibility that it will raise some error? I just want to delete the value of that field using UPDATE query.

Thanks

 

[dhookom]

davyre,
You haven't provided the full SQL view of your query so we can evaluate it for you. I think your Blank = " " is a non-filter, no value addition to the query but I can't tell.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

insertTmpTblAlcReportQry = "UPDATE TmpTblAlcReport SET Distributor='" & Replace(custName, "'", "''") & "',PONumber='" _
  & PONumber & "' WHERE OrderID=" & OrderID & " AND UnitID=" & UnitID & " AND Trim(Distributor & '')=''"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Sorry, I think my post earlier was not relevant to this thread(out of topic). But anyway I will post it.

dim blank as string
blank=" "

removeTmpTblAlcQry = "UPDATE TmpTblAlcReport SET Distributor='" & 0 & "', PONumber='" & blank & "' WHERE OrderID=" & stockOrderID & " AND UnitID=" & UnitID & " AND Distributor='" & custName & "'"

is the query okay? That query will be executed when I press delete button.

 

[PHV]

Why not simply this ?

removeTmpTblAlcQry = "UPDATE TmpTblAlcReport SET Distributor='0',PONumber=' ' WHERE OrderID=" _
  & stockOrderID & " AND UnitID=" & UnitID & " AND Distributor='" & custName & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

woaah PH, I didnt think of this before

PONumber=''

lol Thanks!

 

[Andrzejek]

That all depends how your field (PONumber) is defined in your table (does it allow NULLs?) and what do you consider ‘record not having PONumber number’:
[tt]
PONumber = ''
PONumber = ' '
PONumber = NULL[/tt]


Have fun.

---- Andy