How to query and Sort a text field as a number field

[DukeArgos]

In access, i'm trying to sort a text field.

99.9% of the contents of all the records contained in the text field are numbers. I don't want to permanently make the field a number, i just want to sort it as if it were a number...

Can anyone help me?

thanks

 

[Mile-O]

Put a new field into the query and use the CLng() function

ie.

NewField: CLng(OldField)

 

[DukeArgos]

Mile-O-Phile,

Thanks for responding and trying to help me!!!

ok, it's official -- i'm an idiot when it comes to this...

i don't know how to implement what you suggested...

here is the sql query i currently have generated from access using the query designer:

SELECT Structures.ADD_NUM, Structures.PREDIR, Structures.ST_NAME, Structures.ST_TYPE, Structures.NOTES, Structures.OADDRESS, Structures.OSPREFIX, Structures.OSNAME, Structures.OSTYPE, Structures.HOUSEHEAD, Structures.COMMUNITY
FROM Structures
WHERE (((Structures.JURISDICTION)="MY CITY"))
ORDER BY Structures.COMMUNITY, Structures.ST_NAME, Structures.ADD_NUM;

The field "structures.ADD_NUM" is the text field that i wish to have sorted as a number field.

Is there anything more specific you can help me to do this correctly?

thanks again...

 

[Mile-O]

Copy and paste this SQL in an replace what you have. Then look at the query in the design grid. You'll see what I meant then...


SELECT CLng(Structures.ADD_NUM) AS NewField, Structures.PREDIR, Structures.ST_NAME, Structures.ST_TYPE, Structures.NOTES, Structures.OADDRESS, Structures.OSPREFIX, Structures.OSNAME, Structures.OSTYPE, Structures.HOUSEHEAD, Structures.COMMUNITY
FROM Structures
WHERE (((Structures.JURISDICTION)="MY CITY"))
ORDER BY Structures.COMMUNITY, Structures.ST_NAME, Structures.ADD_NUM;

 

[DukeArgos]

Thanks again!!!

I was able to follow what you suggested and copied and pasted the sql statement you provided and then I saw the changes that occured in the design view. However, i'm still not getting it sorted the way i was hoping.

I'm getting the following results:

1 MAIN ST
11 MAIN ST
2 MAIN ST
34 MAIN ST
303 MAIN ST
4 MAIN ST
42 MAIN ST

I did also try changing the "order by" statment to:
ORDER BY Structures.COMMUNITY, Structures.ST_NAME, Structures.NewField;

But this created a prompt window that came up asking:
Enter Paramater Value
Structures.NewField
_________________
|________________|
ok cancel

Can you provide any other suggestions??

thanks for your help

 

[Mile-O]

Change the last part...

ORDER BY CLng(Structures.ADD_NUM), Structures.COMMUNITY, Structures.ST_NAME;

 

[DukeArgos]

ahhhhhhhhhhh, with your help -- i think i figured it out..

SELECT Structures.ADD_NUM, Structures.PREDIR, Structures.ST_NAME, Structures.ST_TYPE, Structures.NOTES, Structures.OADDRESS, Structures.OSPREFIX, Structures.OSNAME, Structures.OSTYPE, Structures.HOUSEHEAD, Structures.COMMUNITY
FROM Structures
WHERE (((Structures.JURISDICTION)="MY CITY"))
ORDER BY Structures.COMMUNITY, Structures.ST_NAME, CLng(Structures.ADD_NUM);

this seems to work...

THANKS for your help!!!

 

[Mile-O]

No probs.

 

[DukeArgos]

Hmmm,

Now the query is working fine -- but when i create a "report" -- the report is changing the sorted order back to disregard the CLng function...

I posted more details of the report problem in the report forum here:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=57605

i'm not exactly sure if this is a query problem or a report problem...

any ideas?

thanks

 

[DukeArgos]

i ended up getting this to work using Mile-O-Phile's original suggestion of a "newfield"... (at the time, i didn't exactly understand why i needed a newfield -- but now i do...)

I couldn't get the sort to work in the report because it didn't seem to accept the clng function in the report. If i made a "newfield" that was truely nummeric, i could then use it in the report to sort by...

thanks Mile-O-Phile!!!