checking a field value that is the source of a lookup

[atrium]

I have a table with payment transactions I need to go through it and change any transaction that has a (what appears to be ) Blank transaction code to 1

The table field TransCode is a number data type it is also a lookup field to another table called TransactionTypes The lookup for Transactions.TransCode is
SELECT [Transaction Types].[TransTypeId], [Transaction Types].[TransCode], [Transaction Types].[TransTypeDesc] FROM [Transaction Types] ORDER BY [TransCode];

Back to my Transaction file, I want to check if TransCode is blank and another field - Particulars criteria is (InStr([Particulars],"DD Debtor Payment by "))

When I find a transaction that meets the criteria I want to update the TransCode to 1

I keep on getting 0 changes

Any help would be really apreciated

 

[arnelgp]

Update TransTable Set TransCode = 1 Where ((TransCode) Is Null) And (([Particular]) LIke 'DD Debtor Payment By*')

 

[atrium]

I used that arnelgp but it only changed 57 records and there's 1000's of them

 

[arnelgp]

you can also use:

Update TransTable Set TransCode = 1 Where (Nz(TransCode, 0) = 0) And (([Particular]) LIke 'DD Debtor Payment By*')

 

[atrium]

Update Transactions Set TransCode = 1
WHERE (Nz(TransCode, 0) = 0) And (([Particulars]) LIke 'DD Debtor Payment by*')

Update 0 rows only - didn't work

 

[Pat Hartman]

I used that arnelgp but it only changed 57 records and there's 1000's of them

Clearly there are other values. Why don't you figure out what they are?

Select TransCode, Particular, Count(*) as RecCount
From yourtable
Group by TransCode, Particular

What is the first thing you're going to do after you clean up the mess?
-- You are going to modify the table definition to make TransCode required, right?

Also, if TransCode and Particular mean the same thing, why have both fields?

 

[atrium]

Particulars is the description of the transaction.

Yes I will make it mandatory

Thank you

 

[Pat Hartman]

So, you were able to make a query so you can see what is in the table now along with counts?