checking a field value that is the source of a lookup (1 Viewer)

atrium

Registered User.
Local time
Today, 19:59
Joined
May 13, 2014
Messages
348
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

..forever waiting... waiting for jellybean!
Local time
Today, 16:59
Joined
May 7, 2009
Messages
19,170
Update TransTable Set TransCode = 1 Where ((TransCode) Is Null) And (([Particular]) LIke 'DD Debtor Payment By*')
 

atrium

Registered User.
Local time
Today, 19:59
Joined
May 13, 2014
Messages
348
I used that arnelgp but it only changed 57 records and there's 1000's of them
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:59
Joined
May 7, 2009
Messages
19,170
you can also use:

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

atrium

Registered User.
Local time
Today, 19:59
Joined
May 13, 2014
Messages
348
Update Transactions Set TransCode = 1
WHERE (Nz(TransCode, 0) = 0) And (([Particulars]) LIke 'DD Debtor Payment by*')

Update 0 rows only - didn't work
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
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

Registered User.
Local time
Today, 19:59
Joined
May 13, 2014
Messages
348
Particulars is the description of the transaction.

Yes I will make it mandatory

Thank you
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
So, you were able to make a query so you can see what is in the table now along with counts?
 

Users who are viewing this thread

Top Bottom