update query problem

[LUMPKINBD]

Weird problem that I ran into and I'm stumped. In my database i have a column "diagnosis01" which is formatted as a text field, since some diagnosis codes come with a letter. However, I have to run several query's to add the proper 0's back on to the beginning or end of the value when it gets importted because of formatting in excel (cannot be helped). So I run several query's to add the 0 depending on length of the value. However, when i get to the 5 digit length changes i run into a problem. I have some codes that come in like 111.1 and i need to add the 0 to the end. And others come in as 32.51 and I need to add the 0 to the beginning. Most of them are in the format 111.1 so I use the following update query:



UPDATE Master SET Master.DIAGNOSIS01 = IIf(Len([Master].[DIAGNOSIS01])=5,[Master].[DIAGNOSIS01] & "0",[Master].[DIAGNOSIS01]);

I thought of running one that looks like:

UPDATE Master SET Master.DIAGNOSIS01 = IIf(Len([Master].[DIAGNOSIS01])=5,"0" & [Master].[DIAGNOSIS01],[Master].[DIAGNOSIS01]);
Where [master].[diagnosis01]>100

But since it is formatted as text i can't do a less than. Any ideas?

[June7]

The examples you show are both 5 characters (including the period) but no letters and not 5 digits. Where would a letter show up? Every code will have a period? How many characters can be possible?

[LUMPKINBD]

Yes, all entries will have periods. My problem is only with all numerical entries. All entries need to be 6 characters. The entries with letters are not reformatted by Excel and remain 6 characters throughout the process. I just threw that info in there so that I did not get the advise to format the field as a number. And sorry, I did mean characters, not digits.

[June7]

I find it odd that the import drops the zeros.

I created a spreadsheet with diagnosis codes as you describe, with placeholder zeros. Set the column as text. Used import wizard to import to new table. The values were unchanged.

What is the formatting in your Excel that does not retain the zeros? Why is Excel involved? Where does this data really come from?

This update should be possible with one query. However, might be simpler to call a custom function to fix the value instead of trying to build an all-in-one expression in query field.

Consider:
IIf(Len(x)=6,x, Format(Left(x,InStr(x,".")-1),"000") & "." & IIf(IsNumeric(Mid(x,InStr(x,".")+1,2)), Format(Mid(x,InStr(x,".")+1,2),"00"), Mid(x,InStr(x,".")+1,2)))

[LUMPKINBD]

The original data comes from a separate program when it's sent to excel the zeros are removed. My apologies. It happens on the export to excel not the import to access. I will try what you posted if I have time tonight. If not, tomorrow morning. Thanks.