Access 2003 SQL Issue

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.
Who is Participating?
harfangConnect With a Mentor Commented:
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?
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

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

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

jvolzAuthor Commented:
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.
Patrick MatthewsCommented:

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

jvolzAuthor Commented:

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

Object doesn't support this property or method.

jvolzAuthor Commented:
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!
All Courses

From novice to tech pro — start learning today.