IIf Statement to Update Text

[wvmbark]

I have a text field [BOW] that contains either a Bill of Works number (I.e., "TS1483") or " 0" (two spaces and a zero) if there's no BOW #. This data is imported from a customer's .txt file. I make some updates to the data and then append to a table. I want to append "" (leave blank) if the BOW field contains " 0". I've tried the following in my append query, but they only return records where the BOW is not " 0".

Expr2: IIf([tblALERTtxt]![BOW] Like " 0","",[tblALERTtxt]![BOW])

Expr2: IIf([tblALERTtxt]![BOW] = " 0","",[tblALERTtxt]![BOW])

Expr2: IIf([tblALERTtxt]![BOW] In (" 0"),"",[tblALERTtxt]![BOW])

Can someone help me understand why this doesn't work AND what would work?

Thanks!!

 

[mordja]

wvmbark,

IIf([tblALERTtxt]![BOW] Like " 0","",[tblALERTtxt]![BOW])


Works fine. Perhaps it is something else. [Bow] is a text field ? Are you doing a join which restricts the recordset that you are operating on?

Mordja

 

[wvmbark]

thanks mordja,

I'm getting only the records without " 0". I'm also getting a message that says it didn't append 14 records due to validation rule violations... any ideas?

 

[wvmbark]

Here's a copy of the SQL for the query. I've narrowed the query down to only the one field and still same problem.


INSERT INTO tblTargetSelection [BOW]
SELECT IIf([tblALERTtxt]![BOW] Like " 0","",[tblALERTtxt]![BOW]) AS Expr2
FROM tblALERTtxt;

There's no joins. All records with " 0" get dropped due to validation rule violations. Any help would be greatly appreciated.

Thanks!

 

[PHV]

it didn't append 14 records due to validation rule violations
So take a look at the valdation rules of tblTargetSelection.BOW
Seems you're not allowed to make it empty.

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

 

[wvmbark]

that's it. allow zero length was set to no. thanks!