Leading Zero's

[chot]

Ok, so i've tried to search the forum for this issue, becuase i know leading zeros cause problems, but i hadn't found any answers that work for my situation.

I have code that is updating a table with ID numbers. I tried using an auto field but the update sql wouldn't work correctly, so this is where i'm at.

Code:
Dim db As DAO.Database
Dim Prospect As DAO.Recordset
Dim sql As String
Dim q As QueryDef
Dim r As DAO.Recordset
Dim ID As String
Dim adjID As String

ID = Format(Nz(DMax("[ID]", "tbl"), 0), "0000") + 1
sql = "SELECT [ID] FROM [tbl] ORDER BY [Date]"
Set r = CurrentDb.OpenRecordset(sql)
r.MoveFirst
Set db = CurrentDb
Set q = db.CreateQueryDef("")
DoCmd.OpenForm "Standby ID", acNormal
Do While Not r.EOF
adjID = Right("000000000" & ID, 6)
sql = "UPDATE [tbl]"
sql = sql & " SET [tbl].[ID] = " & adjID & ";"
q.sql = sql
q.Execute
On Error Resume Next
Forms("Standby ID")!Status = ID
Forms("Standby ID").Repaint
ID = ID + 1
r.MoveNext
Loop
DoCmd.Close acForm, "Standby ID"

When i run the code i get the correct string, with leading zeros for the 'adjID' variable. But for some reason i can't get the leading zero's to stay in the table field. I have the ID field set as text and have even tried to format the field to "0000".

Help....Thanks.

 

[bob fitz]

Try changing this line:
sql = sql & " SET [tbl].[ID] = " & adjID & ";"

To:
sql = sql & " SET [tbl].[ID] = '" & adjID & "'"

 

[chot]

Bob,

It works! Thanks for the quick response.

 

[bob fitz]

Your welcome

 

[RainLover]

With the Field set as TEXT does the DMax work properly.

I would prefer to use Number and Format it with leading ZEROs.

Just my opinion.

 

[bob fitz]

Hi Rain

Just my opinion.

You're so polite!

 

[RainLover]

Hi RainYou're so polite!

LOL

Not really this is more of a CYA if I am wrong.

I had a near on two year break (Due to illness) and am still trying to get back up to speed.

I have forgotten so much.