Jerry Volz
asked on
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.Ar chiveBirth subform.Re cordSource = "SELECT ArchiveBirth.* FROM ArchiveBirth WHERE (((ArchiveBirth.Name) Like " * " & [Forms]![ArchiveEntryBirth ]![NameSrc h] & " * ") 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.
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.Ar
Any help would be greatly appreciated as I figure it's probably something silly that I'm missing.
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;"
ASKER
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.
Syntax error (missing operator) in query expression 'ArchiveBirth.Name Like'.
Syntax error (missing operator) in query expression '[Name] Like'.
Thanks again.
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;"
ASKER
@matthewspatrick...
I tried that and got the following run-time error:
Object doesn't support this property or method.
Thanks.
Jerry
I tried that and got the following run-time error:
Object doesn't support this property or method.
Thanks.
Jerry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ]![NameSrc h] & "*") 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!
SELECT ArchiveBirth.*, *
FROM ArchiveBirth
WHERE (((ArchiveBirth.Name) Is Null Or (ArchiveBirth.Name) Like "*" & [Forms]![ArchiveEntryBirth
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!
Open in new window