Search bar no longer filtering list box on form

[vector39]

Hi everyone, as some of you may know I've been working on a search criteria for a list box that filters 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.



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;

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!

[andy49]

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 & "*"))

You aren't actually putting a value where the red text is?


Something more like:

Between #"Forms!frmMain!txtFrom " & "# And #" & Forms!frmMain!txtTo & "#)"

so the code pulls in the values you need

Ive assumed the txtfrom and txtto are text fields showing a date.

[vector39]

Here's the change I made

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) Like "*" & forms!frmMain!SrchTxt & "*"))

There's a missing operator somewhere as I keep getting this error

And yes, txtFrom and txtTo are text fields showing the date

[andy49]

Must be & after " to link strings together

[andy49]

Vector I've just realised your in designer view in a query. Sorry mate. My efforts won't help.

[vector39]

No worries. I should have specified. I will keep playing with it in SQL view

[andy49]

Can you put a pic of the design view on here?

[ssanfu]

Missing ampersands and had 2 extra quotes.
See if this gets you closer:

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) Like "*" & forms!frmMain!SrchTxt & "*"))

[vector39]

Hi there, I tried the following code

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

But I keep getting the same syntax error (missing operator) error. And in particular this part of the code is highlighted

" OR ((tblCompany.ISExpiry) Between #"


​Any feedback is much appreciated!

[andy49]

what formats of dates are actually in the txtbox?

[vector39]

They're set as Short Date (6/19/1999)

[vector39]

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.

[vector39]

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!