text data in table gets converted to a number

[Guest]

I am self taught but I am a strong excel user which has helped me immensely.
I can read much VB but cannot write in it. My problems is that I have a
table that has a field that is alphanumeric and stored as text. I am using a
query that pulls the information using the Format([table.field],"0000") to
get all responses to 4 characters. The query works fine for most of the data
but there are instances where it seems to be converting the text to a number
or dropping all of the information completely and returning 0000 in the
field. The instances where it is converting the information to numbers is
where a dash (-) is used, i.e for 27-1 it returned 39109. For the instances
it return 0000 the data was either 010A, 001A, (space)1(space)A or
(space)10A. Is the problem in the Format function? What can I do different
to prevent this from happening?

 

[Baz]

In the first instance it's treating "27-1" as a date, 27th January 2007.
I'm not sure what it's doing with the A's but I daresay it is making a
valiant attempt to interpret it as some kind of number.

The basic problem is that it is meaningless to format a non-numeric value
with an all-numeric format pattern. The Format function is doing it's best
to interpret the supplied value as a number of some kind, but if you don't
like it's efforts then you need to be smart enough to get it not to attempt
this formatting on non-numeric values, e.g.:

Iif(IsNumeric(table.field), Format(table.field,"0000"), table.field)

 

[Guest]

Thanks, I wasn't positive that the format function "0000" related only to
numbers. I tried using " " without any affect. Is there any simple way
to make a field return a specific number of characters other than the format
function?

In the first instance it's treating "27-1" as a date, 27th January 2007.
I'm not sure what it's doing with the A's but I daresay it is making a
valiant attempt to interpret it as some kind of number.

The basic problem is that it is meaningless to format a non-numeric value
with an all-numeric format pattern. The Format function is doing it's best
to interpret the supplied value as a number of some kind, but if you don't
like it's efforts then you need to be smart enough to get it not to attempt
this formatting on non-numeric values, e.g.:

Iif(IsNumeric(table.field), Format(table.field,"0000"), table.field)

I am self taught but I am a strong excel user which has helped me immensely.
I can read much VB but cannot write in it. My problems is that I have a
table that has a field that is alphanumeric and stored as text. I am using a
query that pulls the information using the Format([table.field],"0000") to
get all responses to 4 characters. The query works fine for most of the data
but there are instances where it seems to be converting the text to a number
or dropping all of the information completely and returning 0000 in the
field. The instances where it is converting the information to numbers is
where a dash (-) is used, i.e for 27-1 it returned 39109. For the instances
it return 0000 the data was either 010A, 001A, (space)1(space)A or
(space)10A. Is the problem in the Format function? What can I do different
to prevent this from happening?

 

[Baz]

I'm not sure what you want. After all, in your examples, the problem values
like "27-1" and "010A" already have 4 characters, so what 4 characters do
you want instead? Or, for values that are less than 4 characters, what do
you want to happen to them?

Thanks, I wasn't positive that the format function "0000" related only to
numbers. I tried using " " without any affect. Is there any simple way
to make a field return a specific number of characters other than the format
function?

In the first instance it's treating "27-1" as a date, 27th January 2007.
I'm not sure what it's doing with the A's but I daresay it is making a
valiant attempt to interpret it as some kind of number.

The basic problem is that it is meaningless to format a non-numeric value
with an all-numeric format pattern. The Format function is doing it's best
to interpret the supplied value as a number of some kind, but if you don't
like it's efforts then you need to be smart enough to get it not to attempt
this formatting on non-numeric values, e.g.:

Iif(IsNumeric(table.field), Format(table.field,"0000"), table.field)

I am self taught but I am a strong excel user which has helped me immensely.
I can read much VB but cannot write in it. My problems is that I have a
table that has a field that is alphanumeric and stored as text. I am using a
query that pulls the information using the Format([table.field],"0000") to
get all responses to 4 characters. The query works fine for most of

the
data

but there are instances where it seems to be converting the text to a number
or dropping all of the information completely and returning 0000 in the
field. The instances where it is converting the information to numbers is
where a dash (-) is used, i.e for 27-1 it returned 39109. For the instances
it return 0000 the data was either 010A, 001A, (space)1(space)A or
(space)10A. Is the problem in the Format function? What can I do different
to prevent this from happening?

 

[Guest]

if I just had a 1A, I would want it to be " 1A". I have an excel worksheet
that I use access to analyze the data and then convert some of the data to a
fixed length ascii codeset with a CR/LF record delimiter that is uploaded to
another entity for reporting purposes. Different positions in a string refer
to specific fields and criteria. For example, the first two positions refer
to the product code, the second four positions are sales period (mmyy), etc.
Some of my data varies in the number of characters so I have to standardize
the data.

I'm not sure what you want. After all, in your examples, the problem values
like "27-1" and "010A" already have 4 characters, so what 4 characters do
you want instead? Or, for values that are less than 4 characters, what do
you want to happen to them?

Thanks, I wasn't positive that the format function "0000" related only to
numbers. I tried using " " without any affect. Is there any simple way
to make a field return a specific number of characters other than the format
function?

In the first instance it's treating "27-1" as a date, 27th January 2007.
I'm not sure what it's doing with the A's but I daresay it is making a
valiant attempt to interpret it as some kind of number.

The basic problem is that it is meaningless to format a non-numeric value
with an all-numeric format pattern. The Format function is doing it's best
to interpret the supplied value as a number of some kind, but if you don't
like it's efforts then you need to be smart enough to get it not to attempt
this formatting on non-numeric values, e.g.:

Iif(IsNumeric(table.field), Format(table.field,"0000"), table.field)


I am self taught but I am a strong excel user which has helped me
immensely.
I can read much VB but cannot write in it. My problems is that I have a
table that has a field that is alphanumeric and stored as text. I am
using a
query that pulls the information using the Format([table.field],"0000") to
get all responses to 4 characters. The query works fine for most of the
data
but there are instances where it seems to be converting the text to a
number
or dropping all of the information completely and returning 0000 in the
field. The instances where it is converting the information to numbers is
where a dash (-) is used, i.e for 27-1 it returned 39109. For the
instances
it return 0000 the data was either 010A, 001A, (space)1(space)A or
(space)10A. Is the problem in the Format function? What can I do
different
to prevent this from happening?

 

[Baz]

OK, so you want leading spaces. You can do that with the Format function:

Format([table.field],"@@@@")

if I just had a 1A, I would want it to be " 1A". I have an excel worksheet
that I use access to analyze the data and then convert some of the data to a
fixed length ascii codeset with a CR/LF record delimiter that is uploaded to
another entity for reporting purposes. Different positions in a string refer
to specific fields and criteria. For example, the first two positions refer
to the product code, the second four positions are sales period (mmyy), etc.
Some of my data varies in the number of characters so I have to standardize
the data.

I'm not sure what you want. After all, in your examples, the problem values
like "27-1" and "010A" already have 4 characters, so what 4 characters do
you want instead? Or, for values that are less than 4 characters, what do
you want to happen to them?

Thanks, I wasn't positive that the format function "0000" related only to
numbers. I tried using " " without any affect. Is there any

simple
way

to make a field return a specific number of characters other than the format
function?


:

In the first instance it's treating "27-1" as a date, 27th January 2007.
I'm not sure what it's doing with the A's but I daresay it is making a
valiant attempt to interpret it as some kind of number.

The basic problem is that it is meaningless to format a non-numeric value
with an all-numeric format pattern. The Format function is doing

it's
best

to interpret the supplied value as a number of some kind, but if you

don't

like it's efforts then you need to be smart enough to get it not to attempt
this formatting on non-numeric values, e.g.:

Iif(IsNumeric(table.field), Format(table.field,"0000"), table.field)


I am self taught but I am a strong excel user which has helped me
immensely.
I can read much VB but cannot write in it. My problems is that I

have
a

table that has a field that is alphanumeric and stored as text. I am
using a
query that pulls the information using the

Format([table.field],"0000") to

get all responses to 4 characters. The query works fine for most

of
the

data
but there are instances where it seems to be converting the text to a
number
or dropping all of the information completely and returning 0000

in
the

field. The instances where it is converting the information to numbers is
where a dash (-) is used, i.e for 27-1 it returned 39109. For the
instances
it return 0000 the data was either 010A, 001A, (space)1(space)A or
(space)10A. Is the problem in the Format function? What can I do
different
to prevent this from happening?

 

[Guest]

Thanks Baz,
That really is a great help.

OK, so you want leading spaces. You can do that with the Format function:

Format([table.field],"@@@@")

if I just had a 1A, I would want it to be " 1A". I have an excel worksheet
that I use access to analyze the data and then convert some of the data to a
fixed length ascii codeset with a CR/LF record delimiter that is uploaded to
another entity for reporting purposes. Different positions in a string refer
to specific fields and criteria. For example, the first two positions refer
to the product code, the second four positions are sales period (mmyy), etc.
Some of my data varies in the number of characters so I have to standardize
the data.

I'm not sure what you want. After all, in your examples, the problem values
like "27-1" and "010A" already have 4 characters, so what 4 characters do
you want instead? Or, for values that are less than 4 characters, what do
you want to happen to them?


Thanks, I wasn't positive that the format function "0000" related only to
numbers. I tried using " " without any affect. Is there any simple
way
to make a field return a specific number of characters other than the
format
function?


:

In the first instance it's treating "27-1" as a date, 27th January 2007.
I'm not sure what it's doing with the A's but I daresay it is making a
valiant attempt to interpret it as some kind of number.

The basic problem is that it is meaningless to format a non-numeric
value
with an all-numeric format pattern. The Format function is doing it's
best
to interpret the supplied value as a number of some kind, but if you
don't
like it's efforts then you need to be smart enough to get it not to
attempt
this formatting on non-numeric values, e.g.:

Iif(IsNumeric(table.field), Format(table.field,"0000"), table.field)


I am self taught but I am a strong excel user which has helped me
immensely.
I can read much VB but cannot write in it. My problems is that I have
a
table that has a field that is alphanumeric and stored as text. I am
using a
query that pulls the information using the
Format([table.field],"0000") to
get all responses to 4 characters. The query works fine for most of
the
data
but there are instances where it seems to be converting the text to a
number
or dropping all of the information completely and returning 0000 in
the
field. The instances where it is converting the information to
numbers is
where a dash (-) is used, i.e for 27-1 it returned 39109. For the
instances
it return 0000 the data was either 010A, 001A, (space)1(space)A or
(space)10A. Is the problem in the Format function? What can I do
different
to prevent this from happening?