Run-Time Error 13 - Type Mismatch

[crisb184]

Hello,

I am trying to populate a list box with an event after update in a combo box. I can get the formula to work using 2 criteria, the problem is i nee to add a third criteria. When I try to add it I get the run-time 13 error.

Here is the code I am trying to use:

Private Sub cboStatusRFQ_AfterUpdate()
Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool.RFQ Contact] " & _
                            "FROM Consolidated_Master_Req_Pool " & _
                            "WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool.RFQ Supplier] = '" & Nz(cboStatusRFQ.Value) & "'" And "[cosolidated_master_req_pool.Status] = '" & "[SUPPLIER_RFQ FOLLOW-UP]" & "'" & _
                            "ORDER BY [Consolidated_Master_Req_Pool.RFQ Contact];"
    Me.cboSupplier = Null
End Sub

Any help to get this formula to work is greatly appreciated.

Thanks,
Cris

 

[pbaldy]

Yeah, you've goofed the concatenation. If the third field is text:

 "WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool.RFQ Supplier] = '" & Nz(cboStatusRFQ.Value) & "' And [cosolidated_master_req_pool.Status] = '" & [SUPPLIER_RFQ FOLLOW-UP] & "' " & _

If it's numeric, drop the single quotes around the value.

 

[CJ_London]

The error is what it says on the tin - you are not comparing types of the same sort because you have your double quotes mixed up. There is also a typo, your square brackets are wrong and you are missing a space and spaces in form control names are replaced with an underscore
try

Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool[COLOR=red]].[[/COLOR]RFQ Contact] " & _
                            "FROM Consolidated_Master_Req_Pool " & _
                            "WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool[COLOR=red]].[[/COLOR]RFQ Supplier] = '" & Nz([COLOR=red]Me.cboStatusRFQ[/COLOR]) & [COLOR=red]"' And[/COLOR] [[COLOR=red]con[/COLOR]solidated_master_req_pool[COLOR=red]].[[/COLOR]Status] = '" [COLOR=red]& Me.[/COLOR]SUPPLIER_RFQ[COLOR=red]_[/COLOR]FOLLOW-UP[COLOR=red] &[/COLOR] [COLOR=red]"' "[/COLOR] & _
                            "ORDER BY [Consolidated_Master_Req_Pool[COLOR=red]].[[/COLOR]RFQ Contact];"

This assumes that cboStatusRFQ and SUPPLIER_RFQ FOLLOW-UP are both strings. If they are not, you should remove the single quotation marks