Number Format Field to Allow Text Value?

K

Karl Burrows

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!
 
D

Duane Hookom

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
 
T

tina

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
 
C

Chris Mills

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 ;-)
 
K

Karl Burrows

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!

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
 
R

Randy Harris

Karl Burrows said:
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!
Click to expand...

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.
 
K

Karl Burrows

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!


Karl Burrows said:
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!
Click to expand...

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.
 
D

Dirk Goldgar

Karl Burrows said:
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.
Click to expand...

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.
 
K

Karl Burrows

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

Karl Burrows said:
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.
Click to expand...

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.
 
J

JoeCL

Dirk,

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

Douglas J. Steele

The best way is to use a newsreader, rather than relying on the web
interface.

If you've got Internet Explorer, you've already got Outlook Express. Point
to msnews.microsoft.com, and there are hundreds of newsgroups available.

If you stick with the web interface, if you go to
http://msdn.microsoft.com/newsgroups/ and select Office Solutions
Development from the left hand side, Access Queries is about three-quarters
of the way down the list under Access.
 
Top