Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: Build the WHERE clause dynamically - problem

    I've been trying to follow some advice given by Allen Browne on another forum, to use when I want the form to search only the boxes that have terms entered in them. I have changed it as best I know how to respond to my database but I'm obviously getting something wrong! All my fields in Library_Table are text fields.

    The error I'm getting is Runtime error 3075
    Syntax error (missing operator) in query expression '([Title] = "& me.Title &" ORDER BY [Publish Date];'.

    The line that highlights in the debugger is Me.RecordSource = strSql


    Private Sub cmdSearch_Click()
    Dim strSql As String
    Dim IngLen As Long
    Const strcStub = "SELECT * FROM [Library_Table] "
    Const strcTail = "ORDER BY [Publish Date];"
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.Keyword) Then
    strSql = strSql & "([Keyword] = """ & Me.Keyword & """) AND"
    End If
    If Not IsNull(Me.Title) Then
    strSql = strSql & "([Title] = """ & Me.Title & """) AND"
    End If
    If Not IsNull(Me.Author) Then
    strSql = strSql & "([Author] = """ & Me.Author & """) AND"
    End If
    If Not IsNull(Me.Publisher) Then
    strSql = strSql & "([Publisher] = """ & Me.Publisher & """) AND"
    End If
    If Not IsNull(Me.Subject) Then
    strSql = strSql & "([Subject] = """ & Me.Subject & """) AND"
    End If
    If Not IsNull(Me.Geographical) Then
    strSql = strSql & "([Geographical] = """ & Me.Geographical & """) AND"
    End If
    If Not IsNull(Me.DatePub) Then
    strSql = strSql & "([DatePub] = """ & Me.DatePub & """) AND"
    End If
    IngLen = Len(strSql) - 5 'Without trailing " AND "
    If IngLen > 0 Then
    strSql = strcStub & "WHERE" & Left$(strSql, IngLen) & strcTail
    Else
    strSql = strcStub & strcTail
    End If
    'Assign the query string.
    Me.RecordSource = strSql

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are not adding ' AND ', you are adding ' AND'
    so
    IngLen = Len(strSql) - 5 'Without trailing " AND "
    is stealing a space too many.

    BTW, there is a nice trick from rudy to save the hassle along the following lines:

    strSQL = "SELECT blah FROM here WHERE (1=1) "
    if whatever then
    strSQL = strSQL & "AND this = that "
    endif
    etc...
    ...with no extra 4/5 chars to strip.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2007
    Posts
    72
    Thanks for that izyrider. Just goes to show I'm not such a good proof-reader!
    My code now has no errors.

    I've got another problem now. When I was using the QBF technique the cmdSearch button ran the macro to run the Query and show the results in a table. I then adjusted it to show on a form (with the record source of the form being the query)

    Now that I've changed to this method I don't know where to put information telling Access where to put the results!!

    I know it's got to be extremely logical but my brain is a bit stuck at the moment.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bronisaurus
    Thanks for that izyrider. Just goes to show I'm not such a good proof-reader!
    My code now has no errors.

    I've got another problem now. When I was using the QBF technique the cmdSearch button ran the macro to run the Query and show the results in a table. I then adjusted it to show on a form (with the record source of the form being the query)

    Now that I've changed to this method I don't know where to put information telling Access where to put the results!!

    I know it's got to be extremely logical but my brain is a bit stuck at the moment.

    ?
    me.recordsource=.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2007
    Posts
    72
    Okay where should this me.RecordSource go?
    I've got it behind the cmdSearch button but that is = strSql so as I understand it it's calculating everything above that I am saying = strSql, but it doesn't display anywhere.
    I have a subform for results but I'm obviously getting the record source or linking for that wrong as it isn't working.

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    This SQL string that you have developed in VBA code is the RecordSource for, it sounds like, the subform you spoke about in your last post. Wherever you had the query named before, that is, which form you had that query named as the RecordSource, you now need to set that same form to this SQL string as that form's RecordSource. healdem assumed you were creating the SQL string within the form that would need it for it's RecordSource, therefore the code Me.RecordSource = strSQL
    I then adjusted it to show on a form (with the record source of the form being the query)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Const strcStub = "SELECT * FROM [Library_Table] "
    Please remove the asterix (*) and replace it with the column names
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2007
    Posts
    72
    Quote Originally Posted by georgev
    Code:
    Const strcStub = "SELECT * FROM [Library_Table] "
    Please remove the asterix (*) and replace it with the column names
    Well, thanks to everyone's help my search form works EXCEPT for One thing.

    I put my column names in like so:

    "SELECT [Title], [Author], [Subject], [Publish Date] FROM [Library_Table] "

    but my search form only works when I type a word into the Title text box.
    If I type into any other text box I get no results. Not an error, just no records.

    Have I got the syntax wrong?

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can we see the revised code?

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Jul 2007
    Posts
    72
    The results show on my search form that has the text boxes and Search button in the header and the Library_Table fields i.e. [Title], [Author] etc. displayed in the Detail as a continuous form.

    The results for any searches on Title work beautifully, but nothing else works. By the way if I take out the column names and use the * I get the exact same result (i.e. Title search works but nothing else).



    Private Sub cmdSearchbutton_Click()

    Dim strSql As String
    Dim IngLen As Long
    Const strcStub = "SELECT [Title], [Author], [Subject], [Publish Date] FROM [Library_Table] "
    Const strcTail = " ORDER BY [Title];"
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.txtTitle1) Then
    strSql = strSql & "([Title] Like ""*" & Me.txtTitle1 & "*"") AND "
    End If
    If Not IsNull(Me.TxtAuthor1) Then
    strSql = strSql & "([Title] = ""*" & Me.TxtAuthor1 & "*"") AND "
    End If
    If Not IsNull(Me.TxtSubject1) Then
    strSql = strSql & "([Subject] = ""*" & Me.TxtSubject1 & "*"") AND "
    End If
    If Not IsNull(Me.TxtPublishDate1) Then
    strSql = strSql & "([Publish Date] = ""*" & Me.TxtPublishDate1 & "*"") AND "
    End If
    IngLen = Len(strSql) - 5 'Without trailing " AND "
    If IngLen > 0 Then
    strSql = strcStub & " WHERE " & Left$(strSql, IngLen) & strcTail
    Else
    strSql = strcStub & strcTail

    End If

    'Assign the query string.
    Me.RecordSource = strSql

    End Sub

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    strictly speaking i was right with the first reply, but now i see that it was the wrong direction. sorry!

    you do have space errors, but the other way round.

    consider a middle lump of your code:
    If Not IsNull(Me.Publisher) Then
    strSql = strSql & "([Publisher] = """ & Me.Publisher & """) AND"
    End If
    If Not IsNull(Me.Subject) Then
    strSql = strSql & "([Subject] = """ & Me.Subject & """) AND"
    End If

    if both are not null, you will end up with SQL like this
    blahblah[Publisher] = 'elsevier' AND[Subject] = 'metabolomics' ANDblahblah

    ...so you have too few spaces (and when this is fixed, make sure you trim off the correct number of unwanted chars (unless you followed the oh-so-elegant rudy trick that i mentioned above to avoid the trim)). SQL will not forgive missing spaces but will tolerate extra spaces.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    crossed posts - looking at yours now
    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    add a line after
    strSql = strcStub & strcTail
    reading
    debug.print strSQL
    place several criteria in your form
    and Ctrl-G to get show immediate window
    and post the .printed SQL

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    OK, here's my version of your stuff:

    Dim strSql As String
    Dim IngLen As Long
    Const strcTail = " ORDER BY [Title];"

    strSQL = "SELECT [Title], [Author], [Subject], [Publish Date] FROM [Library_Table] WHERE (1=1)"
    If Not IsNull(Me.txtTitle1) Then
    strSql = strSql & " And ([Title] Like '*" & Me.txtTitle1 & "*')"
    End If
    If Not IsNull(Me.TxtAuthor1) Then
    strSql = strSql & " And ([Title] = '" & Me.TxtAuthor1 & "')"
    End If
    If Not IsNull(Me.TxtSubject1) Then
    strSql = strSql & " And ([Subject] = '" & Me.TxtSubject1 & "')"
    End If
    If Not IsNull(Me.TxtPublishDate1) Then
    strSql = strSql & " And ([Publish Date] = " & Me.TxtPublishDate1 & ")"
    End If

    strSql = strcStub & strcTail

    End If


    NOTES:
    date does not want to be '*# somedate #*'
    ...and i'm also a bit confused about where the the date data is supposed to come from
    = does not want the * wildcards

    izy

    LATER: (DISASTER!) that last line should read
    strSql = strSQL & strcTail
    Last edited by izyrider; 10-02-07 at 13:52.
    currently using SS 2008R2

  15. #15
    Join Date
    Jul 2007
    Posts
    72
    Option Compare Database

    Private Sub Command19_Click()
    On Error GoTo Err_Command19_Click



    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

    Exit_Command19_Click:
    Exit Sub

    Err_Command19_Click:
    MsgBox Err.Description
    Resume Exit_Command19_Click

    End Sub

    Private Sub cmdSearchbutton_Click()

    Dim strSql As String
    Dim IngLen As Long
    Const strcStub = "SELECT [Title], [Author], [Subject], [Publish Date] FROM [Library_Table] "
    Const strcTail = " ORDER BY [Title];"
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.txtTitle1) Then
    strSql = strSql & "([Title] Like ""*" & Me.txtTitle1 & "*"") AND "
    End If
    If Not IsNull(Me.TxtAuthor1) Then
    strSql = strSql & "([Author] = ""*" & Me.TxtAuthor1 & "*"") AND "
    End If
    If Not IsNull(Me.TxtSubject1) Then
    strSql = strSql & "([Subject] = ""*" & Me.TxtSubject1 & "*"") AND "
    End If
    If Not IsNull(Me.TxtPublishDate1) Then
    strSql = strSql & "([Publish Date] = ""*" & Me.TxtPublishDate1 & "*"") AND "
    End If
    IngLen = Len(strSql) - 5 'Without trailing " AND "
    If IngLen > 0 Then
    strSql = strcStub & " WHERE " & Left$(strSql, IngLen) & strcTail
    Else
    strSql = strcStub & strcTail
    Debug.Print strSql
    End If

    'Assign the query string.
    Me.RecordSource = strSql

    End Sub
    Private Sub cmdSeemore_Click()
    On Error GoTo Err_cmdSeemore_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Search_Results_Form"

    stLinkCriteria = "[Title]=" & "'" & Me![Title] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdSeemore_Click:
    Exit Sub

    Err_cmdSeemore_Click:
    MsgBox Err.Description
    Resume Exit_cmdSeemore_Click

    End Sub

Posting Permissions

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