Stripping A character from a string

E

Evan M

Hello,

I have a field that has a has a sequencial numbering system that began a A1
and is currently at A2500. I need to be able to sort these numerically when
the form opens. Does anyone have any suggestions?

Thanks,
Evan M
evan AT radiologyonesource DOT com
 
J

Jeff Boyce

Evan

Look into using the Mid() function to start at position #2 and use the
remainder. A query that includes something like:
Expr1: Mid([YourField],2)
would return all the characters after the first one. You could then sort on
that field in the query.

Good luck

Jeff Boyce
<Access MVP>
 
E

Evan M

Hi Jeff, Thanks for your reply. The character is properly stripped from the
ID, however when i sort the results in an "ascending" fashion, they begin
with 1, then follow with 10, 100, 1000, 1001... and so on. How is it that I
can sort them sequencially?

Thanks,
Evan M
evan AT radiologyonesource DOT com

Jeff Boyce said:
Evan

Look into using the Mid() function to start at position #2 and use the
remainder. A query that includes something like:
Expr1: Mid([YourField],2)
would return all the characters after the first one. You could then sort
on that field in the query.

Good luck

Jeff Boyce
<Access MVP>

Evan M said:
Hello,

I have a field that has a has a sequencial numbering system that began a
A1 and is currently at A2500. I need to be able to sort these numerically
when the form opens. Does anyone have any suggestions?

Thanks,
Evan M
evan AT radiologyonesource DOT com
Click to expand...
Click to expand...
 
J

Jeff Boyce

Sounds like they are characters, not numbers. You may also have to convert,
after stripping, to a numeric data type. Your expression might look
something like:

Expr1: CInt(Mid([YourField],2))

Good luck

Jeff Boyce
<Access MVP>

Evan M said:
Hi Jeff, Thanks for your reply. The character is properly stripped from
the ID, however when i sort the results in an "ascending" fashion, they
begin with 1, then follow with 10, 100, 1000, 1001... and so on. How is it
that I can sort them sequencially?

Thanks,
Evan M
evan AT radiologyonesource DOT com

Jeff Boyce said:
Evan

Look into using the Mid() function to start at position #2 and use the
remainder. A query that includes something like:
Expr1: Mid([YourField],2)
would return all the characters after the first one. You could then sort
on that field in the query.

Good luck

Jeff Boyce
<Access MVP>

Evan M said:
Hello,

I have a field that has a has a sequencial numbering system that began a
A1 and is currently at A2500. I need to be able to sort these
numerically when the form opens. Does anyone have any suggestions?

Thanks,
Evan M
evan AT radiologyonesource DOT com
Click to expand...
Click to expand...
Click to expand...
 
E

Evan M

Hi Jeff,

I added the "CInt()" around the original code as you had reccomended,
however, now i receive a "Data-Type mismatch" error. Do you have any
suggestions about what may be happening?

Thanks,
Evan
evan AT radiologyonesource DOT com



Jeff Boyce said:
Sounds like they are characters, not numbers. You may also have to
convert, after stripping, to a numeric data type. Your expression might
look something like:

Expr1: CInt(Mid([YourField],2))

Good luck

Jeff Boyce
<Access MVP>

Evan M said:
Hi Jeff, Thanks for your reply. The character is properly stripped from
the ID, however when i sort the results in an "ascending" fashion, they
begin with 1, then follow with 10, 100, 1000, 1001... and so on. How is
it that I can sort them sequencially?

Thanks,
Evan M
evan AT radiologyonesource DOT com

Jeff Boyce said:
Evan

Look into using the Mid() function to start at position #2 and use the
remainder. A query that includes something like:
Expr1: Mid([YourField],2)
would return all the characters after the first one. You could then
sort on that field in the query.

Good luck

Jeff Boyce
<Access MVP>

Hello,

I have a field that has a has a sequencial numbering system that began
a A1 and is currently at A2500. I need to be able to sort these
numerically when the form opens. Does anyone have any suggestions?

Thanks,
Evan M
evan AT radiologyonesource DOT com
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Overway

Use CLng instead of CInt

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Evan M said:
Hi Jeff,

I added the "CInt()" around the original code as you had reccomended,
however, now i receive a "Data-Type mismatch" error. Do you have any
suggestions about what may be happening?

Thanks,
Evan
evan AT radiologyonesource DOT com



Jeff Boyce said:
Sounds like they are characters, not numbers. You may also have to
convert, after stripping, to a numeric data type. Your expression might
look something like:

Expr1: CInt(Mid([YourField],2))

Good luck

Jeff Boyce
<Access MVP>

Evan M said:
Hi Jeff, Thanks for your reply. The character is properly stripped from
the ID, however when i sort the results in an "ascending" fashion, they
begin with 1, then follow with 10, 100, 1000, 1001... and so on. How is
it that I can sort them sequencially?

Thanks,
Evan M
evan AT radiologyonesource DOT com

Evan

Look into using the Mid() function to start at position #2 and use the
remainder. A query that includes something like:
Expr1: Mid([YourField],2)
would return all the characters after the first one. You could then
sort on that field in the query.

Good luck

Jeff Boyce
<Access MVP>

Hello,

I have a field that has a has a sequencial numbering system that began
a A1 and is currently at A2500. I need to be able to sort these
numerically when the form opens. Does anyone have any suggestions?

Thanks,
Evan M
evan AT radiologyonesource DOT com
Click to expand...
Click to expand...
Click to expand...
 
E

Evan M

Hi Paul, Jeff,

I found the error - a user had modified two records and added an extra alpha
character to the strings: 1111A and 1111B. The A and B were causing the
problem. Thanks for both of your help!

Evan M
evan AT radiologyonesource DOT com

Paul Overway said:
Use CLng instead of CInt

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Evan M said:
Hi Jeff,

I added the "CInt()" around the original code as you had reccomended,
however, now i receive a "Data-Type mismatch" error. Do you have any
suggestions about what may be happening?

Thanks,
Evan
evan AT radiologyonesource DOT com



Jeff Boyce said:
Sounds like they are characters, not numbers. You may also have to
convert, after stripping, to a numeric data type. Your expression might
look something like:

Expr1: CInt(Mid([YourField],2))

Good luck

Jeff Boyce
<Access MVP>

Hi Jeff, Thanks for your reply. The character is properly stripped from
the ID, however when i sort the results in an "ascending" fashion, they
begin with 1, then follow with 10, 100, 1000, 1001... and so on. How is
it that I can sort them sequencially?

Thanks,
Evan M
evan AT radiologyonesource DOT com

Evan

Look into using the Mid() function to start at position #2 and use the
remainder. A query that includes something like:
Expr1: Mid([YourField],2)
would return all the characters after the first one. You could then
sort on that field in the query.

Good luck

Jeff Boyce
<Access MVP>

Hello,

I have a field that has a has a sequencial numbering system that
began a A1 and is currently at A2500. I need to be able to sort these
numerically when the form opens. Does anyone have any suggestions?

Thanks,
Evan M
evan AT radiologyonesource DOT com
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sequencial Numbering without using Autonumber 3
Brew your own autonumber 2
Adding values to lstbxs 2
Clearing the captions of labels 2
Create an Outlook Contact from Access Data 2
Is that form open? 2
subform calculation 2
Error: 3464 Data type mismatch 2

Top