3075 missing operator

[chizzy42]

hello, im looking to seek some help in trying to locate an error (syntax error 3075 missing operator) that comes up when I try to run a SQL string in VBA. the problem looks to be the variables I am using in the string as it runs ok if I hard code in a date. The code takes a week number from a form and converts it into the date chosen...here's the code

 Dim db As DAO.Database
 Dim qdf As DAO.QueryDef
 Dim strValue As String
 Set db = CurrentDb
 Set qdf = db.QueryDefs("qryPareto")
 Dim strSQL As String
 Dim dates As Date
 Dim weekstart As Integer
 Dim Msgstart As Date
 Dim weekend As Integer
 Dim Msgend As Date
 
 weekstart = Forms![frmweekno]![Text0]
 Msgstart = DateAdd("ww", weekstart, DateSerial(Year(Date), 1, -5))
 weekend = Forms![frmweekno]![Text7]
 Msgend = DateAdd("ww", weekend, DateSerial(Year(Date), 1, 1))
strSQL = "SELECT (Count(*)) AS [Fail Count], Left([Field4],25) AS [Fail Desc], JassnrF1.field3 AS Status " & _
"FROM JassnrF1 " & _
"WHERE (((JassnrF1.DateIn) BETWEEN #" & Msgstart & "# AND #" & Msgend & "#" & _
"GROUP BY Left([Field4],25), JassnrF1.field3 " & _
"HAVING (((JassnrF1.field3) Like 'fail')) " & _
"ORDER BY (Count(*)) DESC;"
qdf.SQL = strSQL
DoCmd.OpenReport "rptParetobutton", acViewPreview
Set qdf = Nothing
Set db = Nothing

Any help would be greatly appreciated

 

[Ranman256]

LIKE needs an asterisk...

....field3) Like 'fail*'

otherwise it is =

 

[chizzy42]

Hi Ranman I tried adding the asterix but im still getting the error..

"HAVING (((JassnrF1.field3) Like 'fail*')) " & _

 

[plog]

What's your SQL statement. Now, I don't care what goes into making it as you posted above. What is your actual SQL statement? Find out whats in strSQL.

 

[chizzy42]

heres a screenshot

 

[plog]

No, that's a screenshot of the error message box. What does strSQL contain after all is said and done?

 

[chizzy42]

strSQL = "SELECT (Count(*)) AS [Fail Count], Left([Field4],25) AS [Fail Desc], JassnrF1.field3 AS Status " & _
"FROM JassnrF1 " & _
"WHERE (((JassnrF1.DateIn) BETWEEN #" & Msgstart & "# AND #" & Msgend & "#" & _
"GROUP BY Left([Field4],25), JassnrF1.field3 " & _
"HAVING (((JassnrF1.field3) Like 'fail*')) " & _
"ORDER BY (Count(*)) DESC;"

 

[plog]

What's your SQL statement. Now, I don't care what goes into making it as you posted above. What is your actual SQL statement? Find out whats in strSQL.

I don't care about how the sausage is made. Show me what the actual sausage contains.

You do realize you are building an SQL string, correct? That SQL string is an actual string of text at the end of your code. What is that actual SQL string?

 

[chizzy42]

hi, you've lost me. I've shown you everything for the strSQL. This string creates a query called qryPareto which is used as part of the report info

 

[plog]

strSQL = "I am building a"
strSQL &= " string. Hopefully"
strSQL &= ", it will demonstrate what I want "
strSQL &= "from you. Although the string is being build via code "
strSQL &= " when all is said and done, it will contain a message."
strSQL &= " When I ask you what strSQL contains, I don't want the "
str_SQL & = "code that builds it. I want the actual string inside strSQL."

So, what does strSQL contain?

 

[JHB]

You're missing a space just before the Group.

strSQL = "SELECT (Count(*)) AS [Fail Count], Left([Field4],25) AS [Fail Desc], JassnrF1.field3 AS Status " & _
"FROM JassnrF1 " & _
"WHERE (((JassnrF1.DateIn) BETWEEN #" & Msgstart & "# AND #" & Msgend & "[B][COLOR=Red]# "[/COLOR][/B] & _
"GROUP BY Left([Field4],25), JassnrF1.field3 " & _
"HAVING (((JassnrF1.field3) Like 'fail*')) " & _
"ORDER BY (Count(*)) DESC;"

 

[Pat Hartman]

Just FYI -
1. Like should ONLY be used if you are actually providing partial strings for the search. If the only value you are looking for is "Fail" then you would NOT use Like at all. You would use =.
2. The HAVING clause is applied AFTER the data is aggregated. Therefore, it would only ever be used for aggregated data such as Sum(amt) > 2000. Move this .field3 condition to the WHERE clause for efficiency.
3. You should use better naming standards. Who would remember what Field4 and Field3 are next month let alone next year.

 

[Mark_]

OP

When someone asks what the SQL string is, they are asking that you put either debug.print OR a msgbox into your code just before you use said string to show what will be passed.

In your case, if you had put

msgbox "SQL is " & strSQL

just before

qdf.SQL = strSQL

you would have been able to see exactly what is being passed.

Alternative is to put a text box on your form and fill it with strSQL so you can cut/paste, but that does take a bit more effort.

 

[chizzy42]

Hi JHB I tried adding in the space but still getting a fail. I've attached a screenshot as suggested by mark which shows the SQL at runtime.

Thanks for the other tips, the fields will be renamed once the fields are agreed just now i'm just trying to get the code to work before adding it into the main program

 

[JHB]

Only for info, when you search for dates then it need to be in US format = mm/dd/yyyy, use the function Format.
I would suggest putting the SQL-string you show here into a new query then it is easier to find the problem. Also notice what Pat writes about using WHERE clause instead of the HAVING.

 

[static]

You have two too many brackets
WHERE (((JassnrF1.DateIn)

strSQL = "SELECT Count(*) AS [Fail Count], Left([Field4],25) AS [Fail Desc], field3 AS Status " & _
    "FROM JassnrF1 " & _
    "WHERE DateIn BETWEEN #" & Format(Msgstart, "dd-mmm-yyyy") & "# AND #" & Format(Msgend, "dd-mmm-yyyy") & "# " & _
    "GROUP BY Left([Field4],25), field3 " & _
    "HAVING field3 Like 'fail' " & _
    "ORDER BY Count(*) DESC;"

 

[chizzy42]

Well spotted static , running fine now..thanks

 

[Pat Hartman]

Now you are using LIKE without any wild cards:

HAVING field3 Like 'fail'


This should be in the WHERE clause AND it should be " field3 ='fair' "

 

[chizzy42]

Hi pat thanks for the tips on tidying the code up , is this the difference you mean?

SELECT (Count(*)) AS [Fail Count], Left([Field12],25) AS [Fail Desc], JasJnrF2.Field8 
FROM JasJnrF2 
WHERE (((JasJnrF2.DateIn) Between (Forms![frmweekno]![Text14]-1+#12/30/1899 8:0:0#) And (Forms![frmweekno]![Text16]+#12/30/1899 8:0:0#))) AND (((JasJnrF2.Field8) = 'fail')) 
GROUP BY Left([Field12],25), JasJnrF2.Field8 
ORDER BY (Count(*)) DESC;

 

[Pat Hartman]

Yes. Thank you for humoring me. Most people work with such small recordsets that using LIKE incorrectly or using a HAVING when they should be using a WHERE won't increase the time sufficiently to cause a problem with slowness. However, if you learn good techniques and use them, you ultimately run into fewer problems.

Using the QBE to build a query is what causes the HAVING issue. You have to specifically change to make the criteria a WHERE and that isn't always obvious. As to LIKE - there is a lot of bad advice out there on the internet. LIKE in most cases causes a full table scan so if you are always using a complete value (i.e. no wildcard), why would you ever want to prevent the query engine from using a more efficient technique? Also LIKE only works correctly on strings. That means you cannot use it with dates or numbers. The errors are subtle and you may not notice for a very long time that the correct records are not actually being selected.