wildcard search

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

Guest

Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters at the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in [Table
B] and I can't use [Table B] to search [Table A]
 
Haggr said:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters at the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in
[Table
B] and I can't use [Table B] to search [Table A]
Click to expand...

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
 
Haggr said:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters at the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in [Table
B] and I can't use [Table B] to search [Table A]
Click to expand...


SELECT A.Items, B.Style
FROM [table A] As B INNER JOIN [table B] As B
ON A.Items Like B.Style & "*"

You can not create that kind of query in the QBE grid, so
you must use SQL View.
 
this is the error message I get when I try to run the query "Ambiguous Name
in Expression ([import].[item]) = fGetNums ([new billing].[new style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New Style]) do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



Gary Walter said:
Haggr said:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters at the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in
[Table
B] and I can't use [Table B] to search [Table A]
Click to expand...

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
 
"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
this is the error message I get when I try to run the query "Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New Style]) do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



Gary Walter said:
Haggr said:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in
[Table
B] and I can't use [Table B] to search [Table A]
Click to expand...

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
 
Sorry Gary I get the same error meassage

Isn't there a way to search for a record excluding specific letters on the
right side of search criteria record?

Ex. " finds xlb114" or "xs245waa" finds "xs345w" or "xw1204aaa/bt" finds
"xw1204"

Thanks Gary

Gary Walter said:
"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
this is the error message I get when I try to run the query "Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New Style]) do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



Gary Walter said:
:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in
[Table
B] and I can't use [Table B] to search [Table A]

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
Click to expand...
 
What is the name of the code module
that you saved the function in?

It cannot have same name as function.

Open the code module (double-click on it)
and at bottom of screen in Immediate Window,
type

?fGetNums("xs245waa")

then hit <ENTER>

do Immediate Window now look like:

?fGetNums("xs245waa")
245


Haggr said:
Sorry Gary I get the same error meassage

Isn't there a way to search for a record excluding specific letters on the
right side of search criteria record?

Ex. " finds xlb114" or "xs245waa" finds "xs345w" or "xw1204aaa/bt" finds
"xw1204"

Thanks Gary

Gary Walter said:
"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
this is the error message I get when I try to run the query "Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New Style])
do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



:

:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters
at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in
[Table
B] and I can't use [Table B] to search [Table A]

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
Click to expand...
 
"Billing"

Result after adding Immediate Window "Ambiguious Name Found fGetNums

I need xlb114r to find xlb114
or xs245waa to find xs245w

Thanks gary



Gary Walter said:
What is the name of the code module
that you saved the function in?

It cannot have same name as function.

Open the code module (double-click on it)
and at bottom of screen in Immediate Window,
type

?fGetNums("xs245waa")

then hit <ENTER>

do Immediate Window now look like:

?fGetNums("xs245waa")
245


Haggr said:
Sorry Gary I get the same error meassage

Isn't there a way to search for a record excluding specific letters on the
right side of search criteria record?

Ex. " finds xlb114" or "xs245waa" finds "xs345w" or "xw1204aaa/bt" finds
"xw1204"

Thanks Gary

Gary Walter said:
"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
On fGetNums([import].[item]) = fGetNums([new billing].[new style]);

:
this is the error message I get when I try to run the query "Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New Style])
do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



:

:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the letters
at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style] in
[Table
B] and I can't use [Table B] to search [Table A]

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
Click to expand...
 
Could you have copied function to
more than one code module?

I'm sorry, I had a long day at work
(started at 4:30 AM) so will try to
look back here in morning (mourning?)...

Haggr said:
"Billing"

Result after adding Immediate Window "Ambiguious Name Found fGetNums

I need xlb114r to find xlb114
or xs245waa to find xs245w

Thanks gary



Gary Walter said:
What is the name of the code module
that you saved the function in?

It cannot have same name as function.

Open the code module (double-click on it)
and at bottom of screen in Immediate Window,
type

?fGetNums("xs245waa")

then hit <ENTER>

do Immediate Window now look like:

?fGetNums("xs245waa")
245


Haggr said:
Sorry Gary I get the same error meassage

Isn't there a way to search for a record excluding specific letters on
the
right side of search criteria record?

Ex. " finds xlb114" or "xs245waa" finds "xs345w" or "xw1204aaa/bt"
finds
"xw1204"

Thanks Gary

:

"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
On fGetNums([import].[item]) = fGetNums([new billing].[new style]);

:
this is the error message I get when I try to run the query
"Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new
style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new
style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New
Style])
do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



:

:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the
letters
at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style]
in
[Table
B] and I can't use [Table B] to search [Table A]

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
Click to expand...
 
Sorry there was a dup. however here is what "xlb114r Returned when I inserted
a "Where" in the SQL.

item new style
XLB114R XH114
XLB114R XH114W
XLB114R XSW114
XLB114R XLB114
XLB114R XAN11-4
XLB114R XMR11/4
XLB114R XMR11/4W
XLB114R XE114
XLB114R XE114W

the return I need is "xlb114"

Prefixes are constant in both [Tables]
Sufixes are not "except for a couple of letters and I need results on them
too. EX. xs245waa should return xs245w.

Please reread the last couple of posts to see what I am trying to do.
Don't give up on me. I have to spend hours every day doing what this search
can do for me in minutes. Not to mention the accurancy in the math. I had to
write other programs just to check that, because I couldn't figure this out.
Please Help Me

Gary Walter said:
Could you have copied function to
more than one code module?

I'm sorry, I had a long day at work
(started at 4:30 AM) so will try to
look back here in morning (mourning?)...

Haggr said:
"Billing"

Result after adding Immediate Window "Ambiguious Name Found fGetNums

I need xlb114r to find xlb114
or xs245waa to find xs245w

Thanks gary



Gary Walter said:
What is the name of the code module
that you saved the function in?

It cannot have same name as function.

Open the code module (double-click on it)
and at bottom of screen in Immediate Window,
type

?fGetNums("xs245waa")

then hit <ENTER>

do Immediate Window now look like:

?fGetNums("xs245waa")
245


:
Sorry Gary I get the same error meassage

Isn't there a way to search for a record excluding specific letters on
the
right side of search criteria record?

Ex.("xlb114r" finds xlb114") or( "xs245waa" finds "xs345w")or ("xw1204aaa/bt" finds"xw1204")

Thanks Gary

:

"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
On fGetNums([import].[item]) = fGetNums([new billing].[new style]);

:
this is the error message I get when I try to run the query
"Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new
style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new
style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New
Style])
do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



:

:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the
letters
at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding [Style]
in
[Table
B] and I can't use [Table B] to search [Table A]

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
Click to expand...
 
This really breaks my "design ethos,"
but I think here be what you want:

Public Function fGetFirstChars_Nums_w(pString As Variant) As String
On Error GoTo Err_fGetFirstCharsNums
Dim i As Integer
Dim boolNum As Boolean
Dim ch As String
Dim tmp As String

If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
ch = Mid(pString, i, 1)
Select Case ch
Case "0" To "9"
If boolNum = False Then boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then Exit For
Case "-", "/"
'ignore
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
Else
Exit For
End If
End Select

Next
Else
tmp = vbNullString
End If

fGetFirstChars_Nums_w = tmp

Exit_fGetFirstCharsNums:
Exit Function

Err_fGetFirstCharsNums:
MsgBox Err.Description
Resume Exit_fGetFirstCharsNums
End Function


"Haggr"wrote:
Sorry there was a dup. however here is what "xlb114r Returned when I
inserted
a "Where" in the SQL.

item new style
XLB114R XH114
XLB114R XH114W
XLB114R XSW114
XLB114R XLB114
XLB114R XAN11-4
XLB114R XMR11/4
XLB114R XMR11/4W
XLB114R XE114
XLB114R XE114W

the return I need is "xlb114"

Prefixes are constant in both [Tables]
Sufixes are not "except for a couple of letters and I need results on them
too. EX. xs245waa should return xs245w.

Please reread the last couple of posts to see what I am trying to do.
Don't give up on me. I have to spend hours every day doing what this
search
can do for me in minutes. Not to mention the accurancy in the math. I had
to
write other programs just to check that, because I couldn't figure this
out.
Please Help Me

Gary Walter said:
Could you have copied function to
more than one code module?

I'm sorry, I had a long day at work
(started at 4:30 AM) so will try to
look back here in morning (mourning?)...

Haggr said:
"Billing"

Result after adding Immediate Window "Ambiguious Name Found fGetNums

I need xlb114r to find xlb114
or xs245waa to find xs245w

Thanks gary



:

What is the name of the code module
that you saved the function in?

It cannot have same name as function.

Open the code module (double-click on it)
and at bottom of screen in Immediate Window,
type

?fGetNums("xs245waa")

then hit <ENTER>

do Immediate Window now look like:

?fGetNums("xs245waa")
245


:
Sorry Gary I get the same error meassage

Isn't there a way to search for a record excluding specific letters
on
the
right side of search criteria record?

Ex.("xlb114r" finds xlb114") or( "xs245waa" finds "xs345w")or
("xw1204aaa/bt" finds"xw1204")

Thanks Gary

:

"import" does not "exist" because
it was still aliased as "A" in your query.

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
On fGetNums([import].[item]) = fGetNums([new billing].[new style]);

:
this is the error message I get when I try to run the query
"Ambiguous
Name
in Expression ([import].[item]) = fGetNums ([new billing].[new
style])"


[import] is [table A], [New Billing] is [table B]

Below is the Function and the SQL I entered:



SELECT [import].[item], [new billing].[new style]
FROM [import] as A INNER JOIN [new billing]
On fGetNums ([import].[item]) = fGetNums ([new billing].[new
style]);




Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case MID(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & MID(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function



One last thing. Some records in [table B] ([New Billing].[New
Style])
do
end with
"W" and I would like to get those as well.

Thanks so Much I've been working at this for months



:

:
Table A
[Items]

xlb114r
xj20aaa
xsw3456qt

Table B

[Style]
xbl114
xj20
xsw3456

Notice the similaries between [Items] and [Style], just the
letters
at
the
end of [Style]

How do I use [Table A].[Items] to find the corresponding
[Style]
in
[Table
B] and I can't use [Table B] to search [Table A]

I'd probably just save the following function
in a code module (say "modUtility")

Public Function fGetNums(pString As Variant) As String
On Error GoTo Err_fGetNums
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
Select Case Mid(pString, i, 1)
Case 0 To 9
fGetNums = fGetNums & Mid(pString, i, 1)
Case Else
'ignore (not number digit)
End Select

Next
Else
fGetNums = vbNullString
End If
Exit_fGetNums:
Exit Function

Err_fGetNums:
MsgBox Err.Description
Resume Exit_fGetNums
End Function

then

SELECT
A.Items,
B.Style
FROM
[Table A] As A
INNER JOIN
[Table B]
ON
fGetNums(A.Items) = fGetNums(B.Style);

the function isn't really done....
if you had string "xx123yy4"
it would return "1234"
rather than "123"

but, it did not sound like you
have such strings?
Click to expand...
Click to expand...
Click to expand...
 
Public Function fGetFirstChars_Nums_w(pString As Variant) As String
On Error GoTo Err_fGetFirstCharsNums
Dim i As Integer
Dim boolNum As Boolean
Dim ch As String
Dim tmp As String

If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
ch = Mid(pString, i, 1)
Select Case ch
Case "0" To "9"
If boolNum = False Then boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then Exit For
Case "-", "/"
'ignore
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
Else
Exit For
End If
End Select

Next
Else
tmp = vbNullString
End If

fGetFirstChars_Nums_w = tmp

Exit_fGetFirstCharsNums:
Exit Function

Err_fGetFirstCharsNums:
MsgBox Err.Description
Resume Exit_fGetFirstCharsNums
End Function
 
This does not seem very efficient...

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
On
fGetFirstChars_Nums_w([import].[item]) =
fGetFirstChars_Nums_w([new billing].[new style]);

Is there a chance you could add a field
(say "MatchString") to each table?

Then, you could run update queries on each
table using the function...

UPDATE [import]
SET MatchString =
fGetFirstChars_Nums_w([item]);

UPDATE [new billing]
SET MatchString =
fGetFirstChars_Nums_w([new style]);

then, a parameter query would be really fast:

SELECT
[import].[item],
[new billing].[new style]
FROM
[import] INNER JOIN [new billing]
On
[import].MatchString = [new billing].MatchString
WHERE
[import].[item] = [Please Enter import item];

///////////////

or...you could have main form bound to [import]
and subform bound to [new billing],
then set your "Master/Child" form/subform link
on the MatchString fields.

so you select an [import] record in main form,
and matching [new billing] record(s) appear in subform...
 
WOW That did it Out of 10,000 records all but about 100 did what I needed,
some style started with numbers. But the style your function gave it will
work just fine.

Who are you?
How do you know this stuff?
Do you get paid to do this?
Anyway to reach you in the future?


Ps: Named the query QryStyleGary

Gary Walter said:
This does not seem very efficient...

SELECT [import].[item], [new billing].[new style]
FROM [import] INNER JOIN [new billing]
On
fGetFirstChars_Nums_w([import].[item]) =
fGetFirstChars_Nums_w([new billing].[new style]);

Is there a chance you could add a field
(say "MatchString") to each table?

Then, you could run update queries on each
table using the function...

UPDATE [import]
SET MatchString =
fGetFirstChars_Nums_w([item]);

UPDATE [new billing]
SET MatchString =
fGetFirstChars_Nums_w([new style]);

then, a parameter query would be really fast:

SELECT
[import].[item],
[new billing].[new style]
FROM
[import] INNER JOIN [new billing]
On
[import].MatchString = [new billing].MatchString
WHERE
[import].[item] = [Please Enter import item];

///////////////

or...you could have main form bound to [import]
and subform bound to [new billing],
then set your "Master/Child" form/subform link
on the MatchString fields.

so you select an [import] record in main form,
and matching [new billing] record(s) appear in subform...


Gary Walter said:
Public Function fGetFirstChars_Nums_w(pString As Variant) As String
On Error GoTo Err_fGetFirstCharsNums
Dim i As Integer
Dim boolNum As Boolean
Dim ch As String
Dim tmp As String

If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
ch = Mid(pString, i, 1)
Select Case ch
Case "0" To "9"
If boolNum = False Then boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then Exit For
Case "-", "/"
'ignore
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
Else
Exit For
End If
End Select

Next
Else
tmp = vbNullString
End If

fGetFirstChars_Nums_w = tmp

Exit_fGetFirstCharsNums:
Exit Function

Err_fGetFirstCharsNums:
MsgBox Err.Description
Resume Exit_fGetFirstCharsNums
End Function
Click to expand...
Click to expand...
 
Haggr said:
WOW That did it Out of 10,000 records all but about 100 did what I
needed,
some style started with numbers. But the style your function gave it will
work just fine.
Click to expand...

Thanks for the update. Glad it worked for you!
 

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

duplicate records 2
search query 0
Include text as part of Heading style 3
Dcount returning no results!!! 0
How to query 2 tables and then perform a wildcard search using a 3 1
Query-finding umatched data with duplicates 5
Parsing some info out of HTML - can you help me please? 3
Microsoft Word 2017 VBA Troubleshooting Restart Numbering Macro 0

Back
Top