Sum of select values in form filter Results

I have a basic knowledge of access and therefore not VB conversant. So any advice please keep simple.

I have created a form with which I already total the sum of the value of all records in the form header, using sum function. When filtering the form the expression returns the correct value for the record set filtered on.

What I also want to achive is a total value for certain records within the filter. I have tried DSum function and IIF argument unsuccesfully.

The record information is as follows:

Form name: Reservations
Field to sum: [No]
Condidtion: Where the [SalesItemID]='EP006'

Expression used:

=DSum(" [No] ","Reservations","[SalesItemID]= 'EP006' ")

This does return the correct number of SalesItem=EP006 in the form when the form is unfiltered, although it does not change to display the value of the records in the filtered set.

Anyone give me some not too technical direction.

Many thanks


Mariner

I have a sum calculated on a main form that is set from a text box in the footer of a subform. I have trained people using forms to use the right click and filter by selection for narrowing records in a datasheet. Problem is the calcualated value on main form is not being updated with the sum of the records in the datasheet. Is there a way to update this?

Thanks in advance,
vmon

Hello from Greece.
I have a little problem. Let's say I have a Tubular (Continues) Form with these Textboxes (assigned to fields in a Table Customers) :

SerialNo Name Date_Of_Birth Value
1 Theodore 9/12/76 5
2 Theodore 9/5/70 6
3 John 4/4/90 3
4 Maria 7/4/90 2
5 Anna 12/5/05 3

Then I apply filters (By right clicking and filter by selection). Let's say multiple filters for Name and Date_Of_Birth.

Is there any possible way, after the filtering, to have the Sum of the Value of each record (filtered Continues form) in a seperate textbox?

Thank a lot ...

I have a table which is a snapshot in time for monthly cost of a service. Each service has a start date, and if the service has been canceled, it has an end date. If the service has not been canceled, the end date field is empty.

I have a form which allows the user to determine what "year" they want the report to select and it is passed to the query and is applied as a filter. [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] ie 2011

I created a query for the report with four new fields where I extract the month and year of both the start and end date fields so I can do the comparisons to the fiscal year choosen by the user in the form.
[FiscalYearEndDate] ie 2011
[FiscalYearEndMonth] ie 4
[FiscalYearStartDate] ie 2011
[FiscalYearStartMonth] ie 4

The query runs without a problem, and I can see the proper data in those newly created fields.

I have created a summary report that will give a one year view of the data broken down by month, based on the start date and end date (if applicable). If fiscal year input by the user from the form is greater than the start date year of the service, OR if the start date year is the same as the input in the form, and the column month (Jan=1, Feb=2, Mar=3, etc.), ) is greater than or equal the start date month of the service, then include the Monthly Recurring Cost [MRC] in the sum. Keeping in mind, if the service also has an end date, and only if the end date year is equal to the fiscal year input into the form AND the end date month is greater than or equal to the column month (Jan=1, Feb=2, Mar=3, etc.), then include the [MRC] in the sum. Obviously, if the end date year is less than the fiscal year the user is requesting, the cost should not be included in the sum.

I have tried both of the following in the report and I get errors on both -- "you have entered a comma without a preceding value or identifier"

=Sum(IIf((isnull([FiscalYearEndDate]) AND [FiscalYearStartDate]

Hi:

I have a form bound to a Crosstab Query. The form has a list box from which the user can select the items to be displayed. With code I then set the filter to this selected item. However, the form displays all records returned by the Query. I have used Debug.Print me.filter to verify that the filter is correct and now I am stuck.

Some background info:

The purpose of the form is to display summary information. The list box is tied to a different query which presents a list of areas to the user. The Crosstab Query does a summary of all areas. Because of the nature of the Crosstab Query my form does not have any fields bound to the Crosstab Query. Instead I have several unbound fields and then I use code to set the Control Source of each Unbound field to a field from the Crosstab Query. All of that works fine and my form displays the data properly. However, what I now want is to limit the display to the user selected area but my filter is having no effect.

Any thoughts.

My Crosstab Query:

TRANSFORM Sum(qryGLVendor.NetAmt) AS SumOfNetAmt
SELECT qryGLVendor.SaleDate, qryGLVendor.RegisterName
FROM qryGLVendor
GROUP BY qryGLVendor.SaleDate, qryGLVendor.RegisterName
PIVOT qryGLVendor.Item ;

My code to set the filter:

Me.Form.Filter = "RegisterName=" & """" & RegisterNameGlbl & """"

Debug.Print Me.Filter


My code to locate the open form and assign Control Source to the unbound fields:

Private Sub subFillCol()

Dim frm As Form, intI As Integer
Dim intJ As Integer
Dim vAmount As Variant
Dim intControls As Integer, intForms As Integer

Dim rst As DAO.Recordset
Dim fld As Field
Dim intCnt As Integer

Set rst = Me.Recordset

intForms = Forms.Count ' Number of open forms.
'Check if any forms are open
If intForms > 0 Then
' Loop thru all open forms looking for frmGLSum
For intI = 0 To intForms - 1

Set frm = Forms(intI)
Select Case frm.Name
'Populate form from query
Case "frmGLSum"
intCnt = 1
For Each fld In rst.Fields ' Print field names.

Select Case VarType(fld.Value)
Case 0 To 1 'Value is a Null or empty
'Debug.Print " Fld 01 "; intCnt; " Name "; fld.Name; " "; fld.Value
Me("Hdn" & intCnt) = fld.Name
Me("Col" & intCnt).ControlSource = fld.Name '""

More similar code to populate unbound fields.

End Select
intCnt = intCnt + 1
Next
intCnt = 1

End Select
Next intI
Else
MsgBox "No open forms.", vbExclamation, "Form Controls"
'End check for any forms open
End If



End Sub

Hey guys i have a form called frmCOMPARISON (Screenshot attached for a better idea)

this form and its subform contain a series of comboboxes and txtboxes that i want to use as filters on a query to compare data from various periods and other options.

what i am wondering is how do i get it so that i can use the same form over and over again for various periods but record in the same query e.g. i will have a query called qryCOMPARISON where i want to store both a day number which is using DateDiff(startdate,date in htrxtbl) and a seperate column for each periiod containing the amount for that day.

what i have below is my logic of how to do this but im not sure how i keep P as the new value each time i re-open my form

any suggestion on what i am trying to do would be great


	Code:
	Dim P as integer

‘set p to be 1 at the opening of the form for the first time
P = 1

‘sets the caption of the form to “SALES COMPARISON PERIOD (and the number of the period)
frmComparison Caption = "SALES COMPARISON PERIOD" & P

' if user press next period button do this

If cmdNExt Selected

start at period(P)
‘DayNo is the datediff of the startdate and that day
‘Period(P)Amt is the sum of total sales for that day applying all filters off frmComparison
qryCOMPARISON= (SELECT DayNo:DateDiff() "Period"& P &"Amt":(Sum HTRX_VALUE) FROM HTRXTBL WHERE "frmcomparison filters
applied") 


‘adds 1 for the next period if next selected
add 1 to P

close form
clear filters(dept etc)
re openform

' repeat until one of the below chosen

'if command button exit selected clear all queries and filters
if cmdexit selected clear all

'if cmdprint selected view excel graph
if print selected export to excel for "qrySALESCOMPARISON"



Sorry for posting this here but it doesn't fit in just one catagory.

I'm having a bit of trouble using a manual input table and ODBC table to filter information through query into form view. Let me shed a little light on what's, what.

I'm looking at adding the totals of hours scanned/worked (sum of periodOfEvent) on job per "workcenternumber" (Scanning hour report - table [ODBC] SQL database on another server) then subtracting them from the hours given (job planning table - table [manual input]) to display hours remaining on project through query. [ITEMA]-[ITEMB]

Here's the problem: I can filter workcenternumber in the ODBC table "Scanning Hour Report" to display the various work center numbers, in this case it would be the following items:

BAL-HIGH
BAL-LOW
CM-TECH
CR-TECH
DW-JNR
DW-SNR
ENG
FS-JNR
FS-SNR
MS-HIGH
MS-LOW
MS-MED
MS-TECH
QA-TECH
SB-TECH
WB-BOIL
WB-HEAT
WB-WELD
WM-TECH

now I'm sitting with a query for each of these work center numbers with each query looking up hours given & subtracting hours worked to give you x amount hours remaining. Here are two example of these queries:

-------------------------------------------------------------------------------
--==1==--

SELECT DISTINCTROW [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, Sum([Scanning Hour Report].PeriodofEvent) AS [Sum Of PeriodofEvent], [Job Planning].[BAL-HIGH Hours Given]-[Sum Of PeriodofEvent] AS [Hours Remaining BAL-HIGH], [Job Planning].[BAL-HIGH Hours Given]

FROM [Job Planning] INNER JOIN [Scanning Hour Report] ON [Job Planning].[Project Number] = [Scanning Hour Report].WBSElement

GROUP BY [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, [Job Planning].[BAL-HIGH Hours Given], [Job Planning].[BAL-HIGH], [Job Planning].[MS-TECH], [Job Planning].[MS-TECH Hours Given], [Job Planning].[BAL-LOW], [Job Planning].[BAL-LOW Hours Given], [Job Planning].[CM-TECH], [Job Planning].[CM-TECH Hours Given], [Job Planning].[CR-TECH], [Job Planning].[CR-TECH Hours Given], [Job Planning].[DW-JNR], [Job Planning].[DW-JNR Hours Given], [Job Planning].[DW-SNR], [Job Planning].[DW-SNR Hours Given], [Job Planning].ENG, [Job Planning].[ENG Hours Given], [Job Planning].[FS-JNR], [Job Planning].[FS-JNR Hours Given], [Job Planning].[FS-SNR], [Job Planning].[FS-SNR Hours Given], [Job Planning].[MS-HIGH], [Job Planning].[MS-HIGH Hours Given], [Job Planning].[MS-LOW], [Job Planning].[MS-LOW Hours Given], [Job Planning].[MS-MED], [Job Planning].[MS-MED Hours Given], [Job Planning].[QA-TECH], [Job Planning].[QA-TECH Hours Given], [Job Planning].[SB-TECH], [Job Planning].[SB-TECH Hours Given], [Job Planning].[WB-BOIL], [Job Planning].[WB-BOIL Hours Given], [Job Planning].[WB-HEAT], [Job Planning].[WB-HEAT Hours Given], [Job Planning].[WB-WELD], [Job Planning].[WB-WELD Hours Given], [Job Planning].[WM-TECH], [Job Planning].[WM-TECH Hours Given]

HAVING ((([Scanning Hour Report].WorkCentreNumber)="BAL-HIGH"));


--==2==--

SELECT DISTINCTROW [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, [Job Planning].[MS-TECH Hours Given], Sum([Scanning Hour Report].PeriodofEvent) AS [Sum Of PeriodofEvent], [Job Planning].[MS-TECH Hours Given]-[Sum Of PeriodofEvent] AS [Hours Remaining]

FROM [Job Planning] INNER JOIN [Scanning Hour Report] ON [Job Planning].[Project Number] = [Scanning Hour Report].WBSElement

GROUP BY [Scanning Hour Report].WBSElement, [Scanning Hour Report].WorkCentreNumber, [Job Planning].[Project Number], [Job Planning].Customer, [Job Planning].Description, [Job Planning].[Planned Start], [Job Planning].[Progress %], [Job Planning].[Planned Finish], [Job Planning].[Actual Finish], [Job Planning].Consultant, [Job Planning].Team, [Job Planning].[MS-TECH Hours Given], [Job Planning].[MS-TECH], [Job Planning].[BAL-HIGH], [Job Planning].[BAL-HIGH Hours Given], [Job Planning].[BAL-LOW], [Job Planning].[BAL-LOW Hours Given], [Job Planning].[CM-TECH], [Job Planning].[CM-TECH Hours Given], [Job Planning].[CR-TECH], [Job Planning].[CR-TECH Hours Given], [Job Planning].[DW-JNR], [Job Planning].[DW-JNR Hours Given], [Job Planning].[DW-SNR], [Job Planning].[DW-SNR Hours Given], [Job Planning].ENG, [Job Planning].[ENG Hours Given], [Job Planning].[FS-JNR], [Job Planning].[FS-JNR Hours Given], [Job Planning].[FS-SNR], [Job Planning].[FS-SNR Hours Given], [Job Planning].[MS-HIGH], [Job Planning].[MS-HIGH Hours Given], [Job Planning].[MS-LOW], [Job Planning].[MS-LOW Hours Given], [Job Planning].[MS-MED], [Job Planning].[MS-MED Hours Given], [Job Planning].[QA-TECH], [Job Planning].[QA-TECH Hours Given], [Job Planning].[SB-TECH], [Job Planning].[SB-TECH Hours Given], [Job Planning].[WB-BOIL], [Job Planning].[WB-BOIL Hours Given], [Job Planning].[WB-HEAT], [Job Planning].[WB-HEAT Hours Given], [Job Planning].[WB-WELD], [Job Planning].[WB-WELD Hours Given], [Job Planning].[WM-TECH], [Job Planning].[WM-TECH Hours Given]

HAVING ((([Scanning Hour Report].WorkCentreNumber)="MS-TECH"));
-------------------------------------------------------------------------------

....these queries would display:

WBSElement - IT123456789012
WorkCentreNumber - BAL-HIGH
Customer - Customer000001
Description - High Speed balancing of turd filled drum
Planned Start - dd/mm/yyyy
Progress % - 95.00%
Planned Finish - dd/mm/yyyy
Actual Finish - dd/mm/yyyy
Consultant - Consultant1
Team - TEAM#
BAL-HIGH Hours Given - 20
Sum Of PeriodofEvent - 13.5
Hours Remaining - 6.5

The project number(Job planning table) and the WBSELEMENTNUMBER(Scanning Hour Report have linked via relationships tab

as an example... [see pic1]

now if the field finds no record in the ODBC it displays no information which would be correct in a sense. [see pic2]
BUT!
Here comes the complicated part.

NB!!:INSTEAD of displaying no information how would I go about making the ODBC fields display default value of 0 so when I add all the above mentioned workcenternumbers to ONE form, instead of displaying NOTHING when it finds no values for one of the workcenternumbers it will instead display that "50" hours given even if "0" hours were worked thus "50" hours still remain for that project number's work center number.
Is there an easier way of filtering different work center numbers to one form and summing figures from there or what?

To wrap it up all I'm looking for is a form that checks the workcenternumber and the hours captured on the ODBC "Scanning hour report" and subtracting it from the table "Job Planning" where the WBSELEMENT & Job Number as well as the work center numbers tie up to give me a total value of hours remaining. If nothing is found for one workcenternumber it displays value 0 instead of a entire blank page.
If you have a sample database I haven't seen that u think could come in handy it would be helpfull too.

Hope I didn't confuse you, yet...

Thanks in advance
solbane

I have been looking at some of the solutions to the running sum problem.

The queries I use cannot use the primary key as there are gaps as some filtering has been done with an unmatched query to give me my sample dataset.

From this dataset I have a list of courses and the number of learning hours per each course. This is the field I would like to perform the running sum on.

Its a parameter query and the intention is to email the query the the various schools so no form was necessary.

The solution on microsofts web site wants me to use a form and I dont understand how it works anyway.

Is there a code solution that can use a recordset that doesnt rely on forms?

At present I am reliant on the date which does work but not where a student does two courses on the same day. The forums advice was to use the autonumber but there are gaps in that so that doesnt work. can I generate an autonumber on the fly as an expression and use that in the sub query method?
Fields:
[Date] [Person Id] [Course Code]
[Course Name] [GLH] [Running Total] [UpliftCode]
[50%] [Rule Broken]

what I have so far:

SELECT Pro.Date, Pro.[Person Id], Pro.[Course Code], Pro.[Course Name], Pro.GLH, (Select Sum(Pro1.[GLH]) FROM [QryAllEnrolmentsWithoutMatchingQryWithdrawls] Pro1 WHERE Pro1.[Date][50%],"N","Y")) AS [Rule Broken]
FROM QryAllEnrolmentsWithoutMatchingQryWithdrawls AS Pro
ORDER BY Pro.Date;


kind regards in advance

Hi,

No idea why this code doesn't work, it's getting me the following error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

The query code:


	Code:
	SELECT EZHotelSalesByMonth.COD_PRESTATARIO, EZHotelSalesByMonth.DES_PRESTATARIO, EZHotelSalesByMonth.Clasification,
Sum(EZHotelSalesByMonth.[COUNT(1)]) AS [SumaDeCOUNT(1)]
FROM HotelContracts RIGHT JOIN EZHotelSalesByMonth ON HotelContracts.seq_hotel = EZHotelSalesByMonth.COD_PRESTATARIO
GROUP BY EZHotelSalesByMonth.COD_PRESTATARIO, EZHotelSalesByMonth.DES_PRESTATARIO, EZHotelSalesByMonth.Clasification,
IIf(([Forms]![MainForm]![CuadroMonth])=0,"",(([EZHotelSalesByMonth].[COD_MES]) Like "*" & [Forms]![MainForm]![CuadroMonth]))
HAVING (((IIf(([Forms]![MainForm]![CuadroMonth])=0,"",(([EZHotelSalesByMonth].[COD_MES]) Like "*" &
[Forms]![MainForm]![CuadroMonth])))False))
ORDER BY Sum(EZHotelSalesByMonth.[COUNT(1)]) DESC;

What I'm trying to do is to filter the record by a value in the field
[Forms]![MainForm]![CuadroMonth]. When the value is 0 or null in other words, it works fine. But when there is any other value, the error occurs...

I've also tried to it the other way round, and I easily managed to filter the records by a value in the selected form ComboBox, but when the field was empty the query wasn't summing up all records, it was just treating them as separate records and was enlisting them...

please help

I have a form with cascading combo boxes. The first combo box (Account) establishes the filter for what's to be seen in the second combo box (Clsssp). The user would then select a Clsssp for inputing into a subform.

I know how to make the subform sum up the values for the Clsssp they select. But what I don't know is how to make a total for all of the Clsssp's associated with the Account they have chosen. (meaning I need the Account's total after they've input data for all of their Clsssp's)

In other words: An account will have multiple Clsssp's and each Clsssp will have dollar values input for a particular month. I need to be able to get all of the Clsssp's assoc with a particular account added up so that I can show an account's total.

Attached is my database in streamlined form. The main form is frmInput. When you open it you see #error in the box where I'm trying to get a total for the account.

Can someone help me fix my code?

I have two subform in main form.
Main form is unbound form. I have 2 queries such as detail query,summary query. summary query is summary of detail query by sum(amount) groupby code. subform1 recordsource is detail query.subform2 recordsource is summary query. My problem is I select a combobox value , atonce detail query filter by combo value, and then result reflect by summary query,detail query. How will do it?
Another doubt I write subform1 current()
Me.Parent![subform2].Form.Filter = "
 = " & Me.Code & ""
Me.Parent![subform2].Form.FilterOn = True
Me.Parent![subform2].Requery
It work well. But First time it will show follow err msg
Quote: You entered an expression that has an invalid reference to the property Form/Report . Why?

Hi All,

I am a compleate new user so please be as kind as possible especially with the technical talk!!

I work at a sawmilling company and am in the process of making a basic database to enter in and then to look over our current stocks.

I have a form (Stock Filter) that has eight unbound combo boxes that lookup the values in eight seperate product description tables.

These combo boxes and there related product description tables are called:

Grade
Treatment
Location
Drying
Finish
Nominal Width
Nominal Thickness
Actual Width
Actual Thickness

The subform (Stock Filter Subform) is a Query that shows each pack of timber we have in stock, the product description information from the above tables, length information plus some general summing information.

Basically I want a "Filter by Selection" on each combo box. When the user chooses the value from each combo box the subform query is filtered to only show the records that corresond to that choice. He or she might filter by "GRADE" and then by "ACTUAL WIDTH" etc and so on.

I presume I also need a command button that removes all the filters at once so the user can start again and would also like a command button that sends the filtered information to a report that we can print, fax or email to customers

I have read and re-read the manual, looked at help and searched the forum but just can't get it to work. Everything I have created so far has been through the use of wizards and I draw a blank on macros and codes.

Can any one give me a step by step on how to set this up?

If I can get one combo box to work, In can follow copy the information for the other boxes.

Really appreciate your help.

JohnG From New Zealand.

I have a form which basically displays all the products sold by a take-away shop contained within a subform (recordset is based on a query called sales).

The fields in the subform (query) are
Date
Code
Product
Cost
Catagory (ie Mains, drinks, side salads, pasta, pizza)

The form I've created displays the above fields in order of date an the footer of the form displays a sum of the cost and catagorie (i.e. $11,111 and a total of 543 sales from the catagories that are displayed).

In the header I have combo-boxes for Date, Code and Catagory. What I want to be able to do is filter the data in the subform based on the selections chosen in the combo-boxes and if the combo-box is blank then to remove all filtering of that field. This is so the user can work out the total sales for a perticular item (i.e supreme pizza), a perticular category (ie pastas), or a perticular date.

I have tried to restrict the results of the query using criterias such as
Code = Form!SalesSubform!CodeComboBox!Code
However this doesn't seem to work because the query runs before the form opens, thus there is no information yet relating the query to the combo-box value.

Also, I don't know how the system will handle a change in the combo-box value - will the query run again after the change has been made to the combo-box value???

Thanks for any help you may be able to provide

Q.

I have a text box on a form containing a sum of several other boxes. I want to store this value in a field. I am using a on click event me.box = me.box2.

This works fine on a Single Form, but on Continuous Form it only does it to the record I have selected.

I Have tired on_current event same problem,
and on_load event but only works when i click on that record

Is there any code for calling all records shown on a filtered cont form to do the same at once?

Thanks

Query filter problem
Hi everyone

I have a database that I have created to track our work in progress. The primary table has the records of all of the jobs. I have created a form to control this table and I have a subform that tracks invoicing (more than one invoice per job).

I am trying to create a query that will allow me to have a summary of the information in the main table and show a sum of invoicing that is outstanding.

I have created the query and it is only working if there is any invoicing against the job. For examble Job 1389 Contract value $100,000 - invoiced so far $10,000 will show a balance of $90,000. This is currently working. I want it to show 0 in the invoicing if there is nothing against the job number but still show the job number and other fields that I select. Eg example above would be 0 in invoicing column and balance $100,000. At present as soon as I put any fields with anything to do with invoicing it will not show the entries in the main table that don't have any invoicing.

If I run a query on my sub form and show the sum for the fields it is not working either.

Sorry if this does not make sense, any feedback would be appreciated.

I am unsure of how to make the appropriate changes to make this work.

Kind Regards

Jodi

Here is my issue in greater detail. Sorry for not being more specific...I've tried Dsum, Davg, Dlookup and nothing works correctly.

I have a totals query that is I have a DSum field where I want to sum only the field in these column where the salary expense is greater than 0: GrossRev. Since I don't have PersonnelExp for every account listing, it's skewing the true personnel expense as a percent of gross revenue. (I have other fields that I need to sum all the values in the column regardless of zero values)

Totals Query:
GrossRev: DSum("[GrossRevenue]","**Single account ","[Salary/BenefitExp] > 0")


The Totals Query is pulling from another query where I have filtered based on two parameters from a form where I can select the data between two years to give me the results.

**Single Account query:
Between [forms]![frmSingleAcctGraph]![Year 1] And [forms]![frmSingleAcctGraph]![Year2]

The DSum field is not producing the correct calculations.

Attached is a sample db.

When you open frmSingleAcctGraph and select just one year, you get the right GrossRev amount - 94985997 (which is the last column). It's when I select both years 2007 and 2008 from form and the last GrossRev column simply sums the the two years together and repeats the same total for each - 148930091.

The **Graph Output Single account query isn't recognizing the filter from on the **Single account query which is Between [forms]![frmSingleAcctGraph]![Year 1] And [forms]![frmSingleAcctGraph]![Year2] Attached Files Database2.zip (178.4 KB, 2 views) Reply With Quote 02-16-2013, 10:30 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,117 I can't review db files until after Feb 22. If you still need help then, submit post.

Why do you show ** characters in the data source name? It is bad practice to use spaces, special characters, punctuation (underscore is exception) in any names.

Ok, so I have a Report that is filtered by several combo boxes on my main Form.

What I want is to change the value of a text box on a sub report on the main Report. The sub report that this combo box is on is either visible or not visible depending on the selection in the combo box. I have this part working properly

So now I want to change the value of the text box to "0" when the sub report is not visible. I placed the following code on the On Format event in the Details section of the main report:

	Code:
	Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Reports!rptInvoice!rptCoating.[Visible] = False Then
    Reports!rptInvoice!rptCoating!txtCoatingTotal = 0
    End If

End Sub

where "rptInvoice" is the main report, "rptCoating" is the optionally visible sub report and "txtCoatingTotal" is the text box who's value I wish to change

now when I open the report with the selection that causes the sub report (rptCoating) to be not visible, I get a "Runtime error: '2448' You can't assign a value to this object"

I'm just wondering how I can fix this. The text box total is based on a sum of a multiplication of a [Quantity] and [Unit Cost] field on the sub report. Would this have anything to do with it?

Thanks in advance for your help
Nathan

I have a number of reporting functions i need from my database. I've decided it can likely be all done at once.
In my explorations, I've read through several threads, and was in the process of implimenting this http://support.microsoft.com/kb/328320/en-us sort of thing.
The code from the above link, relies on a pre-existing query. I would like THIS:

	Code:
	 Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form

   Set dbsReport = CurrentDb
   Set frm = Forms!EmployeeSalesDialogBox 
   Set qdf = dbsReport.QueryDefs("EmployeeSales")
   
  
   qdf.Parameters("Forms!EmployeeSalesDialogBox!BeginningDate") _
     = frm!BeginningDate
   qdf.Parameters("Forms!EmployeeSalesDialogBox!EndingDate") _
     = frm!EndingDate

  Set rstReport = qdf.OpenRecordset()
 intColumnCount = rstReport.Fields.Count
End Sub

to be SOMETHING ELSE which would instead build my string based on the forms controls.

I dont think that that this is so complex that i might be better off just building all the reports one at a time. That seems like a cop-out and i still have a few days left in my internship to work with.

i need to base the report on # by Name, Name by #, or # only (options1-3),
with the ability to select individual names or #s for the report (1 cbo, 1 list),
optional details for the # from two separate tables, dollar values, field counts, and sums (4 chkboxes)
display one or all of three entrytypes (1option4-7)
and filter one of 4 seperate criteria (one of which should add another field to be displayed) (2option8-11)

If there is anyone with enough programming skilz to do this, would you be so kind as to point me a little furthur in the right direction?

First let me start by saying thank you in advance for all the outstanding information and support this forum has offered. This is my first post and this problem has had me tied in knots for months.

Essentially I need to build a report off a query or set of queries to calculate and compare two tables by certain fields. There are many columns in each table but the ones I am concerned with are;

Tables 1 (conducted)
City
State
College
County
Inspection type
user
date conducted

Table 2 (requirements)
City
State
College
County
Inspection type
Quantity required monthly
Quanitity required quarterly

I need to add up how many (inspection type) have been completed for each combination of city,state,college, and county. Then compare that total againts how many were required from table 2 with the same combination of city,state, college, and county.

The problem is that the table 1 values might be different than the table 2 values.

i.e. the requirement might be city 1, state 1, college 7, county null / the table 1 (conducted) records might have several records but they are listed against city 1, state 1, college 7, county 2.

If I ask the query to tell me how many un-fulfilled requirements last month against city 1, state 1, college 7 I need it to tell me WITHOUT concidering county because the combo box on the filter form for county was left blank.


------------

This is the SQL view for what i have so far but it doesnt count correctly. It thinks that if conducted lists a county and the requirement didnt list a county then it doesnt count/match.


	Code:
	SELECT [RILS Builder Listing].Squadron, [RILS Builder Listing].Flight, [RILS Builder Listing].Section AS Sections, [RILS
Builder Listing].Requirement, [RILS Builder Listing].[Inspection Type], Sum((nz([CountOfInspection Type]))) AS [Sum],
[Inspection Type Listing].[Description of Inspection Type], [Inspection Type Listing].AQL
FROM ([RILS Builder Listing] LEFT JOIN [RILS count] ON ([RILS Builder Listing].Squadron = [RILS count].Squadron) AND ([RILS
Builder Listing].Flight = [RILS count].Flight) AND ([RILS Builder Listing].[Inspection Type] = [RILS count].[Inspection
Type])) INNER JOIN [Inspection Type Listing] ON [RILS Builder Listing].[Inspection Type] = [Inspection Type
Listing].[Inspection Type]
GROUP BY [RILS Builder Listing].Squadron, [RILS Builder Listing].Flight, [RILS Builder Listing].Section, [RILS Builder
Listing].Requirement, [RILS Builder Listing].[Inspection Type], [Inspection Type Listing].[Description of Inspection Type],
[Inspection Type Listing].AQL
HAVING ((([RILS Builder Listing].Squadron) Like [Forms]![start page]![squadron] & "*") AND (([RILS Builder Listing].Flight)
Like [Forms]![start page]![flight] & "*") AND (([RILS Builder Listing].Section) Like [Forms]![start page]![section] & "*")
AND (([RILS Builder Listing].Requirement)>=1) AND ((Sum((nz([CountOfInspection Type]))))=1) AND ((Sum((nz([CountOfInspection
Type]))))=1) AND ((Sum((nz([CountOfInspection Type]))))

i have a form for the user to book in tools, the list of tools are filtered through three cascading combo boxes.

My problem is this, i am using a query to calculate individual tool stock levels. now if i book tools in and run the query separately it works ok but i need the query to run automatically once the tool selection in the final combo box has been made.

I am using AfterUpdate and DLookup to access the data from the query but this way i get a Null value every time and ive no idea why.

this is a copy of the sql for the query:

SELECT tblToolBooking.Tool_ID, Sum(tblToolBooking.Book_Value) AS Out, tblTool.Tool_Name, tblTool.Factory_Stock, tblTool.Factory_Stock+[Out] AS StockTotal
FROM tblTool INNER JOIN (tblOperator INNER JOIN (tblMachine INNER JOIN tblToolBooking ON tblMachine.Machine_ID = tblToolBooking.Machine_ID) ON tblOperator.Operator_ID = tblToolBooking.Operator_ID) ON tblTool.Tool_ID = tblToolBooking.Tool_ID
GROUP BY tblToolBooking.Tool_ID, tblTool.Tool_Name, tblTool.Factory_Stock
HAVING (((tblToolBooking.Tool_ID)=[Forms]![frmBookInOut]![cboToolName]));


This is the code for the DLookup:

Private Sub cboToolName_AfterUpdate()
Dim bkValue As Integer

MsgBox "qryCheck returns --> " & DCount("*", "qryCheck")
bkValue = Nz(DLookup("StockTotal", "qryCheck", "Tool_ID = " & Forms![frmBookInOut]!Tool_ID), 0)


If bkValue < 1 Then
MsgBox "The tool is already booked out or hasn't been returned"
Cancel = True
Me.Undo
End If
End Sub

i used this code to check what the query was returning and it is always a Null value

MsgBox "qryCheck returns --> " & DCount("*", "qryCheck")

I have no idea where to start, any help would be gratefully recieved.


Not finding an answer? Try a Google search.