Type mismatch in expression (Access 2000)

Topic

New Reply

Type mismatch in expression

I have the folloiwing valid record source in a report :
Dim Bas01 as String
bas01 = ” SELECT tblClients.CompanyName, tblClients.City, tblOffers.offerdate, tblOffers.EmployeeID,

tblClients.kindid, tblClients.ClientID, tblClients.afid ” & _
” FROM (tblClients INNER JOIN tblOffers ON tblClients.ClientID = tblOffers.Clientid) INNER JOIN

tblOfferDetails ON tblOffers.offerid = tblOfferDetails.offerid ” & _
” WHERE (((tblClients.afid) = 1)) ” & _
” GROUP BY tblClients.CompanyName, tblClients.City, tblOffers.offerdate, tblOffers.EmployeeID,

tblClients.kindid, tblClients.ClientID, tblClients.afid;”

I want to replace the expression WHERE (((tblClients.afid) = 1)) with the name of the
function StrClients which is the following :

Public Function StrClients()
Select Case Forms![FBenchmark]![Office]
Case 1
StrClients = ” where (((tblclients.afid) = 1) ” ‘ Berlin
Case 2
StrClients = ” where (((tblclients.afid) = 2) ” ‘ Paris
etc
etc
End Select
End Function

Unfortunatelky after i have replaced, the record source is not working.
I have done the following :
bas = ” SELECT tblClients.CompanyName, tblClients.City, tblOffers.offerdate, tblOffers.EmployeeID,

tblClients.kindid, tblClients.ClientID, tblClients.afid ” & _
” FROM (tblClients INNER JOIN tblOffers ON tblClients.ClientID = tblOffers.Clientid) INNER JOIN

tblOfferDetails ON tblOffers.offerid = tblOfferDetails.offerid ” & _
” & strClients ” & _
” GROUP BY tblClients.CompanyName, tblClients.City, tblOffers.offerdate, tblOffers.EmployeeID,

tblClients.kindid, tblClients.ClientID, tblClients.afid;”
Me.RecordSource = bas

i receive the message Type mismatch in expression, Run Time error 3615.

Can somebody help me ?

Reply | Quote

Replies

Why are you enclosing the function concatenation in quotes? SQL can’t always resolve functions (especially when it can’t tell they *are* functions without the parens after them), so if you want to use them in code like this, it’s best to concatenate the result of the function like this:

” FROM (tblClients INNER JOIN tblOffers ON tblClients.ClientID = tblOffers.Clientid) INNER JOIN ” _
& “tblOfferDetails ON tblOffers.offerid = tblOfferDetails.offerid ” & strClients() & _
“) GROUP BY tblClients.CompanyName, tblClients.City, tblOffers.offerdate, tblOffers.EmployeeID, ” _
& “tblClients.kindid, tblClients.ClientID, tblClients.afid;”

Your function result won’t work anyhow because the parens are not matched (Note the closing paren in red) . Why don’t you just remove the parentheses from the WHERE expression generated by the function? You don’t actually need them in a simple condition like that and even in a complex condition you don’t need as many as the query designer sticks in there. Otherwise, add the closing paren to your function return value.

Reply | Quote

Is there a one for one between afid and Forms![FBenchmark]![Office], ie. can’t you just replace your WHERE clause with:

WHERE tblclients.afid = Forms![FBenchmark]![Office]

Pat

Reply | Quote