Pull Value from Query and Sum

A

anchorp

Jeremy
Local time
Yesterday, 23:34
Joined
Jan 8, 2007
Messages
24
I have a Text Box in a report in which I want to run a module that runs a query. Please let me know if I am on the right track?

Here is the VB I have so far:

Code: 
Option Compare Database

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset()
Dim crit As String
[INDENT]crit = "SELECT tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours]" & _
FROM (tblEstList INNER JOIN ((tblPhaseItem INNER JOIN qryTruckingDtl ON tblPhaseItem.PhaseID = qryTruckingDtl.PhaseID) INNER JOIN LtblTrucks ON qryTruckingDtl.TruckID = LtblTrucks.TruckID) ON tblEstList.EstID = tblPhaseItem.EstID) LEFT JOIN tblPhaseDtl ON tblPhaseItem.PhaseID = tblPhaseDtl.PhaseID" & _
GROUP BY tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours]" & _
HAVING (((tblEstList.EstNum) Like Nz([Forms]![frmEfficiencyReportInput]![EstNum],"*")) AND ((tblEstList.EstName) Like Nz([Forms]![frmEfficiencyReportInput]![EstName],"*")) AND ((qryTruckingDtl.Date) Between Nz([Forms]![frmEfficiencyReportInput]![StartDate],#1/1/1900#) And Nz([Forms]![frmEfficiencyReportInput]![EndDate],#12/31/9999#)) AND ((qryTruckingDtl.DriverID) Like Nz([Forms]![frmEfficiencyReportInput]![Driver],"*")) AND ((qryTruckingDtl.TruckID) Like Nz([Forms]![frmEfficiencyReportInput]![Truck],"*")));"[/INDENT]

rst.Close
db.Close
Exit Sub

When I attempt to run the code I get "Compile Error: Invalid outside procedure"

Once it runs the query I want it to sum the Total Hours field and put that value in the report.

Any help appreciated!!!
 
I tried simplifying my query to see if that would help. Now I just have:

Code: 
crit = "SELECT tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours]" & _

FROM tblEstList INNER JOIN (tblPhaseItem INNER JOIN qryTruckingDtl ON tblPhaseItem.PhaseID = qryTruckingDtl.PhaseID) ON tblEstList.EstID = tblPhaseItem.EstID" & _

WHERE (((tblEstList.EstNum) Like Nz([Forms]![frmEfficiencyReportInput]![EstNum],"*")) AND ((tblEstList.EstName) Like Nz([Forms]![frmEfficiencyReportInput]![EstName],"*")) AND ((qryTruckingDtl.Date) Between Nz([Forms]![frmEfficiencyReportInput]![StartDate],#1/1/1900#) And Nz([Forms]![frmEfficiencyReportInput]![EndDate],#12/31/9999#)) AND ((qryTruckingDtl.DriverID) Like Nz([Forms]![frmEfficiencyReportInput]![Driver],"*")) AND ((qryTruckingDtl.TruckID) Like Nz([Forms]![frmEfficiencyReportInput]![Truck],"*")));"

Now I am getting a different error. It says invalid outside procedure at the Set db?

Any help appreciated!!
 
In the code you have posted you have an End Sub line but you don't have a Subroutine line. I think this is what is giving your error.
 
Thanks. However still getting the same error without that line. I was trying to pattern off some other vb code I had.

After I click ok on the error message it always goes to the line:

Set db = CurrentDb

Are there rules about using this command that I am not following?
 
anchorp said:
Thanks. However still getting the same error without that line. I was trying to pattern off some other vb code I had.

After I click ok on the error message it always goes to the line:

Set db = CurrentDb

Are there rules about using this command that I am not following?
Click to expand...

Of course you would get that error. You need to put it INSIDE a sub or function
Code: 
Private Sub YourSubNameHere()
  ...put your code here
End Sub
or
Code: 
Private Function YourFunctionNameHere()
...put your code here
End Function
 
The code in moddule should look like

Code: 
Option Compare Database
Option Explicit
Sub SubRoutineName
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset()
Dim crit As String
[INDENT]crit = "SELECT tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours]" & _
FROM (tblEstList INNER JOIN ((tblPhaseItem INNER JOIN qryTruckingDtl ON tblPhaseItem.PhaseID = qryTruckingDtl.PhaseID) INNER JOIN LtblTrucks ON qryTruckingDtl.TruckID = LtblTrucks.TruckID) ON tblEstList.EstID = tblPhaseItem.EstID) LEFT JOIN tblPhaseDtl ON tblPhaseItem.PhaseID = tblPhaseDtl.PhaseID" & _
GROUP BY tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours]" & _
HAVING (((tblEstList.EstNum) Like Nz([Forms]![frmEfficiencyReportInput]![EstNum],"*")) AND ((tblEstList.EstName) Like Nz([Forms]![frmEfficiencyReportInput]![EstName],"*")) AND ((qryTruckingDtl.Date) Between Nz([Forms]![frmEfficiencyReportInput]![StartDate],#1/1/1900#) And Nz([Forms]![frmEfficiencyReportInput]![EndDate],#12/31/9999#)) AND ((qryTruckingDtl.DriverID) Like Nz([Forms]![frmEfficiencyReportInput]![Driver],"*")) AND ((qryTruckingDtl.TruckID) Like Nz([Forms]![frmEfficiencyReportInput]![Truck],"*")));"
[/INDENT]rst.Close
db.Close
Exit Sub
End Sub

You cant just have pieces of code floating around. Thats why you were getting the error messages
 
Last edited:
Rabbie said:
Beat me to it Bob!
Click to expand...

Don't worry, there's more to deal with in this thread. Once the procedure is in place I'm guessing that there will need to be some work around the form references in the SQL string (adding quotes, octothorpes for dates, etc.) :D
 
I don't know how many questions I can ask before I overstay my welcome?

I am still not understanding what I am doing wrong. Couple problems...

If I include the Subroutine line is errors on that line?

If I don't include it then is errors on the Set db = CurrentDb?

Any thoughts? I'm just trying to create a value in a specific cell in my report based on this query...

Thanks again!!
 
anchorp said:
I don't know how many questions I can ask before I overstay my welcome?

I am still not understanding what I am doing wrong. Couple problems...

If I include the Subroutine line is errors on that line?

If I don't include it then is errors on the Set db = CurrentDb?

Any thoughts? I'm just trying to create a value in a specific cell in my report based on this query...

Thanks again!!
Click to expand...

Okay, read my post again. You need to put this code within a PROCEDURE:

Code: 
Option Compare Database
Option Explicit

[COLOR="Red"]Sub GiveItANameHere()[/COLOR]

    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset()
    Dim crit As String

    crit = "SELECT tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours] " & _
           "FROM (tblEstList INNER JOIN ((tblPhaseItem INNER JOIN qryTruckingDtl ON tblPhaseItem.PhaseID = qryTruckingDtl.PhaseID) INNER JOIN LtblTrucks ON qryTruckingDtl.TruckID = LtblTrucks.TruckID) ON tblEstList.EstID = tblPhaseItem.EstID) LEFT JOIN tblPhaseDtl ON tblPhaseItem.PhaseID = tblPhaseDtl.PhaseID " & _
           "GROUP BY tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours] " & _
           "HAVING (((tblEstList.EstNum) Like Nz([Forms]![frmEfficiencyReportInput]![EstNum]," * ")) AND ((tblEstList.EstName) Like Nz([Forms]![frmEfficiencyReportInput]![EstName],'*')) AND ((qryTruckingDtl.Date) Between Nz([Forms]![frmEfficiencyReportInput]![StartDate],#1/1/1900#) And Nz([Forms]![frmEfficiencyReportInput]![EndDate],#12/31/9999#)) AND ((qryTruckingDtl.DriverID) Like Nz([Forms]![frmEfficiencyReportInput]![Driver],'*')) AND ((qryTruckingDtl.TruckID) Like Nz([Forms]![frmEfficiencyReportInput]![Truck],'*')));"
    rst.Close
    db.Close
    Exit Sub
[COLOR="red"]End Sub[/COLOR]
 
And then there are still things wrong with it:

1. You are trying to open the recordset before setting the crit variable. You need to do it first and THEN open the recordset. (see below)

2. You aren't doing anything with the recordset. What is it supposed to do? It doesn't make sense to run a SELECT query like this.

Code: 
Option Compare Database
Option Explicit

Sub GiveItANameHere()

    Dim db As Database
    Dim rst As Recordset
    Dim crit As String

    Set db = CurrentDb

    crit = "SELECT tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours] " & _
           "FROM (tblEstList INNER JOIN ((tblPhaseItem INNER JOIN qryTruckingDtl ON tblPhaseItem.PhaseID = qryTruckingDtl.PhaseID) INNER JOIN LtblTrucks ON qryTruckingDtl.TruckID = LtblTrucks.TruckID) ON tblEstList.EstID = tblPhaseItem.EstID) LEFT JOIN tblPhaseDtl ON tblPhaseItem.PhaseID = tblPhaseDtl.PhaseID " & _
           "GROUP BY tblEstList.EstNum, tblEstList.EstName, qryTruckingDtl.Date, qryTruckingDtl.PhaseID, qryTruckingDtl.Drivers, qryTruckingDtl.DriverID, qryTruckingDtl.Trucks, qryTruckingDtl.TruckID, qryTruckingDtl.[Total Hours] " & _
           "HAVING (((tblEstList.EstNum) Like Nz([Forms]![frmEfficiencyReportInput]![EstNum]," * ")) AND ((tblEstList.EstName) Like Nz([Forms]![frmEfficiencyReportInput]![EstName],'*')) AND ((qryTruckingDtl.Date) Between Nz([Forms]![frmEfficiencyReportInput]![StartDate],#1/1/1900#) And Nz([Forms]![frmEfficiencyReportInput]![EndDate],#12/31/9999#)) AND ((qryTruckingDtl.DriverID) Like Nz([Forms]![frmEfficiencyReportInput]![Driver],'*')) AND ((qryTruckingDtl.TruckID) Like Nz([Forms]![frmEfficiencyReportInput]![Truck],'*')));"

    [COLOR="Red"]Set rst = db.OpenRecordset(crit, dbFailOnError)[/COLOR]

    rst.Close
    db.Close

End Sub
 
If you continue to have problems, I'd question the criteria like:

Like Nz([Forms]![frmEfficiencyReportInput]![EstNum]," * "))
Click to expand...

Bob
 
raskew said:
If you continue to have problems, I'd question the criteria like:



Bob
Click to expand...
Actually, there's a lot of problems in there still. But too many to work on all at once so was attacking piece by piece :D
 
anchorp said:
I have a Text Box in a report in which I want to run a module that runs a query. Please let me know if I am on the right track?

Once it runs the query I want it to sum the Total Hours field and put that value in the report.

Any help appreciated!!!
Click to expand...

i would suggest you make the query in design view and reference it form there (gurus: can that be done that way?)
 
actually, what is the source for your report in the first place? maybe you can actually just total the values that are in the report already? i often place into an unbound textbox this kind of text:

=Sum([YourFieldThatYouWantToSumAndIsOnTheReport])
 
You must log in or register to reply here.

Similar threads

A
Summing Problem-Need Advanced Suggestion for This Query
Replies
2
Views
744
gemma-the-husky
gemma-the-husky
R
compare two tables, create query from lowest to highest value found
Replies
1
Views
1,964
RECrerar
R

Users who are viewing this thread

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