Wrong syntax - missing operator

[djchapple]

I have a table containing the following fields:

Automatic ID
RealDate
Morning
Afternoon

I use the name “RealDate” as I originally had other forms of storing the date – now dispensed with. It is just a date.

“Morning” and “Afternoon” contain 1 to three names which may be of the form “Joe Smith & Fred Blogs” or Jane & John Smith so I cannot search for “Jane Smith”. I must search separately to see if both “Jane” and “Smith” are in the string.

I am trying to find the number of sessions that each person has volunteered.

So I tried inserting the text below in the SQL page of the query to find out how many times |Jane Smith has volunteered in the last year. Unfortunately it returns the error "missing operator".

As I have only been using (trying to use) SQL for a couple of days my ignorance is overwhelming and I am at a complete loss.

Where am I going wrong?


SELECT [Table Rota].ID
, [Table Rota].RealDate
, [Table Rota].Morning
, [Table Rota].Afternoon
, IIf((Like “*[Morning]*” = ”Jane” AND Like “*[Morning]*” = ”Smith” ) , Expr1=Expr1+1, Expr1=Expr1 )
, IIf((Like “*[Afternoon]*” = ”Jane” AND Like “*[Afternoon]*” = ”Smith” ) , Expr1=Expr1+1, Expr1=Expr1)
FROM [Table Rota]
WHERE ((([Table Rota].RealDate)>#8/31/2010# Or ([Table Rota].RealDate)<#9/1/2011#));

As far as I can find out the IIf functions has the form

IIF(Condition, value if true, value if false)

Like “*[Afternoon]*” = ”Jane” is one condition
Like “*[Afternoon]*” = ”Smith” is another condition joined by an AND
Expr1=Expr1+1 is the value if true
Expr1=Expr1 is the value if false (probably redundant and not needed)

 

[plog]

First and foremost you need to properly structure your database. Each discrete piece of data needs to be in its own field--lumping a bunch of names into one field is wrong and you are starting to feel the pain of it.

Second here is the correct syntax for using like:

[Afternoon] Like "*Jane*"

Leave the field name alone, then put like, then use the wild card characters on the text itself.

Third, here's the correct syntax for your Iif statement:

Iif([Afternoon] Like "*Jane*", Expr1 + 1, Expr1)

You don't set anything equal to something else, you just declare what the result will be if true and what the result will be if false.

Fourth and to answer the next problem you are going to have:

WHERE ((([Table Rota].RealDate)>#8/31/2010# Or ([Table Rota].RealDate)<#9/1/2011#));

The 'Or' should an 'AND'. The way it is now you means you are essentially not using a condition on the RealDate field.

 

[djchapple]

Well that's certainly put me on the right track. My previous knowledge gleaned from computer languages, excel etc does not seem to have given me much insight into access.

I am searching around for a good cause to attend in the St neots region of the UK. After this I will redouble my efforts to find such a course.

 

[djchapple]

I amended the sql according to your suggestion to that shown in black below but Access added the extra bits shown in red. It has inroduced an AS and put [] around the quotes.

I am rather surprised as I have no idea what the AS does.

When I try to run it I get the error message "Circular reference caused by alias 'Expr1' in query definition's SELECT list"

SELECT [Table Rota].ID,
[Table Rota].RealDate,
[Table Rota].Morning, [Table Rota].Afternoon,
IIf([Morning] Like [“]*David*[”],Expr1+1,Expr1) AS Expr1,
IIf([Morning] Like [“]*Chapple*[”],Expr2+1,Exp2) AS Expr2,
IIf([Afternoon] Like [“]*David*[”],Expr3+1,Expr3) AS Expr3,
IIf([Afternoon] Like [“]*Chapple*[”],Expr4+1,Expr4) AS Expr4,
FROM [Table Rota]
WHERE ((([Table Rota].RealDate)>#8/31/2010# And ([Table Rota].RealDate)<#9/1/2011#));


I finally got it to work for one person using True and False for the IIf values and then manipulating the -1 and 0 values.

SELECT [Table Rota].ID,
[Table Rota].RealDate,
[Table Rota].Morning
[Table Rota].Afternoon,
IIf([Morning] Like "*David*",True) AS Expr1,
IIf([Morning] Like "*Chapple*",True) AS Expr2,
IIf([Afternoon] Like "*David*",True) AS Expr3,
IIf([Afternoon] Like "*Chapple*",True) AS Expr4,
[Expr1]+[Expr2] AS Expr5,
[Expr3]+[Expr4] AS Expr6,
IIf([Expr5]=-2,True) AS Expr7,
IIf([Expr6]=-2,True) AS Expr8
FROM [Table Rota]
WHERE ((([Table Rota].RealDate)>#8/31/2010# And ([Table Rota].RealDate)<#9/1/2011#));

Now I have two fields (0ne for morning and one for afternoon) holding -1 for sessions volunteered, otherwise blank.