Runtime Error 3075 Debug stops on DoCmd.RunSQL SQL

[chuckduarte]

Unable to figure out why i'm getting a Run-time error 3075. The VBA Message box pops up with the Blue section listed and in the VBA editor the RED item is highlighted in yellow. The SQL is from an existing query that works fine. Currently i'm just using a command button (temporarily) to display the results after the SQL runs. But my end result is to add a Dcount or Count to only display the quantity of records in an unbound text box.

Any help will be greatly appreciated.

Private Sub Command270_Click()

DoSQL

End Sub
-----------------------------------------------------------------------------------
Private Sub DoSQL()

Dim SQL As String

SQL = "SELECT TCTOData.TCTODataID, ProjectData.RCN, TCTOData.TDes, " & _
"TCTOData.TCTO, SupplementData.Supplement, ProjectData.RMName, " & _
"SupplementData.Writer, SupplementData.IssueDate, TCTOData.Status, " & _
"SupplementData.SupplementStatus" & _
"FROM (ProjectData INNER JOIN TCTOData ON ProjectData.ProjectID, " & _
"= TCTOData.ProjectID) INNER JOIN SupplementData ON, " & _
"TCTOData.TCTODataID = SupplementData.TCTODataID;"

DoCmd.RunSQL SQL

End Sub

 

[Ranman256]

run it as a query. Access will show you the errors.
then use: docmd.openquery "qsMyQueryName"

building error free sql takes too much time.

 

[The_Doc_Man]

Error 3075 is "Missing Operator." This means that you probably have a punctuation error somewhere.

Looking closer, I noted that in the part in blue, you have what appears to be an extra comma following INNER JOIN SupplementData ON and that is what confused the issue, 'cause the next character is an "=" sign. So what the SQL parser saw right there was ... ON , = - which blew its little mind.

 

[cheekybuddha]

' ...
"SupplementData.SupplementStatus" & _
"FROM (ProjectData INNER JOIN TCTOData ON ProjectData.ProjectID, " & _
' ...

You need to add a space between the last field selected and the FROM keyword.

Add a Debug.Print SQL line before running the statement to inspect your query in the Immediate Window (Ctrl+G)

 

[chuckduarte]

Error 3075 is "Missing Operator." This means that you probably have a punctuation error somewhere.

Looking closer, I noted that in the part in blue, you have what appears to be an extra comma following INNER JOIN SupplementData ON and that is what confused the issue, 'cause the next character is an "=" sign. So what the SQL parser saw right there was ... ON , = - which blew its little mind.

I took out the comma after the ON and still does the same thing. Just as a side note I can't call the OpenQuery since eventually I just need the record count from the query. I need to repeat this about 60 times with variations to get proper status counts.

 

[chuckduarte]

run it as a query. Access will show you the errors.
then use: docmd.openquery "qsMyQueryName"

building error free sql takes too much time.

I would agree as I've done that many times but in this case I can't call the OpenQuery since eventually I just need the record count from the query. I need to repeat this about 60 times with variations to get proper status counts. I'm just stepping thru it to make sure everything is working properly. This is my first hiccup...

 

[Gasman]

If you debug.printed the sql, the error would be obvious?
I always put my spaces at the start of each line as well, so easier to spot such a problem.

 

[MajP]

I do not get it. If you just want the record count the easiest would be to store this query as a qdf.

me.SomeUnboundTextbox = dcount("*", "SomeStoredQuery")

 

[MajP]

And if the 60 variations are just where clauses to this query then you can simply build 60 queries or do it in code

me.SomeUnboundTextbox = dcount("*", "SomeStoredQuery","SomeField= SomeValue")

 

[chuckduarte]

And if the 60 variations are just where clauses to this query then you can simply build 60 queries or do it in code

me.SomeUnboundTextbox = dcount("*", "SomeStoredQuery","SomeField= SomeValue")

Yes I have done this in other areas but I prefer not to clutter up with a tremendous of queries. This is my first time using SQL in VBA to manage a query function. So the obvious isn't so obvious to me at this point. I'm just testing with a simple query which is the basis where the rest are derived from and can get more complex.

 

[The_Doc_Man]

Instead of 60 queries with DCounts, how about a single query with the SQL "COUNT" aggregate function and a "GROUP BY" - which you can then add to a detailed query using a JOIN clause on the thing by which you are grouping, so that these counts will be available automatically.

 

[chuckduarte]

Instead of 60 queries with DCounts, how about a single query with the SQL "COUNT" aggregate function and a "GROUP BY" - which you can then add to a detailed query using a JOIN clause on the thing by which you are grouping, so that these counts will be available automatically.

Thanks I like this approach and would be a lot simpler. I'm not sure how to put it or where for a single text box; I can do it with the Dcount as you can see in the pic but that requires a separate query with Dcount in the Control source (=DCount("*","TestRM")). So to get the count for the DCount I used the following in a secondary query:
SELECT WorkloadFinal.TCTODataID, WorkloadFinal.RMName, Count(WorkloadFinal.Status) AS CountOfStatus
FROM WorkloadFinal
GROUP BY WorkloadFinal.TCTODataID, WorkloadFinal.RMName
HAVING (((WorkloadFinal.RMName)=24));

WorkloadFinal is the end result from what I showed you initially
is this done as an Event for the text box or On Load for the Form for the Text Box?
In short I need a number counted for each task on left (status) for each name (RMName) at the top. This is 6 columns wide.

Really appreciate your guidance.

 

[The_Doc_Man]

If you make a query with the required aggregates and GROUP BY clauses, you can then make a second query that doesn't contain the aggregates and GROUP BY clauses but that IS part of a JOIN clause based on what drove the GROUP BY. I'll give you an extreme but fictitious example to show how that works:

QueryGenSums:

SELECT SUM( X ) AS SumX, COUNT( X ) AS CntX, AVG( X ) AS AvgX, Y FROM MyTable GROUP BY Y ;

QueryWithSums:

SELECT M.X, S.SumX, S.CntX, S.AvgX, M.Y, M.Z
FROM MyTable M INNER JOIN QueryGenSums S ON M.Y = S.Y 
ORDER BY Z ;

This is a "have your cake and eat it, too" solution, since you have the detailed i.e. individual records with X and Y but on each record, regardless of the order of X and Y, you can see the sum, count, and average values if you need them for anything. This is a contrived example, but it shows you how to make summations available in a detail record even if the final sort order has nothing to do with the X or Y values. Remember, this is a contrived solution to demonstrate a point. You can apply the concept any way you need, though.

If you want this data/sum combination appearing in a combo box, you make QueryWithSums as the .RowSource for the combo box. It has NOTHING to do with events except that it would be evaluated automatically as part of the Form_Load and Form_Current events. If you are doing this for a continuous sub-form, it is the .RecordSource of the sub-form. Don't think of it as something you need to do in an event, but rather put it where Access will do it for you automatically at an appropriate event.