add lead zeroes no longer works since upgrade to Access 2003

[Windsorcat via AccessMonster.com]

I have data that is imported in to Access as a text file. In my add lead
zeroes update query, I have 2 tables, one from the imported data called LSI,
and the second table is the client table in Access.

The imported data has stripped out all the zeroes so I need to add them back
in to match our Client Number. In Access 2000, I just did an update query
that looks like this:

Field: ClientID
Table: LSI
Update to: "0" + [LSI].[ClientID]

AND

Field: Client_Number
Table: dbo_tblClient
Criteria: Is Null

I would run this several times until the number stayed constant on the how
many rows it would update. Since Access 2007, it doesn't seem to be working
right and I can't update beyond 2600 rows, when I used to get down to the
hundreds. I hope this makes sense.

Thanks for your help.

 

[John W. Vinson]

I have data that is imported in to Access as a text file. In my add lead
zeroes update query, I have 2 tables, one from the imported data called LSI,
and the second table is the client table in Access.

The imported data has stripped out all the zeroes so I need to add them back
in to match our Client Number. In Access 2000, I just did an update query
that looks like this:

Field: ClientID
Table: LSI
Update to: "0" + [LSI].[ClientID]

AND

Field: Client_Number
Table: dbo_tblClient
Criteria: Is Null

I would run this several times until the number stayed constant on the how
many rows it would update. Since Access 2007, it doesn't seem to be working
right and I can't update beyond 2600 rows, when I used to get down to the
hundreds. I hope this makes sense.

Thanks for your help.

You should be able to do it all in one query. Let's say your ClientID field is
8 bytes long; update it to

Right("00000000" & [LSI].[ClientID], 8)

This will make a ClientID of 31 into 0000000031, and then truncate it to 8
bytes: "00000031"