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?