Search by Character then number. 1

[paul123456]

Hello everyone. right now i have a database that allows you to search by id number. the record is set to number. I have a lot of different queries that call for search criterias. We need to now add 2 letters in front of the ID number. to make it look as follow AD2343 instead of 2343. how can i make my search skip/ignore the first two letters??

thanks guys

Thanks, PAUL

 

[Steve101]

One way is to use the LIKE operator in your SQL criteria; for example:
[tt]
SELECT *
FROM tblYourTable
WHERE YourSearchId LIKE "??1234"
[/tt]
Will return all records where the third through 6th character of the id field are "1234", irrespective of the value of the first two characters.

You could also use the mid() and right() functions to segment off just the characters that you want to use for the comparison; eg:
[tt]
...
WHERE mid(YourSearchId,3) = "1234"
[/tt]
or
[tt]
WHERE right(YourSearchId,4) = "1234"
[/tt]
Check online help to get a better understanding of these functions.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TonyJollans]

Hi paul123456,

You are changing your field from numeric to alphanumeric, so it rather depends on how you use it. To simply ignore the first two characters and treat the rest as text you can use something like ..

[blue][tt]Mid(IDNumber],3) = "2343"[/tt][/blue]

If you need it to be numeric you need to add a function to convert the string to a number, for example ..

[blue][tt]Val(Mid(IDNumber],3)) = 2343[/tt][/blue]

I would suggest, however, that you consider whether you really need to add the two letters to your number. If you are continuing to query by the number, it would seem that the letters are not adding value; if you need to store the letters at all can you not store them in a separate column?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[paul123456]

I think i did my wording wrong, but if I have multiple different queries that ask for different ranges of ID numbers. i have 1 type that ask for the single ID..then another that asks for a range and another that asks for multiple. basically a select, between and IDLISt statement. why i need this done you ask??

i have ID numbers for different states

TX4858 will be that id number in Texas..but i can have the same id number in Florida..so i need to seperate the two same claim numbers by the first two initials of the state..

So if i need to search a id number..i would put in a state code then number. but i don't understand how i can select a range of numbers if they are converted to text? i can't simply put FL5000 and FL5500 can i? how can the user put the two letters in and still be able to query the letter and pick the range of numbers?

Thanks, PAUL

 

[SteveR77]

Paul,

If the ID is always going to be two character state then numeric value you could do this in a query -

State:Left([ID],2)

IDNum: CInt(Right([ID],4))

The sort the state field then ID Num.

HTH,

Steve

 

[paul123456]

Steve were would i put that in my between statement in my query? Also i have some id numbers that are 2 digits and some up to 7...the ID numbers will range as the letters will stay 2 characters always?

Thanks, PAUL

 

[SteveR77]

Paul,

Modify the ID Num to be this -

IDNum: CInt(Right([ID],Len([ID])-2))

That will give you the ID number regardless of how long or short it is since the state will always be two characters.


HTH,

Steve

 

[TonyJollans]

Hi Paul,

If your numbers an be that big, then you cannot use Integers. Also, if the length is unknown, it is easier to use the Mid Function without specifying a length.

[blue]CLng(Mid([ID], 3))[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[paul123456]

Hey guys im a little confused as to where in my query i sould place this info at? what happends if i have a between statement and i want to search from and to id numbers? like if i where to have a query selecting info from a table and a where between statement? how would this look with CLng(Mid([ID], 3))?

Thanks, PAUL

 

[cmmrfrds]

Paul, this is a good example of why the primary key of a table should not carry intelligence i.e. the state identification. Make the primary key of the table an autonumber and the id and state separate fields. Use both the state and the id as select criteria in the queries. The state and id are the logical key for the table and the autonumber is the primary key. This is a common practice in modeling a database and the physical implementation.

 

[paul123456]

Well..the primary key is another number. this is just 1 field that i need to be able to search by..but by skipping the first 2 characters?? I can't simply use Between [Enter start ID Number] And [Enter end ID Number]. Im wondering if there is a way to include something that skips the first 2 characters and only reads the rest as numbers to i can sort and pull out information in groups of numbers?

Thanks, PAUL

 

[BobAtHome]

Paul

Not sure what you want as the stating of your needs is a little difficult to make out exactly what you want to do.

However, I get the idea that you want a user to be able to enter a 2 character state designator and a from/to number range.

To test a solution, I created a 1 field table - Table1.IdNumber
I entered the following data.

IdNumber
--------
FL1
FL345
FL45
FL6700
FL8095433
FL8098
TX6789
TX9
TX987654
NY35
NY4576
FL1456
TX33667

I queried for state "FL' and number range from/to 40 / 7000

SELECT IdNumber
FROM Table1
WHERE Left([IdNumber],2)="FL" AND CLng(Mid([IdNumber],3)) Between 40 And 7000;

The results are:
IdNumber
--------
FL45
FL6700
FL345
FL1456

If you just want to search a from/to range by just the numeric part (ignoring the state), it would be as follows.

SELECT IdNumber
FROM Table1
WHERE CLng(Mid([IdNumber],3)) Between 40 And 7000;

The results are:
IdNumber
--------
FL45
FL6700
TX6789
FL345
NY4576
FL1456

Is this what you are looking for??

 

[paul123456]

Im getting this error This expression is typed incorrectly, or it is too complex to be evaluated. For example expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to varibles???? heres what i have

SELECT Main.FirstNm, Main.M, Main.LastNm, Main.Address1, Main.Address2, Main.City, Main.State, Main.Zip, ID.IDNum, Main.SocSecID;
FROM Main INNER JOIN ID ON Main.SocSecID = ID.SocSecID WHERE Left([ID.IDNum],2)="CA" AND CLng(Mid([Main.IDNum],3)) Between [Please enter start ID Number] And [Please enter end ID Number] ORDER BY ID.IDNum DESC;

im not sure what the ="CA" does? can i put wild cards in place of this??? since the user will be typing in CA3242 - CA3500?

Thanks, PAUL

 

[BobAtHome]

Paul

I'll get back to you on a day or two with this. I have some suggestions but I'm a little busy right now.

Bob

 

[BobAtHome]

Your query was as follows:

SELECT Main.FirstNm, Main.M, Main.LastNm, Main.Address1, Main.Address2, Main.City, Main.State, Main.Zip, ID.IDNum, Main.SocSecID;
FROM Main INNER JOIN ID ON Main.SocSecID = ID.SocSecID WHERE Left([ID.IDNum],2)="CA" AND CLng(Mid([Main.IDNum],3)) Between [Please enter start ID Number] And [Please enter end ID Number] ORDER BY ID.IDNum DESC;

You have a semi-colon after Main.SocSecID; - don't need it. The "CA" is there as I thought you wanted to treat the state part of the field separately. The user would enter the state then the range, as in - "WHERE Left([ID.IDNum],2)= [Enter State] AND".

However since you don't want that use the query below:

SELECT ID.IDNum, Main.FirstNm, Main.M, Main.LastNm, Main.Address1, Main.Address2, Main.City, Main.State, Main.Zip, Main.SocSecID
FROM ID INNER JOIN Main ON ID.SocSecID = Main.SocSecID
WHERE ID.IDNum Between [enter start ID Number] And [enter end ID Number];

Bob