Query in VBA Question 1

[Ray1127]

I have a function in Access 2000 That Allows the user to type in a search string based on last name, First name or any portion of each Such as

Smi,Joh or Smith,J or Smith,John

It then builds an SQL Statment and runs the Query Displaying the results so the user can select the person they are looking for. If I open a new query and plug the generated SQL into it and run the query I get the expected results. If I run the exact same query in VBA using ADO I get no results. I don't understand why the query would work in 1 place and not in another.

SELECT qry_get_members.SBSB_ID as mbrid, qry_get_members.SBSB_LAST_NAME, qry_get_members.SBSB_First_NAME, qry_get_members.SBSB_MID_INIT, dbo_CMCV_MEME_BASE.MEME_BIRTH_DT as DOB FROM qry_get_members Where UCase(qry_get_members.SBSB_LAST_NAME) Like "SMI*" AND UCase(qry_get_members.SBSB_FIRST_NAME) Like "J*" Order By qry_get_members.sbsb_last_name, qry_get_members.sbsb_first_name

qry_get_members is a query to get a unique table of current members.

Any help is appreciated

 

[SkipVought]

and your CODE?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

Oops sorry

Public Function Find_mbrid() As String
Dim strsql As String, rs As New ADODB.Recordset
Dim strlname As String, strfname As String, lngcheck As Long, strname As String
Find_mbrid = ""
strname = InputBox("Enter Member Last Name, First Name or any portion of each." & vbCrLf & _
"Example: Smith,John Or Smi,Joh", "Search for Member")
strlname = (Left(strname, InStr(1, strname, ",") - 1))
strfname = Trim(Right(strname, Len(strname) - InStr(1, strname, ",")))
strsql = "SELECT qry_get_members.SBSB_ID as mbrid, qry_get_members.SBSB_LAST_NAME, " & _
"qry_get_members.SBSB_First_NAME, qry_get_members.SBSB_MID_INIT, " & _
"dbo_CMCV_MEME_BASE.MEME_BIRTH_DT as DOB " & _
"FROM qry_get_members "
strsql = strsql & "Where UCase(qry_get_members.SBSB_LAST_NAME) Like """ & UCase(strlname) & _
"*"" AND UCase(qry_get_members.SBSB_FIRST_NAME) Like """ & UCase(strfname) & "*"""
strsql = strsql & " Order By qry_get_members.sbsb_last_name, qry_get_members.sbsb_first_name"
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rs.EOF
lngcheck = MsgBox(Trim(rs!SBSB_LAST_NAME) & ", " & Trim(rs!SBSB_FIRST_NAME) & " " & Trim(rs!SBSB_MID_INIT) & _
" DOB: " & rs!DOB, vbYesNo, "Is this the correct person?")
If lngcheck = vbYes Then
Find_mbrid = rs!mbrid
Exit Do
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
If Find_mbrid = "" Then
MsgBox "Last Name not found please Retry", vbOKOnly, "Not found Error"
End If

End Function

 

[SkipVought]

Hi,

Used a debug.print and found...
[tt]
SELECT
qry_get_members.SBSB_ID as mbrid
, qry_get_members.SBSB_LAST_NAME
, qry_get_members.SBSB_First_NAME
, qry_get_members.SBSB_MID_INIT
, dbo_CMCV_MEME_BASE.MEME_BIRTH_DT as DOB

FROM qry_get_members

Where UCase(qry_get_members.SBSB_LAST_NAME) Like "METZ*"
AND UCase(qry_get_members.SBSB_FIRST_NAME) Like "CHA*"

Order By
qry_get_members.sbsb_last_name
, qry_get_members.sbsb_first_name

[/tt]
where is dbo_CMCV_MEME_BASE.???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

didn't notice that it should've been qry_get_members. Interesting that it still worked in the query window with the correct data????

Changed the sql in the function to

strsql = "SELECT qry_get_members.SBSB_ID as mbrid, qry_get_members.SBSB_LAST_NAME, " & _
"qry_get_members.SBSB_First_NAME, qry_get_members.SBSB_MID_INIT, " & _
"qry_get_members.MEME_BIRTH_DT as DOB " & _
"FROM qry_get_members "
strsql = strsql & "Where UCase(qry_get_members.SBSB_LAST_NAME) Like """ & UCase(strlname) & _
"*"" AND UCase(qry_get_members.SBSB_FIRST_NAME) Like """ & UCase(strfname) & "*"""
strsql = strsql & " Order By qry_get_members.sbsb_last_name, qry_get_members.sbsb_first_name"


had no effect still returns 0 data in VBA but returns 5 records in the query window.

 

[PHV]

With ADO the wildcard is % (not *)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ray1127]

Ok, had my idiot moment for the day. Thanks, that worked perfectly.