Problems keeping leading zeros

[toast]

*Solved* Problems keeping leading zeros

Hello all,

I have a problem trying to keep leading zeros.

The entries in a text box can be numbers or text, and are not of a fixed length.

I want to store the entry into a string variable, which ultimately gets used in a SQL INSERT statement (into a string field).

The problem is, I can't find a way to keep the leading zeros.

For example, if somebody enters "000000" the variable will store it as "000000", but when that variable is used in the SQL statement it becomes "0".

I've tried playing around with Cstr(strVariable) but that doesn't work.
I've also played with IsNumeric(strVariable) logic but that doesn't work either (I'm surmising there is a difference between being a number, and being numeric...).

Any help on this one would be very much appreciated.

 

[pbaldy]

How are you using it? Your assumption is correct; a numeric data type or variable will drop the leading zeros. Only a text data type/string variable will keep them.

 

[toast]

Thanks for the reply.

Lets say 0000 is entered in the textbox tbEntryBox:

Dim strEntry as string
Dim strUpdate As String

strEntry = Me.tbEntryBox

debug.print strEntry 'this shows 0000

strUpdate = "INSERT INTO tblLog (strValue) VALUES (" & strEntry & ");"
DoCmd.RunSQL strUpdate

And the value that ends up in strValue is 0.

I've tried using Cstr(strEntry) and Cstr(Me.tbEntryBox) but the result is the same.
I've also tried using

if isnumeric(strEntry) then strEntry = "'" & strEntry

But the result is the same.

 

[pbaldy]

Are you sure it's a text field? If it is it should be:

strUpdate = "INSERT INTO tblLog (strValue) VALUES ('" & strEntry & "');"

Without the delimiters you're treating it like a numeric field.

 

[toast]

Spot on - all fixed now!
Many thanks

 

[pbaldy]

Happy to help! I'm actually surprised it didn't throw an error.