Writing String Drops Leading Zeros?

[databasedonr]

Hi folks,

I am trying to update a field that contains position number information. Position numbers are 8 characters (all numbers) and begin with either two or three zeros as placeholders.

When I update the field, the preceeding zeros are all dropped. Here's the bit of code I use:

Dim strNewBF As String * 8
Dim strMember As String

strNewBF = Space(8)
strNewBF = Me.cboBackFill.Value


strMember = "UPDATE tblMember SET POSNO = " & strNewBF & " WHERE MemberID = " & Me.MemberID

DoCmd.RunSQL strMember

I can check my string in break mode, and it is indeed 8 characters long, with the proceeding zeros, but those zeros are dropped when the field is updated.

Typical position numbers are 00035434 and 00130754 ....

Thanks in advance for suggestions....

 

[pdx_man]

What datatype is POSNO?

 

[databasedonr]

Oh, I am so stupid

PosNo is a text field -- although it has numbers only, I do no calculations so left it as text and used a string variable (although of fixed length).

So then I went for a 5 km run and discovered that I did not embed my string in quotes in the SQL statement -- thus:


strMember = "UPDATE tblMember SET POSNO = '" & strNewBF & "' WHERE MemberID = " & Me.MemberID

Now it works beautifully. So let me sing the praises of even short runs -- not only can they keep you healthy, but sometimes they can make your code better!

Thanks anyway for looking, everyone.