Sorting Text Field containg letters and numbers

[tpeter]

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[tpeter]

What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

 

[John Spencer]

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[tpeter]

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[KARL DEWEY]

Try this --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

:

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[tpeter]

I have copied it into the SQL statement and it is missing some syntax.

Try this --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

tpeter wrote:
What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

:

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[KARL DEWEY]

Had extra parenthesis --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE ((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

I have copied it into the SQL statement and it is missing some syntax.

Try this --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

:

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

tpeter wrote:
What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

:

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[tpeter]

Karl,

I would like to say Thank you for all of your assistance. I copied the code
into the SQL and it is still saying that it is to complex. When the query
runs a message box comes up asking for a number+Alpha+* to narrow down the
search(30,000 records based of of PartNumber Field). When I put in 22907* an
error comes up saying it is to complex. When I type 22907G036 it works, but
that is only because there is one of them. Could the wildcard be messing with
the statement?

Tim Peter

Had extra parenthesis --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE ((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

I have copied it into the SQL statement and it is missing some syntax.

Try this --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.


:

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

:

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

tpeter wrote:
What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

:

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[John Spencer]

There is the possibility of an error if Sheet is ever null. Val(Null)
generates an error.

Try this simple modification to the expression.

IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet] & ""))


Sorry, I didn't catch that earlier.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[tpeter]

This statement does what it need to, it just doesn't take into account the
second digit:

Part Sheet
22907P031 1
22907P027 13
22907P016 6 67
22907P028 68 68
22907P015 7 7
22907G036 G036

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]))
ORDER BY tblPartNumber.Sheet;

Karl,

I would like to say Thank you for all of your assistance. I copied the code
into the SQL and it is still saying that it is to complex. When the query
runs a message box comes up asking for a number+Alpha+* to narrow down the
search(30,000 records based of of PartNumber Field). When I put in 22907* an
error comes up saying it is to complex. When I type 22907G036 it works, but
that is only because there is one of them. Could the wildcard be messing with
the statement?

Tim Peter

Had extra parenthesis --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE ((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

I have copied it into the SQL statement and it is missing some syntax.

:

Try this --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.


:

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

:

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

tpeter wrote:
What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

:

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[KARL DEWEY]

Use this --
WHERE ((tblPartNumber.Part) Like [Enter Number+Alpha]) & "*"

Do not type 22907* but just 22907 to find 22907G036. I removed the asterisk
from the prompt.

--
Build a little, test a little.

Karl,

I would like to say Thank you for all of your assistance. I copied the code
into the SQL and it is still saying that it is to complex. When the query
runs a message box comes up asking for a number+Alpha+* to narrow down the
search(30,000 records based of of PartNumber Field). When I put in 22907* an
error comes up saying it is to complex. When I type 22907G036 it works, but
that is only because there is one of them. Could the wildcard be messing with
the statement?

Tim Peter

Had extra parenthesis --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE ((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.

I have copied it into the SQL statement and it is missing some syntax.

:

Try this --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));

--
Build a little, test a little.


:

John,

Thank you for your response. There will never be more than 1 letter and most
of the time there will only be numbers. I have put the iff statement in the
criteria and changed the "MyField" to Sheet, when I run the query it says it
is to complex. Attached is the SQL Stement:

SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet AS Sort
FROM tblPartNumber
WHERE (((tblPartNumber.Part) Like [Enter Number+Alpha*]) AND
((tblPartNumber.Sheet)=IIf([Sheet] Like
"[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]))));

I have also been trying:
Right([Sheet],3) in the criterial, but this only sorts by the 1st digit so 4
could be after 39.

Am I missing something?
Thanks again for your assistance.

Tim Peter

:

Ok, if you never have more than one letter character then you can use an
expression like this to get the numeric value and then sort by this caclulated
field.

Field: NumberSort: IIF([MyField] Like "[!0-9]*", Val(Mid([MyField],2)),
Val([MyField]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

tpeter wrote:
What I am attempting to do is when it sorts the field in assending order if
there is a letter, ignore the letter and sort by the numbers, if there is no
letter before the numbers sort them as numbers. I am unable to make this into
two fields because of company formatting.

:

I have a field that is forced to be a text field. The field is called [sheet]
and contains numbers. This format can vary from 1 to J256. When I run my
query I am unable to sort the data by sheet because it doesn't recognize it
as a number(because of the occational letter). Is there something I can put
into the criterial area that will make it sort in assending order? The
problem is sometimes there is a letter in from of the number and sometimes
not. Thank you for your help, it is greatly appreciated.

Tim Peter

 

[tpeter]

That was it. Sorry my bad, I didn't even think about Null's, and there were
some. Thank both of you for all of your help and experience.

There is the possibility of an error if Sheet is ever null. Val(Null)
generates an error.

Try this simple modification to the expression.

IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet] & ""))


Sorry, I didn't catch that earlier.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Had extra parenthesis --
SELECT tblPartNumber.Part, tblPartNumber.Sheet, tblPartNumber.Title,
tblPartNumber.Reference, tblPartNumber.Type, tblPartNumber.Sheet,
IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet])) AS Sort
FROM tblPartNumber
WHERE ((tblPartNumber.Part) Like [Enter Number+Alpha*])
ORDER BY IIf([Sheet] Like "[!0-9]*",Val(Mid([Sheet],2)),Val([Sheet]));