Runtime Error 3075 Debug stops on DoCmd.RunSQL SQL (1 Viewer)

chuckduarte

New member
Local time
Yesterday, 22:01
Joined
Feb 19, 2019
Messages
12
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

Well-known member
Local time
Today, 01:01
Joined
Apr 9, 2015
Messages
4,102
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

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
22,799
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

AWF VIP
Local time
Today, 06:01
Joined
Jul 21, 2014
Messages
1,012
Code:
' ...
"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

New member
Local time
Yesterday, 22:01
Joined
Feb 19, 2019
Messages
12
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

New member
Local time
Yesterday, 22:01
Joined
Feb 19, 2019
Messages
12
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

Enthusiastic Amateur
Local time
Today, 06:01
Joined
Sep 21, 2011
Messages
10,544
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

You've got your good things, and you've got mine.
Local time
Today, 01:01
Joined
May 21, 2018
Messages
6,369
I do not get it. If you just want the record count the easiest would be to store this query as a qdf.
Code:
me.SomeUnboundTextbox = dcount("*", "SomeStoredQuery")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:01
Joined
May 21, 2018
Messages
6,369
And if the 60 variations are just where clauses to this query then you can simply build 60 queries or do it in code
Code:
me.SomeUnboundTextbox = dcount("*", "SomeStoredQuery","SomeField= SomeValue")
 

chuckduarte

New member
Local time
Yesterday, 22:01
Joined
Feb 19, 2019
Messages
12
And if the 60 variations are just where clauses to this query then you can simply build 60 queries or do it in code
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

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
22,799
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

New member
Local time
Yesterday, 22:01
Joined
Feb 19, 2019
Messages
12
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.
 

Attachments

  • Screenshot 2022-05-16 123753.jpg
    Screenshot 2022-05-16 123753.jpg
    32.5 KB · Views: 50

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
22,799
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:

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

QueryWithSums:

Code:
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.
 

Users who are viewing this thread

Top Bottom