Problem with numbers

[RAG2007]

Hello,

I'm creating a Project ID and I'm having some trouble getting VBA to respond to correctly. My Project ID has to have 4 digits, so I'm taking the Autonumber ID that Access creates, and adding 0's before depending on how many digits it is.

Here's the code I'm using:

 iTempID = Me.ID.Value
If iTempID > 999 Then iTempID = iTempID
If 99 < iTempID < 1000 Then iTempID = "0" & iTempID
If 9 < iTempID < 100 Then iTempID = "00" & iTempID
If 10 > iTempID Then iTempID = "000" & iTempID
Me.ProjectID.Value = iTempID

If it is single digit (i.e. <10), it adds the three 0's. However, once it doesn't recognize that it is between 9 and 100, and still adds three 0's. Is there something wrong with my code here? After I wrote this I understood that I could use Len(), but I'd still like to know where I went wrong.

Thanks

Robin

[Nathan H]

Hello,

I'm creating a Project ID and I'm having some trouble getting VBA to respond to correctly. My Project ID has to have 4 digits, so I'm taking the Autonumber ID that Access creates, and adding 0's before depending on how many digits it is.

Here's the code I'm using:

 iTempID = Me.ID.Value
If iTempID > 999 Then iTempID = iTempID
If 99 < iTempID < 1000 Then iTempID = "0" & iTempID
If 9 < iTempID < 100 Then iTempID = "00" & iTempID
If 10 > iTempID Then iTempID = "000" & iTempID
Me.ProjectID.Value = iTempID

If it is single digit (i.e. <10), it adds the three 0's. However, once it doesn't recognize that it is between 9 and 100, and still adds three 0's. Is there something wrong with my code here? After I wrote this I understood that I could use Len(), but I'd still like to know where I went wrong.

Thanks

Robin

What about something as simple as putting "0000" in the format box of the ID field in the table?

[Nathan H]

What about something as simple as putting "0000" in the format box of the ID field in the table?

Try this one...I think there was a problem with using itempID on both sides of the equation I'm not sure. But this was working for me.

Dim itempID2 As String
itempID = Me.ID.Value
If itempID > 999 Then itempID2 = itempID
If 99 < itempID < 1000 Then itempID2 = "0" & itempID
If 9 < itempID < 100 Then itempID2 = "00" & itempID
If 10 > itempID Then itempID2 = "000" & itempID
Me.ProjectID.Value = itempID2
 

[MMcCarthy]

Nathan

I think your first idea was better. Using format "0000" will show the numbers as the OP requires but will still store them as numbers.

If you try to add trailing 0's to a number it won't work as numbers will always store without them. Whereas the format will show them as required.

Mary