Access 2003 SQL Issue

Posted on 2012-08-10
Last Modified: 2012-08-13
I'm sure this is trivial for SQL gurus but I'm struggling with this SQL statement.  I'm using Access 2003 and I have the following:

A table, ArchiveBirth
A form, ArchiveEntryBirth
A subform, ArchiveBirthsubform
Three form fields on the main form, ArchiveEntryBirth called NameSrch, YrBegin, YrEnd

I have the main form opening with all the subform records showing by setting the subform recordsource as SELECT * FROM ArchiveBirth;

I'm trying to requery the ArchiveBirth table depending on what's in the NameSrch and a year range  depending on what's in YrBegin and YrEnd.  I have a filter command button on the main form where I'm trying to set the recordsource through VB but I can't seem to get the syntax correct.  Here's what I have:

This is for the Filter_Click sub:

Forms!ArchiveEntryBirth.ArchiveBirthsubform.RecordSource = "SELECT ArchiveBirth.* FROM ArchiveBirth WHERE (((ArchiveBirth.Name) Like " * " & [Forms]![ArchiveEntryBirth]![NameSrch] & " * ") AND ((ArchiveBirth.DateYear) Between [Forms]![ArchiveEntryBirth]![YrBegin] And [Forms]![ArchiveEntryBirth]![YrEnd])) ORDER BY ArchiveBirth.Name, ArchiveBirth.DateYear;"

Any help would be greatly appreciated as I figure it's probably something silly that I'm missing.
Question by:jvolz
    LVL 61

    Expert Comment

    Try this in the click event:

    Me.ArchiveBirthsubform.Form.RecordSource = "SELECT ArchiveBirth.* FROM ArchiveBirth WHERE ArchiveBirth.Name Like "'*" & me.NameSrch & "*' AND ArchiveBirth.DateYear Between " &  me.YrBegin & " And " & me.YrEnd & " ORDER BY ArchiveBirth.Name, ArchiveBirth.DateYear;"

    Open in new window

    LVL 61

    Expert Comment

    Or this:

    Me.ArchiveBirthsubform.Form.RecordSource = "SELECT * FROM ArchiveBirth WHERE [Name] Like "'*" & me.NameSrch & "*' AND DateYear Between " &  me.YrBegin & " And " & me.YrEnd & " ORDER BY [Name], DateYear;"

    Open in new window


    Author Comment

    I've tried both, but I'm getting similar errors with both expressions:

    Syntax error (missing operator) in query expression 'ArchiveBirth.Name Like'.

    Syntax error (missing operator) in query expression '[Name] Like'.

    Thanks again.
    LVL 92

    Expert Comment

    by:Patrick Matthews

    Forms!ArchiveEntryBirth.ArchiveBirthsubform.RecordSource = _
        "SELECT * " & _
        "FROM ArchiveBirth " & _
        "WHERE [Name] Like '*" & [Forms]![ArchiveEntryBirth]![NameSrch] & "*' AND " & _
        "(DateYear Between [Forms]![ArchiveEntryBirth]![YrBegin] And " & _
        "[Forms]![ArchiveEntryBirth]![YrEnd]) " & _
        "ORDER BY [Name], DateYear;"

    Open in new window


    Author Comment


    I tried that and got the following run-time error:

    Object doesn't support this property or method.

    LVL 58

    Accepted Solution

    I think that what you were missing is that you can't have a double quote inside a string delimited with double quotes. Either replace the internal double quotes with single quotes, or double the double quotes.

    Better yet, don't change the record source at all, and use directly a record source that handles the text boxes on the parent form:

    SELECT * FROM ArchiveBirth As A
    WHERE A.Name Like '*'+Parent.NameSrch+'*'
      AND A.DateYear Between Parent.YrBegin And Parent.YrEnd
    ORDER BY A.Name, A.DateYear;
    If you want to return all records when the parent form's controls are left blank, you can test use this instead:

    SELECT * FROM ArchiveBirth As A
    WHERE (Parent.NameSrch Is Null Or A.Name Like '*'+Parent.NameSrch+'*')
      AND (Parent.YrBegin Is Null Or Parent.YrBegin <= A.DateYear)
      AND (Parent.YrEnd Is Null Or Parent.YrEnd >= A.DateYear)
    ORDER BY A.Name, A.DateYear;
    Then, you only need to:

    Open in new window

    Does that make sense?

    Author Comment

    I ended up setting up the record source initially with the filter button doing a requery after possible search data being entered as suggested by harfang.  Here is what I had for the final record source:

    SELECT ArchiveBirth.*, *
    FROM ArchiveBirth
    WHERE (((ArchiveBirth.Name) Is Null Or (ArchiveBirth.Name) Like "*" & [Forms]![ArchiveEntryBirth]![NameSrch] & "*") AND ((ArchiveBirth.DateYear) Between [Forms]![ArchiveEntryBirth]![YrBegin] And [Forms]![ArchiveEntryBirth]![YrEnd]));

    Worked like a charm.  I'd offer partial points for the other ideas, but I couldn't seem to get by the run-time error on the other suggestions. Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now