001 as text and 001 as Numeric

[Guest]

Hello all,
I am importing an excel spreadsheet into an existing table. I have a field
titled "Sequence". Values such as 001, 002, 003 etc... Currently setup as
TEXT field. It doesn't matter to me if it is text or number field, but I
need the leading zeros. I will use this field later in combination with some
other fields to create a unique key in another table.
Question is, I am trying to validate this value is between 001 and 999 when
I import. This doesn't work too well as a text field. If I convert it to
numeric field, How can I copy this numeric value (with leading zero's) into a
text field. I have read that the numeric field won't actually store the
leading zero's, but the format can display the leading zero's.

Field: Sequence
Type: Numeric
Value: 1

Field: field1
Type: text
Value: test

New Field: New_Field
Type: Text
Value: New_Field = me.field1 & "." & me.Sequence

I would like the New_Field to equal "text.001"

Any thoughts about how to do this would be greatly appreciated.

 

[Ken Snell \(MVP\)]

What type of validation are you trying to do? This will test whether the
imported value is in the proper range:

Between "001" And "999"

As for making it a numeric field, if you want to use it as a related /
linking field, leave it as text if the leading zeroes are important.

 

[Guest]

Ken,
Thank you for the very quick response.

The validation is the crux of the matter. If I leave the Sequence field as
text, i run a validation check (Query) with parameters to selects invalid
values.
< "001" or > "999"
Works ok, but if user types value of 001a, it will pass validation (not <
"001" or > "999").
I was hoping to have the Sequence field numeric so the validation of < 1 or

999 would work better..... but later in my program I will use the Sequence

field as part of another field and will want to use the leading zero's.
Can I add a field Sequence2 as text and use an update query to update
Sequence2 (text) to Sequence(numeric) with leading zeros? For example,
update Sequence2, so it would be text "001" if Sequence(numeric) was equal to
1.

 

[Pat Hartman\(MVP\)]

You can convert the value to numeric to edit it but you'll have to do it in
a form. You can't do this with a validation rule:

If Val(YourField)>=1 and <= 999 Then
"good"
Else
"bad"
End If

 

[John Vinson]

Question is, I am trying to validate this value is between 001 and 999 when
I import. This doesn't work too well as a text field. If I convert it to
numeric field, How can I copy this numeric value (with leading zero's) into a
text field. I have read that the numeric field won't actually store the
leading zero's, but the format can display the leading zero's.

This might be one of the cases where the much disdained Input Mask is
useful. A mask of "000" will require the entry of exactly three
digits.

John W. Vinson[MVP]

 

[onedaywhen]

This will test whether the
imported value is in the proper range:

Between "001" And "999"

Alternatively:

my_col LIKE '[0-9][0-9][0-9]' AND my_col <> '000'

Jamie.

--

 

[Guest]

David,

I agree in some sense with Ken and Pat but if you really must have it this
way, then you can treat is as numeric through all the validation and
everything to keep it true, and then when you get to the point that you want
to have the leading zeros to do mixing for the unique id, use a small
function to format them or just format them on the fly.

Hope you got it worked out or this helps but if you need more help, post
more specifics and hopefully we can help.

Michael

 

[Ken Snell \(MVP\)]

You can test the length of the number, or better you can test if the value
of the number matches the original number:

CStr(Val([EnteredValue])) = [EnteredValue] And
[EnteredValue] Between "001" And "999"