Update Query

[edp1959]

I have a text field in my table called MissingNum with several thousand records. The total number of digits in this field should always be six. The leading zero(s) have been dropped. I would like to run an update query to add these zero(s). I tried: Format([MissingNum]),"000000") in the "Update to" field of the query and I get a error message "wrong number of arguments". I also tried "000000" and it changes the all of the numbers in the table to 000000.

What am I doing wrong?

 

[Nouba]

Your query should look like

UPDATE YourTable SET MissingNumber = Format([MissingNumber],"000000")

I couldn't tell you, why you receive an error without seeing the query.

 

[edp1959]

Here is the SQL View of my query:
UPDATE tblMissingNumbers SET tblMissingNumbers.MissingNum = Format([MissingNum],"000000");

This gives me an error message: Wrong number of arguments used with function in query expression 'Format([MissingNum],"000000")

I'm using Access 2000, does this have anything to do with it.

Thanks for the reply, any further suggestions?

 

[Nouba]

Hi,

the syntax of your query looks correct. Open the VBA editor and look at tools/references. Are there any missing entries? Can you use the Format function inside the immediate window? Have you installed all the service packs (Access, MDAC, etc.)? You could also try importing all objects into a new database.

 

[Pat Hartman]

Leading zeros are by default not displayed for numeric data types. How would Access know how many zeros to display? This is simply a formatting issue. There is no need to update the table, nor will the update you are attempting be successful if the field is defined as numeric.

Whenever you want to display the field with leading zeros, use a Format() function -
Format(MissingNum,"000000")

The number of zeros in the format determines the total length of the displayed field.

 

[edp1959]

I checked my reference library and found what I needed to make this work.

Thanks for your help.