Search bar no longer filtering list box on form (1 Viewer)

[vent]

Hi everyone, as some of you may know I've been working on a search criteria that filters a listbox based on what the user types into the search bar or using a date range criteria. While I finally got the date range to work (thanks to you guys ) now the search bar won't filter the list box as the user begins typing as it previously was. There is a button where users can add in new info (e.g. a new company, their address, insurance info, etc) and even though it updates on the company table, nothing shows up on the list box. Do list boxes have a limit of rows they display? Here's the following SQL row source for the list box and I suspect the problem is here, particularly in the WHERE clause.

[B]SELECT[/B] tblCompany.CompanyName, tblAssociation.Associate, tblCompany.AAEndDate, tblCompany.ISExpiry, tblCompany.ISDeclaration, tblCompany.Address, tblCompany.City, tblCompany.Province, tblCompany.PostalCode, tblCompany.ContactName, tblCompany.ContactEmail, tblCompany.ContactPhoneNumber, tblPrograms.Program
[B]FROM [/B]tblPrograms INNER JOIN (tblAssociation INNER JOIN tblCompany ON tblAssociation.ID = tblCompany.[Associate(s)].Value) ON tblPrograms.ID = tblCompany.Programs.Value
[B]WHERE [/B](((tblCompany.AAEndDate) Between Forms!frmMain!txtFrom And Forms!frmMain!txtTo) OR ((tblCompany.ISExpiry) Between forms!frmMain!txtFrom And forms!frmMain!txtTo) And ((tblCompany.CompanyName & tblAssociation.Associate & tblCompany.AAEndDate & tblCompany.ISExpiry) Like "*" & forms!frmMain!SrchTxt & "*"))
[B]ORDER BY [/B]tblCompany.CompanyName;

AAEndDate and ISExpiry are the two date columns in the list box. If you guys wouldn't mind having a look at this and maybe highlight any potential errors, that would be much appreciated!

 

[isladogs]

Several problems with the WHERE clause:

SELECT tblCompany.CompanyName, tblAssociation.Associate, tblCompany.AAEndDate, tblCompany.ISExpiry, tblCompany.ISDeclaration, tblCompany.Address, tblCompany.City, tblCompany.Province, tblCompany.PostalCode, tblCompany.ContactName, tblCompany.ContactEmail, tblCompany.ContactPhoneNumber, tblPrograms.Program
FROM tblPrograms INNER JOIN (tblAssociation INNER JOIN tblCompany ON tblAssociation.ID = tblCompany.[Associate(s)].Value) ON tblPrograms.ID = tblCompany.Programs.Value
WHERE (((tblCompany.AAEndDate) Between Forms!frmMain!txtFrom And Forms!frmMain!txtTo) OR ((tblCompany.ISExpiry) Between forms!frmMain!txtFrom And forms!frmMain!txtTo) And ((tblCompany.CompanyName & tblAssociation.Associate & tblCompany.AAEndDate & tblCompany.ISExpiry) Like "*" & forms!frmMain!SrchTxt & "*"))
ORDER BY tblCompany.CompanyName;

a) Dates need to be wrapped in # delimiters
b) The second part is WRONG - you can't join field together like this
And ((tblCompany.CompanyName & tblAssociation.Associate & tblCompany.AAEndDate & tblCompany.ISExpiry)

The date part should read:

WHERE (((tblCompany.AAEndDate) Between #" & Forms!frmMain!txtFrom & "# And #" & Forms!frmMain!txtTo & "#) OR ((tblCompany.ISExpiry) Between #" & forms!frmMain!txtFrom & "# And #" & forms!frmMain!txtTo & "#)

You may also need to reformat for local date format
e.g. UK dd/mm/yyyy would require EACH item to be written like

#" & Format(Forms!frmMain!txtFrom,"dd/mm/yyyy") & "#

EASY!!??!!

You'll need to fix this part yourself as I'm not clear what you are trying to do:

 And ((tblCompany.CompanyName & tblAssociation.Associate & tblCompany.AAEndDate & tblCompany.ISExpiry) Like "*" & forms!frmMain!SrchTxt & "*"))

Also you seem to have a field called 'Associate' which is fine & another with the name 'Associate(s)' - don't use () in field names
- similarly avoid spaces, apostrophes etc

 

[vent]

Hi thank you for the reply. I'm working on making those changes you've suggested. The second part of the code

And ((tblCompany.CompanyName & tblAssociation.Associate & tblCompany.AAEndDate & tblCompany.ISExpiry) Like "*" & forms!frmMain!SrchTxt & "*"))

is for when the user starts typing the list box filters as you type but for some reason it included the two date fields, which isn't necessary since this really just needs to filter by Company or Association names (e.g. user types in sun and the companies Sunnyview or SunnySide filter in the list box). I've deleted these date fields and will get back to you soon.

 

[vent]

Hi guys here's a pic of the SQL in design view. Not sure if this helps but I figured I'd include it. As it currently stands, there is no more syntax error and the date range text boxes filter between dates as intended however, the search bar no longer filters as the user types.

 

[vent]

Never mind guys I got it (with the help of Microsoft Community) working by adding a confirm button which the on click event handler to Me.lstSearchResults.Requery and declaring parameters for the date/time to prevent values being confused as arithmetic vs dates entered. Thank so much!