limit number search and update 2

[Spott]

I have a field with 4 digit and 5 digit numbers. I want to filter to only the 4 digit numbers and then do an update query to add a leading zero.

I think I have done this before, but none of my search criteria or pattern matching ideas are returning me the 4 digit numbers.

Thanks much!

 

[PHV]

One way:
UPDATE yourTable
SET yourField = "0" & yourField
WHERE yourField Like '[0-9][0-9][0-9][0-9]';
Another way:
UPDATE yourTable
SET yourField = Format([yourField],'00000')
WHERE Val([yourField])<=9999;

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

 

[Spott]

Thanks, PH. It helped somewhat.

It didn't do anything until I changed the field from number to text. Then it added a zero to all of the numbers including the 5 digit ones. I had criteria <=9999 and update to "0" & [field]

Now I need to figure how to only pull the 4 digit again. <=9999 worked to sort as a number, but not as text.

Thanks!
Spott

 

[PHV]

how to only pull the 4 digit again
WHERE Val([yourField])<=9999

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

 

[Spott]

In SQL it looks like this:

UPDATE [DLH REPORT] SET [DLH REPORT].[FILE#] = "0" & [FILE#]
WHERE ((([DLH REPORT].[FILE#])<="9999"));

Still pulling all records. Ugg!

Changed to:

UPDATE [DLH REPORT] SET [DLH REPORT].[FILE#] = "0" & [FILE#]
WHERE Val((([DLH REPORT].[FILE#])<="9999"));

Still the same results.

Spott

 

[Golom]

Your "Val" is in the wrong place. Try

UPDATE [DLH REPORT] SET [DLH REPORT].[FILE#] = "0" & [FILE#]
WHERE Val([DLH REPORT].[FILE#])<= 9999;

 

[Spott]

You guys are good! That worked.

New issue, if I run the update query again, another 0 is added.

I'll be importing data to this table every two weeks. Is there a way to do that within this query? Only update the new <9999 data? My other thought is create a table for the new data, update and then append it to the data table?

Thanks!
Barbara

 

[Golom]

Maybe

UPDATE [DLH REPORT] SET [DLH REPORT].[FILE#] = "0" & [FILE#]
WHERE Val([DLH REPORT].[FILE#])<= 9999 
      AND Left([DLH REPORT].[FILE#],1) <> "0"

or

UPDATE [DLH REPORT] SET [DLH REPORT].[FILE#] = "0" & [FILE#]
WHERE Val([DLH REPORT].[FILE#])<= 9999 
      AND Len([DLH REPORT].[FILE#]) < 5

 

[Spott]

Fantastic!! B-)

Both codes work beautifully!!