SQL problems in query (1 Viewer)

K

kimberlin

Registered User.
Local time
Today, 06:19
Joined
Jul 22, 2011
Messages
64
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.
 
T

Taruz

Registered User.
Local time
Today, 06:19
Joined
Apr 10, 2009
Messages
168
Hi..

As an alternative; you can change the structure of the criteria in this way.:

Code: 
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 
              WorkerTrade.TradeNameID = 
              iif([Forms]![Trade Search Form]![txtFilterTradeName] Is Null ,[TradeNameID],[Forms]![Trade Search Form]![txtFilterTradeName] )
AND 
              tblWorker.Available = 
              iif([Forms]![Trade Search Form]![txtFilterAvailable] Is Null ,[Available],[Forms]![Trade Search Form]![txtFilterAvailable] )
AND 
              tblWorker.CSCS = 
              iif([Forms]![Trade Search Form]![txtFilterCSCS] Is Null ,[CSCS],[Forms]![Trade Search Form]![txtFilterCSCS] )
AND
              tblWorker.Town = 
              iif([Forms]![Trade Search Form]![txtFilterTown] Is Null ,[Town],[Forms]![Trade Search Form]![txtFilterTown] )
AND
              tblWorker.County = 
              iif([Forms]![Trade Search Form]![txtFilterCounty] Is Null ,[County],[Forms]![Trade Search Form]![txtFilterCounty] )
AND
              tblWorker.RegDate = 
              iif([Forms]![Trade Search Form]![txtFromDate] Is Null ,[RegDate],[Forms]![Trade Search Form]![txtFromDate] )
AND
              tblWorker.RegDate = 
              iif([Forms]![Trade Search Form]![txtToDate] Is Null ,[RegDate],<[Forms]![Trade Search Form]![txtToDate]+1 )
 
Last edited:
K

kimberlin

Registered User.
Local time
Today, 06:19
Joined
Jul 22, 2011
Messages
64
jdraw said:
You may find the free video tutorial on a search form at
http://www.datapigtechnologies.com/flashfiles/searchform.html
helpful.
This doesn't deal directly with your SQL issue, but shows an search approach which seems too fit your general criteria.
Good luck.
Click to expand...

Thank you for that link, I've just watched the video on that page and it does seem to do the sort of search that I am after. I didn't realise about the indexing either so I'm going to go into my tables and do that in a few minutes. The "like" phrase does worry me a bit though as I don't think the boss will be too impressed if he types in plumber for the trade he wants and gets greeted with all the plumber's mates as well!
 
K

kimberlin

Registered User.
Local time
Today, 06:19
Joined
Jul 22, 2011
Messages
64
Taruz said:
Hi..

As an alternative; you can change the structure of the criteria in this way.:

Code: 
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 
              WorkerTrade.TradeNameID = 
              iif([Forms]![Trade Search Form]![txtFilterTradeName] Is Null ,[TradeNameID],[Forms]![Trade Search Form]![txtFilterTradeName] )
AND 
              tblWorker.Available = 
              iif([Forms]![Trade Search Form]![txtFilterAvailable] Is Null ,[Available],[Forms]![Trade Search Form]![txtFilterAvailable] )
AND 
              tblWorker.CSCS = 
              iif([Forms]![Trade Search Form]![txtFilterCSCS] Is Null ,[CSCS],[Forms]![Trade Search Form]![txtFilterCSCS] )
AND
              tblWorker.Town = 
              iif([Forms]![Trade Search Form]![txtFilterTown] Is Null ,[Town],[Forms]![Trade Search Form]![txtFilterTown] )
AND
              tblWorker.County = 
              iif([Forms]![Trade Search Form]![txtFilterCounty] Is Null ,[County],[Forms]![Trade Search Form]![txtFilterCounty] )
AND
              tblWorker.RegDate = 
              iif([Forms]![Trade Search Form]![txtFromDate] Is Null ,[RegDate],[Forms]![Trade Search Form]![txtFromDate] )
AND
              tblWorker.RegDate = 
              iif([Forms]![Trade Search Form]![txtToDate] Is Null ,[RegDate],<[Forms]![Trade Search Form]![txtToDate]+1 )
Click to expand...

I didn't realise that I could do it as a lot of IIF statements but I must admit I am confused by what you are asking the results to show as it looks as though you are saying if there's nothing in that field for this criteria then just give the field name but if there is something there then give me what I'm looking for, or have I got things muddled?
 
K

kimberlin

Registered User.
Local time
Today, 06:19
Joined
Jul 22, 2011
Messages
64
Re: SQL problems in query - update

I've tried deleting the coding that I had in the SQL view of that query and doing a copy paste of the SQL which Taruz used but now I'm getting another error message coming up which is in the attached word document, so I'm still no further forward.
 

Attachments

  • update on SQL.doc
    37 KB · Views: 95
T

Taruz

Registered User.
Local time
Today, 06:19
Joined
Apr 10, 2009
Messages
168
Please try this way..:


Code: 
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 
              WorkerTrade.TradeNameID = 
              iif([Forms]![Trade Search Form]![txtFilterTradeName] Is Null ,[TradeNameID],[Forms]![Trade Search Form]![txtFilterTradeName] )
AND 
              tblWorker.Available = 
              iif([Forms]![Trade Search Form]![txtFilterAvailable] Is Null ,[Available],[Forms]![Trade Search Form]![txtFilterAvailable] )
AND 
              tblWorker.CSCS = 
              iif([Forms]![Trade Search Form]![txtFilterCSCS] Is Null ,[CSCS],[Forms]![Trade Search Form]![txtFilterCSCS] )
AND
              tblWorker.Town = 
              iif([Forms]![Trade Search Form]![txtFilterTown] Is Null ,[Town],[Forms]![Trade Search Form]![txtFilterTown] )
AND
              tblWorker.County = 
              iif([Forms]![Trade Search Form]![txtFilterCounty] Is Null ,[County],[Forms]![Trade Search Form]![txtFilterCounty] )
AND
              tblWorker.RegDate between 
              iif([Forms]![Trade Search Form]![txtFromDate] Is Null ,[RegDate],[Forms]![Trade Search Form]![txtFromDate] )
AND
              iif([Forms]![Trade Search Form]![txtToDate] Is Null ,[RegDate],[Forms]![Trade Search Form]![txtToDate] )
 
K

kimberlin

Registered User.
Local time
Today, 06:19
Joined
Jul 22, 2011
Messages
64
Thank you Taruz, that set of coding has saved fine without any error messages coming up. Hopefully I'll now be able to get the rest of this database finished before I go back to work tomorrow.
 
K

kimberlin

Registered User.
Local time
Today, 06:19
Joined
Jul 22, 2011
Messages
64
Hit a slight problem now as it seems to think that I have to answer questions about all those criteria before I can open the form which is supposed to control it all!
 
You must log in or register to reply here.

Similar threads

A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
384
ebs17
E
S
Solved Query in form not populating field
2
Replies
20
Views
1,122
Sh8dyDan
S
A
VBA Code For Windows Popup Message
Replies
1
Views
483
Ranman256
Ranman256
AlexN
Solved Problem in designing the right query(ies)
2
Replies
30
Views
1,798
AlexN
AlexN
G
Solved Why are my SQL Queries so slow?
Replies
9
Views
882
Minty
Minty

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom