Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    37

    Unanswered: Message> in query expression <expression>. (Error 3075)

    I've created a Report Generator in Access VBA that uses a Filter Command.
    When I use data that includes a single quot I get an error 3075.
    Otherwise data with no single quot works fine.
    How can I get over this problem?

    Here is the code:


    ' Build criteria string from lstAuthor listbox

    For Each varItem In Me.lstAuthor.ItemsSelected
    strAuthor = strAuthor & ",'" & Me.lstAuthor.ItemData(varItem) _
    & "'"
    Next varItem

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    you're basically building a string like:
    Code:
    author1,'author2,'author3...
    Which is jumble as far as SQL is concerned. It's a good idea to do a
    Code:
    msgbox strAuthor
    In your code every so often when building strings dynamically to make sure you get what you want. What are you trying to build?
    Me.Geek = True

  3. #3
    Join Date
    May 2007
    Posts
    37

    Here is the whole SubRoutine

    Private Sub cmdApplyFilter_Click()
    Dim varItem As Variant
    Dim strAuthor As String
    Dim strDonor As String
    Dim strGender As String
    Dim strFilter As String
    Dim strSortOrder As String

    For Each varItem In Me.lstAuthor.ItemsSelected
    strAuthor = strAuthor & ",'" & Me.lstAuthor.ItemData(varItem) _
    & "'"
    Next varItem

    If Len(strAuthor) = 0 Then
    strAuthor = "Like '*'"
    Else
    strAuthor = Right(strAuthor, Len(strAuthor) - 1)
    strAuthor = "IN(" & strAuthor & ")"
    End If


    ' Build filter string
    strFilter = "[AuthorName] " & strAuthor

    ' Build sort string
    If Me.cboSortOrder1.Value <> "Not Sorted" Then
    strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
    If Me.cmdSortDirection1.Caption = "Descending" Then
    strSortOrder = strSortOrder & " DESC"
    End If
    If Me.cboSortOrder2.Value <> "Not Sorted" Then
    strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
    If Me.cmdSortDirection2.Caption = "Descending" Then
    strSortOrder = strSortOrder & " DESC"
    End If
    If Me.cboSortOrder3.Value <> "Not Sorted" Then
    strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value & "]"
    If Me.cmdSortDirection3.Caption = "Descending" Then
    strSortOrder = strSortOrder & " DESC"
    End If
    End If
    End If
    End If

    DoCmd.OpenReport "Books", acPreview


    ' Apply filter and sort to report
    With Reports![Books]
    .Filter = strFilter
    .FilterOn = True
    .OrderBy = strSortOrder
    .OrderByOn = True
    End With
    End Sub

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I'm leaving for vacation in less than an hour, but if nobody's posted by Monday to help you out, I'll see if I can help then.
    Me.Geek = True

  5. #5
    Join Date
    May 2007
    Posts
    37
    Thank's, I've got a solution already.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Public forums = share the solution!
    Enjoy your hols Nick!
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    strSQL = "Select * from dbo_WECCCustomers where LastName = """ & Forms!MyForm!LastName & """"

    is an example of a sql select statement which accounts for names like O'Brien (ie. with an apostrophe) without returning an error for utilizing the strSQL statement in code.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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