error select records based on 2 WHERE conditions 1

[paulminne]

Hi,

I have a form with a text box and a combo box. I am wanting to match a record based on the entries in the text and combo fields, however when I run the SQL I am getting an error stating 'Type Mismatch'.

The code is:
******************************

Private Sub cmdPrint()

Dim strInput as String
Dim strName as String
Dim db as Database
Dim rs as ADODB.Recordset
Dim strSQL as String
Dim strLink as String
Dim strLinkCriteria as String
Dim stDocName as String

stDocName = "WeeklyJobs"

strInput = txtDate.value
strName = cboConsultant.column(0)

set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection

stLinkCriteria = "[JobDate]= #" & strInput & "#"
strLink = "[Consultant]= '" & strName & "'"

------------------------------------------------
strSQL = "SELECT * FROM JobsBooked2 WHERE " & _
stLinkCriteria AND strLink

ERROR IN THE ABOVE CODE
-------------------------------------------------

set db = CurrentDb

rs.open strSQL

If (rs.EOF) Then
msgbox "no matching records"
Else
DoCmd.OpenReport stDocName, acViewNormal, , stLink
DoCmd.Close acReport, stDocName
End If

set rs = Nothing
set db = Nothing

End Sub
****************************************

Any help is appreciated,

- Paul

 

[Nikita6003]

Hiya,

I suspect you'll find the variable names in the SQL insteda of the two WERE statements you've assigned to them ... try changing:

strSQL = "SELECT * FROM JobsBooked2 WHERE " & _
             stLinkCriteria AND strLink

to

strSQL = "SELECT * FROM JobsBooked2 WHERE " & _
             stLinkCriteria & " AND " & strLink

HTH!

Cheers
Nikki

 

[paulminne]

Thankyou...that was exactly my problem because it is working fine now...

Thanks heaps,

- Paul