We help IT Professionals succeed at work.

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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

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.
CERTIFIED EXPERT
Top Expert 2010

Commented:
Try:

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

Commented:
@matthewspatrick...

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

Object doesn't support this property or method.

Thanks.
Jerry
CERTIFIED EXPERT
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:
    Me.ArchiveBirthsubform.Form.Requery

Open in new window

Does that make sense?
(°v°)

Author

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!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.