Selective matching

[Guest]

I have a form called frmPackage with a list box containing a query.
I have part numbers (NPN) in a table called tblPackage that look like this:
123
123BB
123BB
456
456AA
456AA
456B
789
789C

The letters show a child relationship to the number only part-number.
I have made a listbox that contains a query that shows all the matches to
the current record's NPN, but it only shows exact matches. Such as if the
current record is 123, it shows 123 in the list box. If the current record
is 123BB, then it shows the two 123BB records.
I would like the list box to ignore the letters completely and show all the
matching NPNs to the current record's parent portion of the NPN. Like this:
If Current record is 123, then List box shows:
123
123BB
123BB
Or if Current record is 123BB then list box shows:
123
123BB
123BB

This is my current SQL that gives me exact matches only:

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE (((tblPackage.NNPN)=[Forms]![frmPackage]![NNPN]))
ORDER BY tblPackage.NNPN, tblPackage.ID;

I have tried using the Like statement but it only works properly when the
current record's NPN is numbers only. When the current record's NPN has a
letter suffix it won't show the parent NPN.

SELECT tblPackage.ID, tblPackage.NNPN, tblPackage.SI, tblPackage.[EFS LAR
NO], tblPackage.DeleteRecordFlag
FROM tblPackage
WHERE (((tblPackage.NNPN)=[Forms]![frmPackage]![NNPN] Or (tblPackage.NNPN)
Like [Forms]![frmPackage]![NNPN] & "[a-zA-Z]*"))
ORDER BY tblPackage.NNPN, tblPackage.ID;

I hope this is clear and not convoluted. I am self-taught and the
designated DB person in my group. I have been trying to get this for so long
now I am developing a nervous tick and it feels like a miniature pick-axe is
stuck in the back of my left eyeball.
Any help will be greatly appreciated.

Mike

 

[Guest]

The second SQL statement should read...

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE (((tblPackage.NPN)=[Forms]![frmPackage]![NPN] Or (tblPackage.NPN)
Like [Forms]![frmPackage]![NPN] & "[a-zA-Z]*"))
ORDER BY tblPackage.NPN, tblPackage.ID;

I removed of some of the SELECT fields and NNPN should be NPN.

Mike

 

[John Spencer]

Is the NPN always three digits followed by zero or more characters. IF so,
you could try

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE tblPackage.NPN LIKE Left([Forms]![frmPackage]![NPN],3) & "*"
ORDER BY tblPackage.NPN, tblPackage.ID;

Alternative is if the number portion never STARTS with a ZERO then try

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE tblPackage.NPN LIKE Val([Forms]![frmPackage]![NPN]) & "*"
ORDER BY tblPackage.NPN, tblPackage.ID;

This could run into problems if the NPN is ever something like
123E2 or 123D3 Since the val function reads those as scientific notation
and will return
12300 and 123000 respectively.

Of course the real solution would be to break the NPN into its component
parts and store those parts in two fields. One for the digital portion and
one for the alpha portion - PartNum and SubPartNum. Then you would be able
to do this easily.

If neither of the above solutions work, then I think you are going to have
to fix the data structure or use a custom VBA function that returns the
leading number characters of the NPN.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a form called frmPackage with a list box containing a query.
I have part numbers (NPN) in a table called tblPackage that look like
this:
123
123BB
123BB
456
456AA
456AA
456B
789
789C

The letters show a child relationship to the number only part-number.
I have made a listbox that contains a query that shows all the matches to
the current record's NPN, but it only shows exact matches. Such as if the
current record is 123, it shows 123 in the list box. If the current
record
is 123BB, then it shows the two 123BB records.
I would like the list box to ignore the letters completely and show all
the
matching NPNs to the current record's parent portion of the NPN. Like
this:
If Current record is 123, then List box shows:
123
123BB
123BB
Or if Current record is 123BB then list box shows:
123
123BB
123BB

This is my current SQL that gives me exact matches only:

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE (((tblPackage.NNPN)=[Forms]![frmPackage]![NNPN]))
ORDER BY tblPackage.NNPN, tblPackage.ID;

I have tried using the Like statement but it only works properly when the
current record's NPN is numbers only. When the current record's NPN has a
letter suffix it won't show the parent NPN.

SELECT tblPackage.ID, tblPackage.NNPN, tblPackage.SI, tblPackage.[EFS LAR
NO], tblPackage.DeleteRecordFlag
FROM tblPackage
WHERE (((tblPackage.NNPN)=[Forms]![frmPackage]![NNPN] Or (tblPackage.NNPN)
Like [Forms]![frmPackage]![NNPN] & "[a-zA-Z]*"))
ORDER BY tblPackage.NNPN, tblPackage.ID;

I hope this is clear and not convoluted. I am self-taught and the
designated DB person in my group. I have been trying to get this for so
long
now I am developing a nervous tick and it feels like a miniature pick-axe
is
stuck in the back of my left eyeball.
Any help will be greatly appreciated.

Mike

 

[Guest]

Thanks John for your response.

My numbers were actually 9 or 10 digit with a possible 2 or 3 character
suffix and no digits after that. So your second example worked for me
perfectly.

I recieved a 'function too complicated' error when it first loaded, which i
understand since this is an 'on the fly' listbox that checks for
duplicate/parent records but is working fine now only with a slight lag when
cycling through the records. We are fine with the lag however as the
functionality of it is of great help.

Thanks again John.
And thanks to all the contributers to this site.


Mike

Is the NPN always three digits followed by zero or more characters. IF so,
you could try

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE tblPackage.NPN LIKE Left([Forms]![frmPackage]![NPN],3) & "*"
ORDER BY tblPackage.NPN, tblPackage.ID;

Alternative is if the number portion never STARTS with a ZERO then try

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE tblPackage.NPN LIKE Val([Forms]![frmPackage]![NPN]) & "*"
ORDER BY tblPackage.NPN, tblPackage.ID;

This could run into problems if the NPN is ever something like
123E2 or 123D3 Since the val function reads those as scientific notation
and will return
12300 and 123000 respectively.

Of course the real solution would be to break the NPN into its component
parts and store those parts in two fields. One for the digital portion and
one for the alpha portion - PartNum and SubPartNum. Then you would be able
to do this easily.

If neither of the above solutions work, then I think you are going to have
to fix the data structure or use a custom VBA function that returns the
leading number characters of the NPN.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a form called frmPackage with a list box containing a query.
I have part numbers (NPN) in a table called tblPackage that look like
this:
123
123BB
123BB
456
456AA
456AA
456B
789
789C

The letters show a child relationship to the number only part-number.
I have made a listbox that contains a query that shows all the matches to
the current record's NPN, but it only shows exact matches. Such as if the
current record is 123, it shows 123 in the list box. If the current
record
is 123BB, then it shows the two 123BB records.
I would like the list box to ignore the letters completely and show all
the
matching NPNs to the current record's parent portion of the NPN. Like
this:
If Current record is 123, then List box shows:
123
123BB
123BB
Or if Current record is 123BB then list box shows:
123
123BB
123BB

This is my current SQL that gives me exact matches only:

SELECT tblPackage.ID, tblPackage.NPN
FROM tblPackage
WHERE (((tblPackage.NNPN)=[Forms]![frmPackage]![NNPN]))
ORDER BY tblPackage.NNPN, tblPackage.ID;

I have tried using the Like statement but it only works properly when the
current record's NPN is numbers only. When the current record's NPN has a
letter suffix it won't show the parent NPN.

SELECT tblPackage.ID, tblPackage.NNPN, tblPackage.SI, tblPackage.[EFS LAR
NO], tblPackage.DeleteRecordFlag
FROM tblPackage
WHERE (((tblPackage.NNPN)=[Forms]![frmPackage]![NNPN] Or (tblPackage.NNPN)
Like [Forms]![frmPackage]![NNPN] & "[a-zA-Z]*"))
ORDER BY tblPackage.NNPN, tblPackage.ID;

I hope this is clear and not convoluted. I am self-taught and the
designated DB person in my group. I have been trying to get this for so
long
now I am developing a nervous tick and it feels like a miniature pick-axe
is
stuck in the back of my left eyeball.
Any help will be greatly appreciated.

Mike