Query runs perfectly in Query Designer but not in code

[Ray1127]

I have a Subroutine in an Access 2000 Module the Subroutine contains an ADODB Recordset. The SQL that the recordset is based on always returns an empty recordset however if I take that SQL and paste it into a the Query Designer and run it without modification it works perfectly returning all of the records it should. No clue as to why this is happening.

 

[Andrzejek]

Could you show the code of the Subroutine in question?

Have fun.

---- Andy

 

[Ray1127]

Private Sub cmdSearchmbr_Click()
Dim rs As New ADODB.Recordset
Dim strSQL As String, strLName As String, strFName As String, strDOB As String
Dim strSBSB_Id As String
strLName = InputBox("Please Enter the Members Last Name." & vbCrLf & "If no last name is entered this operation is canceled.", "Last Name")
If strLName = "" Then Exit Sub
strFName = InputBox("Please Enter the Members First Name" & vbCrLf & "If No First name is entered this operation is canceled.", "First Name")
If strFName = "" Then Exit Sub
strDOB = InputBox("Please Enter the Members DOB", "DOB")

strSQL = "SELECT dbo_CMCV_SBSB_BASE.SBSB_ID " & _
"FROM (dbo_CMCV_MEME_BASE INNER JOIN dbo_CMCV_SBSB_BASE ON " & _
"dbo_CMCV_MEME_BASE.SBSB_CK = dbo_CMCV_SBSB_BASE.SBSB_CK) " & _
"INNER JOIN dbo_CMCV_MEPE_BASE ON " & _
"dbo_CMCV_MEME_BASE.MEME_CK = dbo_CMCV_MEPE_BASE.MEME_CK " & _
"WHERE (((dbo_CMCV_MEPE_BASE.MEPE_EFF_DT) <= Now()) And " & _
"((dbo_CMCV_MEPE_BASE.MEPE_TERM_DT) >= Now()) And " & _
"((dbo_CMCV_MEPE_BASE.MEPE_ELIG_IND) = ""Y"")) And " & _
"Trim(UCase([MEME_LAST_NAME])) Like """ & UCase(strLName) & "*""" & _
" AND Trim(UCase([MEME_FIRST_NAME]))Like """ & UCase(strFName) & "*"""
If IsDate(strDOB) Then
strSQL = strSQL & " AND dbo_CMCV_MEME_BASE.MEME_BIRTH_DT = #" & _
strDOB & "# "
End If

rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
Do Until rs.EOF
strfilter = rs!sbsb_id & ","
rs.MoveNext
Loop
strfilter = Left(strfilter, Len(strfilter) - 1)

'Me.cboMemberID.
End If
rs.Close
Set rs = Nothing '

End Sub

 

[Andrzejek]

And what do you get in between the 2 lines (Debug statement) in Immediate Window:

       " AND Trim(UCase([MEME_FIRST_NAME]))Like """ & UCase(strFName) & "*"""
    If IsDate(strDOB) Then
        strSQL = strSQL & " AND dbo_CMCV_MEME_BASE.MEME_BIRTH_DT = #" & _
        strDOB & "# "
End If
[blue]
Debug.Print strSQL
[/blue]    
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Have fun.

---- Andy

 

[Ray1127]

SELECT dbo_CMCV_SBSB_BASE.SBSB_ID FROM (dbo_CMCV_MEME_BASE INNER JOIN dbo_CMCV_SBSB_BASE ON dbo_CMCV_MEME_BASE.SBSB_CK = dbo_CMCV_SBSB_BASE.SBSB_CK) INNER JOIN dbo_CMCV_MEPE_BASE ON dbo_CMCV_MEME_BASE.MEME_CK = dbo_CMCV_MEPE_BASE.MEME_CK WHERE (((dbo_CMCV_MEPE_BASE.MEPE_EFF_DT) <= Now()) And ((dbo_CMCV_MEPE_BASE.MEPE_TERM_DT) >= Now()) And ((dbo_CMCV_MEPE_BASE.MEPE_ELIG_IND) = "Y")) And Trim(UCase([MEME_LAST_NAME])) = "Smith" AND Trim(UCase([MEME_FIRST_NAME]))= "John"

The name was changed to protect the innocent (me)

 

[dhookom]

Ray,
How do you get that debug print result without any of the "Like" operators included in your code?


Duane
Hook'D on Access
MS Access MVP

 

[Ray1127]

After meeting with the user it was determined that the Like operators were no longer needed. It would have been a rare occurrence anyway but Management decided that if the user did not have the member id they would have the full first and last name they may or may not have the DOB. It can still result in mutiple records but with only 16000 members and less than 10% of them will be entered into this specialized DB that will be extremely rare.

 

[Andrzejek]

Just a guess here, but shouldn't names be in UPPER CASE here:

And Trim([red]UCase[/red]([MEME_LAST_NAME])) = "Smith" 
AND Trim([red]UCase[/red]([MEME_FIRST_NAME]))= "John"

and shouldn't names be with single quotes around them, not double quotes?

Have fun.

---- Andy

 

[Hap007]

Hi Ray,

Just a quick thought here.

Your query in code is using ADO, but when you cut and paste the SQL into the Access query designer window and run the code, then it is being run as a DAO connection.

Access itself uses DAO (at least back in 2000 Jet) as the default.

I would guess that is why one DAO works and the ADO does not work.

I would suggest changing the VBA code to DAO data type and try again.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Ray1127]

Once I took out the like everything worked fine. Apparently ADO did not like the like. Thanks for your help

 

[dhookom]

The "Like" wildcard in ADO is not the same as it is in DAO as suggested by Hap007.

Duane
Hook'D on Access
MS Access MVP