Number Format Field to Allow Text Value?

I have a number formatted fields that now needs to have text added to it.
if I convert it to a text field then the sort order changes from record 1,
2, 3, etc. to 1, 10, 11, 12 and so on since it is now looking at text
values. is there a way to allow text at the end of the number value, but
still keep it sorting in numeric order?

Thanks!

You can sometimes use the Val() function against a mixed field to return the
numeric value of the leading numeric characters. For instance
Val("123 Summit") = 123
Val("Summit 123") = 0

--
Duane Hookom
MS Access MVP


"Karl Burrows" wrote in message
...
I have a number formatted fields that now needs to have text added to it.
if I convert it to a text field then the sort order changes from record 1,
2, 3, etc. to 1, 10, 11, 12 and so on since it is now looking at text
values. is there a way to allow text at the end of the number value, but
still keep it sorting in numeric order?

Thanks!

suggest you leave the number field as a number data type, and add a separate
field for the text values you need. in a query, you can concatenate the two
fields so they show as one in the query dataset, as

NumberAndText: [MyNumberField] & " - " & [MyTextField]

hth


"Karl Burrows" wrote in message
...
I have a number formatted fields that now needs to have text added to it.
if I convert it to a text field then the sort order changes from record 1,
2, 3, etc. to 1, 10, 11, 12 and so on since it is now looking at text
values. is there a way to allow text at the end of the number value, but
still keep it sorting in numeric order?

Thanks!

For this very reason (sort order), I have sometimes maintained a field in two
ways:

e.g.
clauses 1, 1a, 2, 10, 10a clearly must be in that order, which may not be a
text order.
I keep, in one case, a separate field with the required sorted order.

You can see, just as well as I can, that text sorts in text order, numerics in
numeric order. You could sort on some mid$ (if you can identify it).

What are you asking? For some inbuilt Access method which you know doesn't
exist? Look at all the text manipulation functions to see what you can use.

I dunno. Keep the number and put the text in a further field?
(this appears to be Tina's suggestion, and nothing wrong with it just you have
to invent some method to separate stuff, if you want to sort another way,
clearly)

Chris ;-)

"Karl Burrows" wrote in message
...
I have a number formatted fields that now needs to have text added to it.
if I convert it to a text field then the sort order changes from record 1,
2, 3, etc. to 1, 10, 11, 12 and so on since it is now looking at text
values. is there a way to allow text at the end of the number value, but
still keep it sorting in numeric order?

Thanks!

The field value would be something like 123b. Probably only 1 letter after
the number, so how would you recommend I use the VAL in the table to get it
to sort correctly?

Thanks!

"Duane Hookom" wrote in message
...
You can sometimes use the Val() function against a mixed field to return the
numeric value of the leading numeric characters. For instance
Val("123 Summit") = 123
Val("Summit 123") = 0

--
Duane Hookom
MS Access MVP


"Karl Burrows" wrote in message
...
I have a number formatted fields that now needs to have text added to it.
if I convert it to a text field then the sort order changes from record 1,
2, 3, etc. to 1, 10, 11, 12 and so on since it is now looking at text
values. is there a way to allow text at the end of the number value, but
still keep it sorting in numeric order?

Thanks!

"Karl Burrows" wrote in message
...
The field value would be something like 123b. Probably only 1 letter
after
the number, so how would you recommend I use the VAL in the table to get
it
to sort correctly?

Thanks!

Use a query sorted by the Val function.

Select * from yourtable order by val([yourfield])

And you don't need to cross post to every newsgroup you can spell. People
tend to be more willing to help you if you are courteous.

Sorry, I just didn't know which group would be most appropriate. I didn't
think I was cross-posting, just multi-group posting the same message thread.
Some monitor particular groups and some others. Sorry!

"Randy Harris" wrote in message
...

"Karl Burrows" wrote in message
...
The field value would be something like 123b. Probably only 1 letter
after
the number, so how would you recommend I use the VAL in the table to get
it
to sort correctly?

Thanks!

Use a query sorted by the Val function.

Select * from yourtable order by val([yourfield])

And you don't need to cross post to every newsgroup you can spell. People
tend to be more willing to help you if you are courteous.

"Karl Burrows" wrote in message

Sorry, I just didn't know which group would be most appropriate. I
didn't think I was cross-posting, just multi-group posting the same
message thread.

That's what cross-posting is. Sure, it's better than multiposting,
which is when you post a separate message to each group to ask the same
question. But cross-posting to irrelevant groups is still frowned on.
You shouldn't be looking for maximum saturation for your message, you
should be looking to target it to the one or two groups where it is on
topic. In this case, the .queries group would probably have been best,
but I can understand how you might not realize it. The question is way
off-topic for the .setupconfig group.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Yea, you are right. I just wasn't sure, but now I know. Thanks!

"Dirk Goldgar" wrote in message
...
"Karl Burrows" wrote in message

Sorry, I just didn't know which group would be most appropriate. I
didn't think I was cross-posting, just multi-group posting the same
message thread.

That's what cross-posting is. Sure, it's better than multiposting,
which is when you post a separate message to each group to ask the same
question. But cross-posting to irrelevant groups is still frowned on.
You shouldn't be looking for maximum saturation for your message, you
should be looking to target it to the one or two groups where it is on
topic. In this case, the .queries group would probably have been best,
but I can understand how you might not realize it. The question is way
off-topic for the .setupconfig group.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk,

You mentioned a queries newsgroup, could tell me link to it? I can't seem to
find it. Thanks.
--
JoeCL
LACO-CAO


"Dirk Goldgar" wrote:

"Karl Burrows" wrote in message

Sorry, I just didn't know which group would be most appropriate. I
didn't think I was cross-posting, just multi-group posting the same
message thread.

That's what cross-posting is. Sure, it's better than multiposting,
which is when you post a separate message to each group to ask the same
question. But cross-posting to irrelevant groups is still frowned on.
You shouldn't be looking for maximum saturation for your message, you
should be looking to target it to the one or two groups where it is on
topic. In this case, the .queries group would probably have been best,
but I can understand how you might not realize it. The question is way
off-topic for the .setupconfig group.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)