Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Type mismatch in expression (Access 2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Type mismatch in expression (Access 2000)

    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() & _
    "<font color=red>)</font color=red> 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 <font color=red>red</font color=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.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Type mismatch in expression (Access 2000)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •