Update Query

[Lynn_AccessUser]

In Access 2000 I have the following:

Table: tblDoc
FieldName: DocID

I need to run an update query that will change the format of the values in the DocID field to add a zero on the end if the number to the right of the decimal is only 3 digits. The number of characters to the left of the decimal can vary.

DocID:
DT-USF0376694.560
DT-USF03766.5600

New values after update will be

DocID:
DT-USF0376694.5600
DT-USF03766.5600

Thank you!

 

[shadow9449]

In Access 2000 I have the following:

Table: tblDoc
FieldName: DocID

I need to run an update query that will change the format of the values in the DocID field to add a zero on the end if the number to the right of the decimal is only 3 digits. The number of characters to the left of the decimal can vary.

DocID:
DT-USF0376694.560
DT-USF03766.5600

New values after update will be

DocID:
DT-USF0376694.5600
DT-USF03766.5600

Thank you!

Well, where are you getting stuck?

Here's how I would do it:

1) Determine the number of digits after the decimal. The easiest way to do that is using the InStrRev function.
2) If the number is 3, then update the value with the value followed by a 0, otherwise, just use the value.

I don't know what your skill level is, but this should at least get you started. (e.g. if you aren't familiar with InStrRev, you can easily do a search)

Let us know if you succeed...

SHADOW

 

[Lynn_AccessUser]

I figured it out:

In the update query:

Len(Val(Right$([DocID],Len([DocID])-InStrRev([DocID],".")))) = 3 Then [DocID] + "0"

 

[shadow9449]

I figured it out:

In the update query:

Len(Val(Right$([DocID],Len([DocID])-InStrRev([DocID],".")))) = 3 Then [DocID] + "0"

Did you get it to work...?

SHADOW