Antoher type mismatch error

[waylander32]

HI
Confused below is some code I'm using to filter a report on opening.

Private Sub cmd_specfic_Click()
DoCmd.OpenReport "rpt_specfic_panel_time_detail", acViewReport, , _
"[house number]='" & Me.txt_h_no & "'" And _
"[project code]= '" & Me.txt_pc & "' " And _
"[panel ref]='" & Me.txt_p_ref & "'"
End Sub

if I do each one seperatly they work as soon as I join using "and" I get a type mismatch error

can some one please point me in the right direction
cheers

 

[RuralGuy]

Put your WhereCondition in a string variable and you will see what is wrong.

 

[waylander32]

RuralGuy
thanks for the reply but, I fairly new to vba so can you explain in a bit more detail please

 

[RuralGuy]

Try and create a MsgBox with the same WhereCondition string. You have broken the string with your single and double quotes and the compiler can find it.

 

[waylander32]

tried it still get a type mismatch error
this may be due to my ignorance now so apologies if the code i copy below is "silly" tried a number if different combinations.

MsgBox (" & Me.txt_h_no & " And " & Me.txt_pc & " And " & Me.txt_p_ref & ")

 

[RuralGuy]

String fields need to be enclosed in quotes as you had before but you need to be more careful with the quotes since the entire argument is a string.
Change this:

"[house number]='" & Me.txt_h_no & "'" And _
"[project code]= '" & Me.txt_pc & "' " And _
"[panel ref]='" & Me.txt_p_ref & "'"

...to...

"[house number]='" & Me.txt_h_no & [COLOR="Red"]"' And " & _
[/COLOR]"[project code]= '" & Me.txt_pc & [COLOR="Red"]"'  And " & _
[/COLOR]"[panel ref]='" & Me.txt_p_ref & "'"

...if they are all string fields!

 

[waylander32]

worked
thank you very much

 

[RuralGuy]

Glad to help.