finding records matching an approximate value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to search a pile of records with a field matching all the
characters from a field in a form, but it doesn't have to be exact.
for example the field on the form may be: "d86-934"
and the field in the record could be: "00d86934"

Is this possible with something like this?
SELECT records FROM records
WHERE (((records.CONT_NO) Like
[Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM]));
Any help is greatly appreciated.
 
Change your Like statement to this and it should work:
Like "*" & [Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM] & "*"
 
PC datasheet's suggestion may work for you. It would not work for the specific
example you cited since the dash is not in the field in the record.

You can use wild card criteria, but you will only get a match up to a certain
point. I can't recall if you can reference a control on a subform in a query or
not. And I don't have something to test it with right now.

What version of Access are you using? You might be able to achieve the desired
results by using the replace function (Access 2002 and later) in conjunction
with wild cards if your input is fairly consistent. That is only numbers,
letters, and dashes and you want to ignore the dashes.

PC said:
Change your Like statement to this and it should work:
Like "*" & [Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM] & "*"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Ken said:
I would like to search a pile of records with a field matching all the
characters from a field in a form, but it doesn't have to be exact.
for example the field on the form may be: "d86-934"
and the field in the record could be: "00d86934"

Is this possible with something like this?
SELECT records FROM records
WHERE (((records.CONT_NO) Like
[Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM]));
Any help is greatly appreciated.
Click to expand...
Click to expand...
 
I appreciate the input, I ended up writing a function that places "%" after
every letter of the string referenced to the function. Then used this with
the SQL "LIKE". I am still not returning any results even when I change a
record to match, so it may be that I cant query the sub form. I am afraid I
still have to some serious thinking, because I am trying to match a string
that could be part of another string or vice versa so I would be looking for
a percentage of match. If I figure it out, I will post, in the mean time if
you come across something I would appreciate hearing.

John Spencer (MVP) said:
PC datasheet's suggestion may work for you. It would not work for the specific
example you cited since the dash is not in the field in the record.

You can use wild card criteria, but you will only get a match up to a certain
point. I can't recall if you can reference a control on a subform in a query or
not. And I don't have something to test it with right now.

What version of Access are you using? You might be able to achieve the desired
results by using the replace function (Access 2002 and later) in conjunction
with wild cards if your input is fairly consistent. That is only numbers,
letters, and dashes and you want to ignore the dashes.

PC said:
Change your Like statement to this and it should work:
Like "*" & [Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM] & "*"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Ken said:
I would like to search a pile of records with a field matching all the
characters from a field in a form, but it doesn't have to be exact.
for example the field on the form may be: "d86-934"
and the field in the record could be: "00d86934"

Is this possible with something like this?
SELECT records FROM records
WHERE (((records.CONT_NO) Like
[Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM]));
Any help is greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Is this an ADP or an MDB? Access with Jet (.mdb) uses different wild cards then
Access using ADO.

You might try using "*" vice "%". HOWEVER, this will still run into a problem
with the sample data you gave.

"d86-934" converted to "*d*8*6*-*9*3*4*" will not match "00d86934". There has
to be a "-" between the 6 and the 9 in there to get a match.


I appreciate the input, I ended up writing a function that places "%" after
every letter of the string referenced to the function. Then used this with
the SQL "LIKE". I am still not returning any results even when I change a
record to match, so it may be that I cant query the sub form. I am afraid I
still have to some serious thinking, because I am trying to match a string
that could be part of another string or vice versa so I would be looking for
a percentage of match. If I figure it out, I will post, in the mean time if
you come across something I would appreciate hearing.

John Spencer (MVP) said:
PC datasheet's suggestion may work for you. It would not work for the specific
example you cited since the dash is not in the field in the record.

You can use wild card criteria, but you will only get a match up to a certain
point. I can't recall if you can reference a control on a subform in a query or
not. And I don't have something to test it with right now.

What version of Access are you using? You might be able to achieve the desired
results by using the replace function (Access 2002 and later) in conjunction
with wild cards if your input is fairly consistent. That is only numbers,
letters, and dashes and you want to ignore the dashes.

PC said:
Change your Like statement to this and it should work:
Like "*" & [Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM] & "*"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I would like to search a pile of records with a field matching all the
characters from a field in a form, but it doesn't have to be exact.
for example the field on the form may be: "d86-934"
and the field in the record could be: "00d86934"

Is this possible with something like this?
SELECT records FROM records
WHERE (((records.CONT_NO) Like
[Forms]![Select_SH_NUM]![ROAD_HIST_TBL2].[Form]![CONT_NUM]));
Any help is greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top