Hi, I'm having to base an optional multi-parameter form on a query as the parameters come from two different tables and I'm getting problems with the construction of the SQL in the query. My co-workers and boss would want to search on any combination of 6 different fields but the results need to give them more details than the search is on.
I'm working in Access 2007 and I've downloaded and printed out for reference the Search Criteria from Allen Browne's site (allenbrowne.com/ser-62.html) but I keep hitting a problem with the SQL in the query. Initially I included the PARAMETERS statement at the start of it all and used the 3 sets of closing brackets followed by the semi-colon at thend of everything which I had input but when I tried to save it, I got an error message thrown back at me. So on closer look I thought maybe I don't need those parameters at the start from the way my data is structured as he has got less criteria in the parameters bit than on the search form, took those away from the statement and left the three closing brackets at the end but that still brought up the same error message, so I thought well there seems to be an unmatched bracket at the end of it all, so I took that out, tried saving again and - yes you've guessed it I got the same error message.
I've copied the SQL I've used below in the hopes that someone can tell me where I've gone wrong. I've named the fields I want to appear in the results and made sure that they are checked in the query design.
SELECT tblWorker.[FirstName] & " " & [LastName] AS WorkerName, tblWorker.HomePhone, tblWorker.MobilePhone, tblWorker.Town, tblWorker.County, tblWorker.WorkerNotes, tblWorker.Available, tblWorker.RegDate, tblWorker.CSCS, WorkerTrade.TradeNameID, WorkerTrade.[Trade Notes]
FROM tblWorker INNER JOIN WorkerTrade ON tblWorker.WorkerID=WorkerTrade.WorkerID
WHERE (([Forms]![Trade Search Form]![txtFilterTradeName] Is Null)
OR (WorkerTrade.TradeNameID = [Forms]![Trade Search Form]![txtFilterTradeName]))
AND (([Forms]![Trade Search Form]![txtFilterAvailable] Is Null)
OR (tblWorker.Available = [Forms]![Trade Search Form]![txtFilter Available]))
AND (([Forms]![Trade Search Form]![txtFilterCSCS] Is Null)
OR (tblWorker.CSCS = [Forms]![Trade Search Form]![txtFilterCSCS]))
AND (([Forms]![Trade Search Form]![txtFilterTown] Is Null)
OR (tblWorker.Town = [Forms]![Trade Search Form]![txtFilterTown]))
AND (([Forms]![Trade Search Form]![txtFilterCounty] Is Null)
OR (tblWorker.County = [Forms]![Trade Search Form]![txtFilterCounty]))
AND (([Forms]![Trade Search Form]![txtFromDate] Is Null)
OR (tblWorker.RegDate >= [Forms]![Trade Search Form]![txtFromDate]))
AND (([Forms]![Trade Search Form]![txtToDate] Is Null)
OR ((tblWorker.RegDate < DateAdd("d", 1, [Forms]![Trade Search Form]![txtToDate])):
The error message that's coming up starts with:
Syntax error (missing operator) in query expression. It then goes on to give the first three and a bit lines of the part after WHERE.
I'm working in Access 2007 and I've downloaded and printed out for reference the Search Criteria from Allen Browne's site (allenbrowne.com/ser-62.html) but I keep hitting a problem with the SQL in the query. Initially I included the PARAMETERS statement at the start of it all and used the 3 sets of closing brackets followed by the semi-colon at thend of everything which I had input but when I tried to save it, I got an error message thrown back at me. So on closer look I thought maybe I don't need those parameters at the start from the way my data is structured as he has got less criteria in the parameters bit than on the search form, took those away from the statement and left the three closing brackets at the end but that still brought up the same error message, so I thought well there seems to be an unmatched bracket at the end of it all, so I took that out, tried saving again and - yes you've guessed it I got the same error message.
I've copied the SQL I've used below in the hopes that someone can tell me where I've gone wrong. I've named the fields I want to appear in the results and made sure that they are checked in the query design.
SELECT tblWorker.[FirstName] & " " & [LastName] AS WorkerName, tblWorker.HomePhone, tblWorker.MobilePhone, tblWorker.Town, tblWorker.County, tblWorker.WorkerNotes, tblWorker.Available, tblWorker.RegDate, tblWorker.CSCS, WorkerTrade.TradeNameID, WorkerTrade.[Trade Notes]
FROM tblWorker INNER JOIN WorkerTrade ON tblWorker.WorkerID=WorkerTrade.WorkerID
WHERE (([Forms]![Trade Search Form]![txtFilterTradeName] Is Null)
OR (WorkerTrade.TradeNameID = [Forms]![Trade Search Form]![txtFilterTradeName]))
AND (([Forms]![Trade Search Form]![txtFilterAvailable] Is Null)
OR (tblWorker.Available = [Forms]![Trade Search Form]![txtFilter Available]))
AND (([Forms]![Trade Search Form]![txtFilterCSCS] Is Null)
OR (tblWorker.CSCS = [Forms]![Trade Search Form]![txtFilterCSCS]))
AND (([Forms]![Trade Search Form]![txtFilterTown] Is Null)
OR (tblWorker.Town = [Forms]![Trade Search Form]![txtFilterTown]))
AND (([Forms]![Trade Search Form]![txtFilterCounty] Is Null)
OR (tblWorker.County = [Forms]![Trade Search Form]![txtFilterCounty]))
AND (([Forms]![Trade Search Form]![txtFromDate] Is Null)
OR (tblWorker.RegDate >= [Forms]![Trade Search Form]![txtFromDate]))
AND (([Forms]![Trade Search Form]![txtToDate] Is Null)
OR ((tblWorker.RegDate < DateAdd("d", 1, [Forms]![Trade Search Form]![txtToDate])):
The error message that's coming up starts with:
Syntax error (missing operator) in query expression. It then goes on to give the first three and a bit lines of the part after WHERE.