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.