Number Format Field to Allow Text Value?

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

 

[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

 

[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

 

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

 

[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

 

[Randy Harris]

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]

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!

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.

 

[Dirk Goldgar]

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.

 

[Karl Burrows]

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

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.

 

[Possum1]

You could use a text field and still sort in numerical order-

If your largest number has five digits, convert numbers to text, concatenate
"0000" (a text string comprising four zero characters) to the left end of
your new string that represents the number, then trim the result to keep the
rightmost five characters. Then add a letter or letters to the right end.

Make sure when you convert the number to a text string that it doesn't have
a leading sign or space.

With the leading zeros, the alphabetic order will be the same as the numeric
order. All the string and value manipulation is done up front and you don't
have to worry about it when using the data later.

So, to get store 12c, convert the number 12 to the string "12", add "0000"
to get "000012", trim to "00012", add the "c" to get "00012c".

I've found that technique very useful elsewhere, but I'm very new to Access
so I don't think I'd better try to provide a code example.

Good luck.

 

[Guest]

Dirk,

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

 

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