Creating conditions within a Report

[Cintury]

Hello I am new to this forum. I am using Microsoft Access 2003 and Windows XP 2002 ver.

I have 2 questions:
1) Is there always a substantial amount of visual basic coding to create a report in design view? (I ask because I am only familiar with "C/C++")

2) I am trying to create a report that will display the undistributed balances for a user entered month.

There is already a similar report made which includes donor company, receiving agency, date(always a single day 04/01/2007 ~ 04/01/2007), and then the poundage of food. (bakery, dairy, meat...etc.)

I see that in this report the undistributed balances are calculated by subtracting the total lbs of food in each donor company column by those in it corresponding receiving agency column. Note: corresponding agencies only appear on the report when the respective option box is selected. [ex. donor bakery (75) ~ agency1 bakery (25) ~ agency2 bakery(50) ~ undistributed balance (0)]

But what I want is to only show the donor/agencies with an undistributed balance and to not display the others.

[puppydogbuddy]

If you have a query as the record source for your report, the easiest thing to do is to open your query in design view and enter >0 in the criteria row of the undistributed balance column. Save query and rerun report...voilla!!!

[Cintury]

If you have a query as the record source for your report, the easiest thing to do is to open your query in design view and enter >0 in the criteria row of the undistributed balance column. Save query and rerun report...voilla!!!

The record value is a table. I already have the undistributed fields working. What I'm asking about is how I would make it so that the donor/agency was not shown at all if its undistributed balance was zero(0).

The purpose is to greatly shorten the time it takes to find all the undistributed balances in a given month instead of checking every single day.

[puppydogbuddy]

The record value is a table. I already have the undistributed fields working. What I'm asking about is how I would make it so that the donor/agency was not shown at all if its undistributed balance was zero(0).

The purpose is to greatly shorten the time it takes to find all the undistributed balances in a given month instead of checking every single day.

Here is another easy way to exclude 0 balances. Use the where clause option in the VBA command that you used to open the report, as shown below:

DoCmd.OpenReport "YourReportName", acViewNormal, , "[UndistributedBalance] >" & 0
 

Just substitue your actual field and report name for the generic names I used above.

[Cintury]

Here is another easy way to exclude 0 balances. Use the where clause option in the VBA command that you used to open the report, as shown below:

DoCmd.OpenReport "YourReportName", acViewNormal, , "[UndistributedBalance] >" & 0
 

Just substitue your actual field and report name for the generic names I used above.

Where in Access do I place this coding?

[puppydogbuddy]

Where in Access do I place this coding?

How do you currently launch (open) your report? The code would go behind a button click event for a button on a form. If you don't have a button, you can create one using the button wizard from the Access toolbox.

[Cintury]

In my database menu there is a button "FoodDonorByDonorReceipt" which opens up into more options that include:
-date from
-date to
-a scrollable list of donors
-a button to toggle "corresponding agencies"

The "FoodDonorByDonorReceipt" button is what I want to edit?

[puppydogbuddy]

In my database menu there is a button "FoodDonorByDonorReceipt" which opens up into more options that include:
-date from
-date to
-a scrollable list of donors
-a button to toggle "corresponding agencies"

The "FoodDonorByDonorReceipt" button is what I want to edit?

If that is the "report" that you are trying to change, yes. If you look at the code behind the button, you should see a line of code that is very similar to the one I gave you, except it doesn't have a where clause in it. That would be the line you want to replace with the code I gave you. Remember that before you use the line I gave you, you have to replace the generic report and field names that I used with their actual names in the application.

[Cintury]

If that is the "report" that you are trying to change, yes. If you look at the code behind the button, you should see a line of code that is very similar to the one I gave you, except it doesn't have a where clause in it. That would be the line you want to replace with the code I gave you. Remember that before you use the line I gave you, you have to replace the generic report and field names that I used with their actual names in the application.

The code I found behind the button shows a series of case statements for each of its corresponding reports. (each having 2 case statements) One of which displays the appropriate buttons and drop down menus. The second one is:
Case 14: 'Food Donation by Donor Receipt No
strReport = "rptFoodDonationbyReceipt"

What I have not found is what activates the actual opening of each report. Unless the second case statement as shown above is it.

[puppydogbuddy]

The code I found behind the button shows a series of case statements for each of its corresponding reports. (each having 2 case statements) One of which displays the appropriate buttons and drop down menus. The second one is:
Case 14: 'Food Donation by Donor Receipt No
strReport = "rptFoodDonationbyReceipt"

What I have not found is what activates the actual opening of each report. Unless the second case statement as shown above is it.

Cintury,
You are almost there, but not quite. Somewhere in your code, should be:
DoCmd.OpenReport strReport, acViewNormal.

Here is an idea: why can't you just create another button and put the following code behind it, replacing [UndistributedBalance] wth the actual name of your field.

Dim strReport as String
strReport = "rptFoodDonationbyReceipt"
DoCmd.OpenReport strReport, acViewNormal, , "[UndistributedBalance] >" & 0

[Cintury]

Cintury,
You are almost there, but not quite. Somewhere in your code, should be:
DoCmd.OpenReport strReport, acViewNormal.

Here is an idea: why can't you just create another button and put the following code behind it, replacing [UndistributedBalance] wth the actual name of your field.

Dim strReport as String
strReport = "rptFoodDonationbyReceipt"
DoCmd.OpenReport strReport, acViewNormal, , "[UndistributedBalance] >" & 0

Ok in the last line of the code for this form it shows:
DoCmd.OpenReport strReport, lngView, , strCriteria

strReport - stores the name of the report
lngView - shows the type of view we want
strCriteria - contains the various parameters such as begin/end date and crossref yes or no value

So the question is do I include "[UndistributedBalance] >" & 0 as part of the value for strCriteria?

[puppydogbuddy]

Ok in the last line of the code for this form it shows:
DoCmd.OpenReport strReport, lngView, , strCriteria

strReport - stores the name of the report
lngView - shows the type of view we want
strCriteria - contains the various parameters such as begin/end date and crossref yes or no value

So the question is do I include "[UndistributedBalance] >" & 0 as part of the value for strCriteria?

Yes!

strCriteria = "[UndistributedBalance] >" & 0
DoCmd.OpenReport strReport, lngView, , strCriteria
 

[Cintury]

Yes!

strCriteria = "[UndistributedBalance] >" & 0
DoCmd.OpenReport strReport, lngView, , strCriteria
 

Right now...
strCrteria = Nz(Forms!fdlgPrintReports!txtEnterBeginningDate, "") & IIf(IsNull(Forms!fdlgPrintReports!txtEnterEndingDa te), "", " Through " & Forms!fdlgPrintReports!txtEnterEndingDate)

So where would I add "[UndistributedBalance] >" & 0 ?
At the end of that or maybe another line?

[puppydogbuddy]

Right now...
strCrteria = Nz(Forms!fdlgPrintReports!txtEnterBeginningDate, "") & IIf(IsNull(Forms!fdlgPrintReports!txtEnterEndingDa te), "", " Through " & Forms!fdlgPrintReports!txtEnterEndingDate)

So where would I add "[UndistributedBalance] >" & 0 ?
At the end of that or maybe another line?

Try it this way:

strCrteria = Nz(Forms!fdlgPrintReports!txtEnterBeginningDate, "") & IIf(IsNull(Forms!fdlgPrintReports!txtEnterEndingDa te), "", " Through " & Forms!fdlgPrintReports!txtEnterEndingDate) & " And [UndistributedBalance] >" & 0

If that doesn't work you can define the [UndistributedBalance] >" & 0 as a filter Like this:

Dim strFilter as String
strFilter = "[UndistributedBalance] >" & 0
DoCmd.OpenReport strReport, lngView, strFilter, strCriteria

[Cintury]

Try it this way:

strCrteria = Nz(Forms!fdlgPrintReports!txtEnterBeginningDate, "") & IIf(IsNull(Forms!fdlgPrintReports!txtEnterEndingDa te), "", " Through " & Forms!fdlgPrintReports!txtEnterEndingDate) & " And [UndistributedBalance] >" & 0

If that doesn't work you can define the [UndistributedBalance] >" & 0 as a filter Like this:

Dim strFilter as String
strFilter = "[UndistributedBalance] >" & 0
DoCmd.OpenReport strReport, lngView, strFilter, strCriteria

When I did the first method I didnt get an error but the line of code turned red after I saved. So I tried the second method and as far as I know it worked until it got to the formatting part where the green bars would load up all the way and stop. Not as in I had to reset access but press ctrl+break

[puppydogbuddy]

When I did the first method I didnt get an error but the line of code turned red after I saved. So I tried the second method and as far as I know it worked until it got to the formatting part where the green bars would load up all the way and stop. Not as in I had to reset access but press ctrl+break

Sorry, I have something wrong in the syntax. try it this way:

Dim strFilter as String
strFilter = "[UndistributedBalance] >" & 0
DoCmd.OpenReport strReport, lngView, , strCriteria And strFilter

[Cintury]

Sorry, I have something wrong in the syntax. try it this way:

Dim strFilter as String
strFilter = "[UndistributedBalance] >" & 0
DoCmd.OpenReport strReport, lngView, , strCriteria And strFilter

Should this: strFilter = "[UndistributedBalance] >" & 0
be something like: strFilter = [FoodDonationByReceipt]![txtUndistributedTotal] > & 0

Linking it with the report in some kind of way?

[puppydogbuddy]

Should this: strFilter = "[UndistributedBalance] >" & 0
be something like: strFilter = [FoodDonationByReceipt]![txtUndistributedTotal] > & 0

Linking it with the report in some kind of way?

[UndistributedBalance] should be a field in the record source of your report and should show up in the reports field list If the above expression did not work, try it for just a test....leave out strCriteria so that the expression has only strFilter, and then tell me what happens..

[Cintury]

[UndistributedBalance] should be a field in the record source of your report and should show up in the reports field list If the above expression did not work, try it for just a test....leave out strCriteria so that the expression has only strFilter, and then tell me what happens..

I have Dim strFilter as String declared with the rest of the variables. I included strFilter = "[txtUndistributedTotal] >" & 0 under my report's respective case statement, and added in your code at the bottom as follows:

1. End Select
2. If strReport = "rptFoodDonationbyReceipt" Then
3. DoCmd.OpenReport strReport, lngView, strFilter And strCriteria
4. Else
5. DoCmd.OpenReport strReport, lngView, , strCriteria
6. End If
7. End Sub

I get this error: Run-time error '13': Type mismatch
When I go into debug it has item "3" from above highlighted.
Also when I run it without strCriteria in item "3" from above the report displays #Error in all the fields.

One more thing while I mouse over the words in item "3" this tooltip appears with the appropriate field in bold: OpenReport(ReportName,[View As AcView = acViewNormal],[Filter Name],[WhereCondition],[WindowMode As AcWindowMode = acWindowNormal],[OpenArgs])

[puppydogbuddy]

I have Dim strFilter as String declared with the rest of the variables. I included strFilter = "[txtUndistributedTotal] >" & 0 under my report's respective case statement, and added in your code at the bottom as follows:

1. End Select
2. If strReport = "rptFoodDonationbyReceipt" Then
3. DoCmd.OpenReport strReport, lngView, strFilter And strCriteria
4. Else
5. DoCmd.OpenReport strReport, lngView, , strCriteria
6. End If
7. End Sub

I get this error: Run-time error '13': Type mismatch
When I go into debug it has item "3" from above highlighted.
Also when I run it without strCriteria in item "3" from above the report displays #Error in all the fields.

One more thing while I mouse over the words in item "3" this tooltip appears with the appropriate field in bold: OpenReport(ReportName,[View As AcView = acViewNormal],[Filter Name],[WhereCondition],[WindowMode As AcWindowMode = acWindowNormal],[OpenArgs])

Cintury,
The change you made and your explanation of what happened were excellent. Thanks for your patience while we get the syntax ironed out. Try replacing the statement in your code with the following, rerun the report and let me know what happens.

strFilter = "[UndistributedBalance] >0"

For purposes of debugging, put a message box following the end select statement as shown below. Let me know what happened. Thanks.

End Select
MsgBox strFilter & " And " & strCriteria

[Cintury]

Cintury,
The change you made and your explanation of what happened were excellent. Thanks for your patience while we get the syntax ironed out. Try replacing the statement in your code with the following, rerun the report and let me know what happens.

strFilter = "[UndistributedBalance] >0"

For purposes of debugging, put a message box following the end select statement as shown below. Let me know what happened. Thanks.

End Select
MsgBox strFilter & " And " & strCriteria

I got the same error. This displays in the msg box: [UndistributedBalance] >0 And [Date]>=#6/1/2007# AND [Date]<=#6/1/2007#

Then I changed

DoCmd.OpenReport strReport, lngView, strFilter And strCriteria

to

DoCmd.OpenReport strReport, lngView, strFilter, strCriteria

I didn't get an error but all donors/agencies appeared as normal those with and without an undistributed balance.

This is a screen shot of what my report looks like: http://i19.photobucket.com/albums/b160/MadAlpha/ssRpt.jpg
This is a screen shot of what I want removed: http://i19.photobucket.com/albums/b160/MadAlpha/ssRpt2.jpg

[puppydogbuddy]

I got the same error. This displays in the msg box: [UndistributedBalance] >0 And [Date]>=#6/1/2007# AND [Date]<=#6/1/2007#

Then I changed

DoCmd.OpenReport strReport, lngView, strFilter And strCriteria

to

DoCmd.OpenReport strReport, lngView, strFilter, strCriteria

I didn't get an error but all donors/agencies appeared as normal those with and without an undistributed balance.

This is a screen shot of what my report looks like: http://i19.photobucket.com/albums/b160/MadAlpha/ssRpt.jpg
This is a screen shot of what I want removed: http://i19.photobucket.com/albums/b160/MadAlpha/ssRpt2.jpg

Cintury,
If [UndistributedBalance] is the name of the field that generated the 0 amount on the report, then one of the syntaxes below should work. However, in looking at the report graphic you posted, it looks like UndistributedBalance is just a heading and is not the name of the field/control that is displaying the 0 amounts. To verify, place the report in design view, go to the designer toolbar, look for an icon that displays the word Field List and make sure UndistributedBalance is listed. If it isn't, highlight the controls that are displaying the zero amounts and get me the name via the name property.

In the meantime, if UndistributedBalance is the correct field/control name, here are some additional syntaxes for you to try.

try #1
leave strFilter as is and try the syntax that I used for the message box.
DoCmd.OpenReport strReport, lngView, strFilter & " And " & strCriteria

try #2
change strFilter to: strFilter = "Val([UndistributedBalance]) > 0"
DoCmd.OpenReport strReport, lngView, strFilter And strCriteria

try#3
change strFilter to: strFilter = "Val([UndistributedBalance]) > 0"
DoCmd.OpenReport strReport, lngView, strFilter & " And " & strCriteria

try#4
change strFilter to: strFilter = "Val([UndistributedBalance]) > 0"
DoCmd.OpenReport strReport, lngView, strFilter, strCriteria

[Cintury]

Cintury,
If [UndistributedBalance] is the name of the field that generated the 0 amount on the report, then one of the syntaxes below should work. However, in looking at the report graphic you posted, it looks like UndistributedBalance is just a heading and is not the name of the field/control that is displaying the 0 amounts. To verify, place the report in design view, go to the designer toolbar, look for an icon that displays the word Field List and make sure UndistributedBalance is listed. If it isn't, highlight the controls that are displaying the zero amounts and get me the name via the name property.

In the meantime, if UndistributedBalance is the correct field/control name, here are some additional syntaxes for you to try.

The first is just a label, and then the rest are like: txtUndistributedBakery
So I assume instead of using [UndistributedBalance] I should use the last one [txtUndistributedTotal] thats the name of the last one in that row.

[puppydogbuddy]

The first is just a label, and then the rest are like: txtUndistributedBakery
So I assume instead of using [UndistributedBalance] I should use the last one [txtUndistributedTotal] thats the name of the last one in that row.

Cintury,
You would have to get rid of each control individually, one by one. Now that you have a little more familiarity with what you are working with, maybe you will be willing to do it the easiest way.....the way I suggested in my first post....in the Query that is the source of the report. Trust me on this one... it is really very simple..here is a step by step:

1. place the report in design view, invoke the property sheet, look at the record source property for the report......it should reference either a query name or consist of a select statement.
2. if it references a query name, go to the query object window, highlight the query name, place it in design view. On the query grid in design view look for a column that says balance or undistributed balance or something similar. All you need to do is go to the criteria row for that column and put the following: >0

3. If the record source references a select statement, let me know and I will tell you how to save it as an Access query.

4. If the query does not contain a derived column representing the balance or undestributed balance, let me know and I will tell you how to add it to the query grid.

[Cintury]

Cintury,
You would have to get rid of each control individually, one by one. Now that you have a little more familiarity with what you are working with, maybe you will be willing to do it the easiest way.....the way I suggested in my first post....in the Query that is the source of the report. Trust me on this one... it is really very simple..here is a step by step:

1. place the report in design view, invoke the property sheet, look at the record source property for the report......it should reference either a query name or consist of a select statement.
2. if it references a query name, go to the query object window, highlight the query name, place it in design view. On the query grid in design view look for a column that says balance or undistributed balance or something similar. All you need to do is go to the criteria row for that column and put the following: >0

3. If the record source references a select statement, let me know and I will tell you how to save it as an Access query.

4. If the query does not contain a derived column representing the balance or undestributed balance, let me know and I will tell you how to add it to the query grid.

Ok I found what you are talking about the query object window where things are in a table-like-grid. But, I cannot find an appropriate column to enter >0. The record source is qrptFoodDonations does that mean query report?

These are the column titles DonorCompany, Date, Bakery, Dairy, Meat, Produce, Prepared, Non-Perishable, Non-Food, DonorReceiptId, Unscheduled, DonorID, FoodDonationsID, Juice, PaidAmount, County, InventoryTypeId, Description, FinalDistribution, Description, Fruit, and Vegetable.

I tried placing ">0" in the criteria row of all the food columns and I got #Error in all fields of the report.

Edit: The Undistributed balance only appears on the actual report. As in it takes the "txt" values on the report to come out with the undistributed balances. This is the txt in the "undistributed balance" box for bakery: =[Bakery]-IIf(IsNumeric([txtBakeryD]),[txtBakeryD],0)

[puppydogbuddy]

Ok I found what you are talking about the query object window where things are in a table-like-grid. But, I cannot find an appropriate column to enter >0. The record source is qrptFoodDonations does that mean query report?

These are the column titles DonorCompany, Date, Bakery, Dairy, Meat, Produce, Prepared, Non-Perishable, Non-Food, DonorReceiptId, Unscheduled, DonorID, FoodDonationsID, Juice, PaidAmount, County, InventoryTypeId, Description, FinalDistribution, Description, Fruit, and Vegetable.

I tried placing ">0" in the criteria row of all the food columns and I got #Error in all fields of the report.

Edit: The Undistributed balance only appears on the actual report. As in it takes the "txt" values on the report to come out with the undistributed balances. This is the txt in the "undistributed balance" box for bakery: =[Bakery]-IIf(IsNumeric([txtBakeryD]),[txtBakeryD],0)

Cintury,

First, the answer to your question:
The record source is qrptFoodDonations does that mean query report? Close, but not quite. qrptFoodDonations is the name of the query that is the record source (extracts the data from the tables) for the report.

Secondly, based on the column titles provided, it looks like the expressions for both, the UndistributedBalance and the Distribution (e.g. txtBakeryD) for each product is done in the report. It would have made things easier if the distribution for each product was added as a column on the query grid, but since it was all done in the report, itself, let's work with that:

You need to change each one of the Undestributed balance textboxes on the report in a similar fashion to what is shown below:
Change this:
=[Bakery]-IIf(IsNumeric([txtBakeryD]),[txtBakeryD],0)

to this:

=IIf(nz([Bakery],0) - nz([txtBakeryD],0) <> 0, (nz([Bakery],0) - nz([txtBakeryD],0)), "")

all the above expression says is: if the undistributed difference between the total Bakery amount and the distributed amount <> 0 (after nulls have been converted to 0), then show the undistributed difference, otherwise leave it blank.

Let me know how it goes.

.

[Cintury]

Cintury,

First, the answer to your question:
The record source is qrptFoodDonations does that mean query report? Close, but not quite. qrptFoodDonations is the name of the query that is the record source (extracts the data from the tables) for the report.

Secondly, based on the column titles provided, it looks like the expressions for both, the UndistributedBalance and the Distribution (e.g. txtBakeryD) for each product is done in the report. It would have made things easier if the distribution for each product was added as a column on the query grid, but since it was all done in the report, itself, let's work with that:

You need to change each one of the Undestributed balance textboxes on the report in a similar fashion to what is shown below:
Change this:
=[Bakery]-IIf(IsNumeric([txtBakeryD]),[txtBakeryD],0)

to this:

=IIf(nz([Bakery],0) - nz([txtBakeryD],0) <> 0, (nz([Bakery],0) - nz([txtBakeryD],0)), "")

all the above expression says is: if the undistributed difference between the total Bakery amount and the distributed amount <> 0 (after nulls have been converted to 0), then show the undistributed difference, otherwise leave it blank.

Let me know how it goes.

.

So would I have to change the last one (UndistributedTotal) from this:
=[txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+
[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+
[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]

Its all one line with no spaces, but it made the page really long so I broke it up.

Edit: Some of the lines where like this =[Non-Perishable]-IIf(IsNumeric([txtNonPerisD]),[txtNonPerisD],0)
with shortened names how would I tranlate that into your formula?

[puppydogbuddy]

So would I have to change the last one (UndistributedTotal) from this:
=[txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+
[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+
[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]

Its all one line with no spaces, but it made the page really long so I broke it up.

Edit: Some of the lines where like this =[Non-Perishable]-IIf(IsNumeric([txtNonPerisD]),[txtNonPerisD],0)
with shortened names how would I tranlate that into your formula?

Cintury,
1. <<<So would I have to change the last one (UndistributedTotal)>>>>>
Hopefully, no. This is just the grand sum of the individual product totals.

2. <<<<Its all one line with no spaces, but it made the page really long so I broke it up.>>>>
Shift + F2 while in the line will give you a page view.

3. <<<<=[Non-Perishable]-IIf(IsNumeric([txtNonPerisD]),[txtNonPerisD],0)>>>>>

=IIf(nz([Non-Perishable],0) - nz([txtNonPerisD],0) <> 0, (nz([Non-Perishable],0) - nz([txtNonPerisD],0)), "")

[Cintury]

Cintury,
1. <<<So would I have to change the last one (UndistributedTotal)>>>>>
Hopefully, no. This is just the grand sum of the individual product totals.

2. <<<<Its all one line with no spaces, but it made the page really long so I broke it up.>>>>
Shift + F2 while in the line will give you a page view.

3. <<<<=[Non-Perishable]-IIf(IsNumeric([txtNonPerisD]),[txtNonPerisD],0)>>>>>

=IIf(nz([Non-Perishable],0) - nz([txtNonPerisD],0) <> 0, (nz([Non-Perishable],0) - nz([txtNonPerisD],0)), "")

Ok for the most part it works. These are some problems that remain:

1. Getting #Error for total column
2. Running report asks for parameter value of txtVegetable
3. All beverage fields display "-1"

Juice looks different too: =nz([Juice],0)-IIf(IsNumeric([txtBeverageD]),[txtBeverageD],0)

Other than those problems all zero balances are not shown/hidden ^_^

[puppydogbuddy]

Ok for the most part it works. These are some problems that remain:

1. Getting #Error for total column
2. Running report asks for parameter value of txtVegetable
3. All beverage fields display "-1"

Juice looks different too: =nz([Juice],0)-IIf(IsNumeric([txtBeverageD]),[txtBeverageD],0)

Other than those problems all zero balances are not shown/hidden ^_^

Good. That is a sign of progress!

1. Let's leave the total until we fix #2 and #3 below....
2. re: txtVegetable....that message probably means the parameter name is incorrect.....should it be txtVeg instead of txtVegetable? Highlight the applicable textbox in design view, invoke the property sheet, look at name property and compare to name used in expression for UndistributedBalance.
3. Re: value of -1 for beverage.....that means the amounts distributed was more than the amount received. You either need to reduce txtBeverageD by 1, or increase Juice by 1.
4. Yes, inconsistent with naming convention for the others, but not a problem if those were the names shown in the name property of the applicable textboxes.

After problems 2 and 3 fixed, let me know and we will fix #1.

[Cintury]

Good. That is a sign of progress!

1. Let's leave the total until we fix #2 and #3 below....
2. re: txtVegetable....that message probably means the parameter name is incorrect.....should it be txtVeg instead of txtVegetable? Highlight the applicable textbox in design view, invoke the property sheet, look at name property and compare to name used in expression for UndistributedBalance.
3. Re: value of -1 for beverage.....that means the amounts distributed was more than the amount received. You either need to reduce txtBeverageD by 1, or increase Juice by 1.
4. Yes, inconsistent with naming convention for the others, but not a problem if those were the names shown in the name property of the applicable textboxes.

After problems 2 and 3 fixed, let me know and we will fix #1.

Ok the total is still giving me the #Error but the other problems are fixed!

[puppydogbuddy]

Ok the total is still giving me the #Error but the other problems are fixed!

Cintury,
Try this for the UndistributedTotal:

=IIf(IsNumeric([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), [txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood], "")

[Cintury]

Cintury,
Try this for the UndistributedTotal:

When the undistributed balance is zero (0) this formula works correctly. But when there is something remaining I still get #Error. I tried to give you another screen shot but they don't want to upload to photobucket for some reason.

Edit: Is there a way I can change just this line of text to lets say red?

[puppydogbuddy]

When the undistributed balance is zero (0) this formula works correctly. But when there is something remaining I still get #Error. I tried to give you another screen shot but they don't want to upload to photobucket for some reason.

Edit: Is there a way I can change just this line of text to lets say red?

Cintury,


Try this for UndistributedTotal. I added the Val function. If that doesn't work, let me know.
=IIf(IsNumeric([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), Val([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), "")


Re: Color red....If you want it to be red every time the report runs, all you have to do is highlight the textbox in design view and invoke the property sheet. About half way down the property sheet, you will see a property named "Back Color". If you place your mouse cursor on the color number and you will see a button on the right with 3 Dots on it (referred to as a "build" button). Click the build buttonl and it will bring up the color picker.

[Cintury]

Cintury,


Try this for UndistributedTotal. I added the Val function. If that doesn't work, let me know.
=IIf(IsNumeric([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), Val([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), "")


Re: Color red....If you want it to be red every time the report runs, all you have to do is highlight the textbox in design view and invoke the property sheet. About half way down the property sheet, you will see a property named "Back Color". If you place your mouse cursor on the color number and you will see a button on the right with 3 Dots on it (referred to as a "build" button). Click the build buttonl and it will bring up the color picker.

I have the color changed thank you. But the total field is still giving me #Error when I have undistributed balances.

[puppydogbuddy]

I have the color changed thank you. But the total field is still giving me #Error when I have undistributed balances.

Cintury,
try this:
=IIf(Val([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood])> 0, Val([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), "")

If the "blanket" Val test above does not work, then we will have to do a Val test on each individual total within the blanket. Let me know. Thanks for your patience.

[Cintury]

Cintury,
try this:
=IIf(Val([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood])> 0, Val([txtUndistributedBakery]+[txtUndistributedDairy]+[txtUndistributedMeat]+[txtUndistributedFruit]+[txtUndistributedVeg]+[txtUndistributedProduce]+[txtUndistributedPrepared]+[txtUndistributedBeverage]+[txtUndistributedNonPerish]+
[txtUndistributedNonFood]), "")

If the "blanket" Val test above does not work, then we will have to do a Val test on each individual total within the blanket. Let me know. Thanks for your patience.

The total field is still giving me #Error when I have undistributed balances.

Edit: We also have times where we just want to show that the driver went to a stop even if nothing was donated. So this results in the donor side have all zeros (0) and the corresponding agency side not showing anything, because there is nothing to be delivered. This gives #Error in all of those fields and my co-worker said thats probably not going to fly when the boss wants a report printed out for viewing and presentation purposes.

What would be nice here is maybe another statement saying if the amount donated is zero (0) then don't subtract. Or if the amount donated was zero then make the corresponding agency zeros as well. If either is possible it would help me a great deal.

[Cintury]

The total field is still giving me #Error when I have undistributed balances.

Edit: We also have times where we just want to show that the driver went to a stop even if nothing was donated. So this results in the donor side have all zeros (0) and the corresponding agency side not showing anything, because there is nothing to be delivered. This gives #Error in all of those fields and my co-worker said thats probably not going to fly when the boss wants a report printed out for viewing and presentation purposes.

What would be nice here is maybe another statement saying if the amount donated is zero (0) then don't subtract. Or if the amount donated was zero then make the corresponding agency zeros as well. If either is possible it would help me a great deal.

Since I couldn't get photobucket to work again I used TinyPic.com these are the 2 things I have stated in the above post:
http://tinypic.com/view.php?pic=54ec4tf
http://tinypic.com/view.php?pic=5250hae

[puppydogbuddy]

The total field is still giving me #Error when I have undistributed balances.

Edit: We also have times where we just want to show that the driver went to a stop even if nothing was donated. So this results in the donor side have all zeros (0) and the corresponding agency side not showing anything, because there is nothing to be delivered. This gives #Error in all of those fields and my co-worker said thats probably not going to fly when the boss wants a report printed out for viewing and presentation purposes.

What would be nice here is maybe another statement saying if the amount donated is zero (0) then don't subtract. Or if the amount donated was zero then make the corresponding agency zeros as well. If either is possible it would help me a great deal.

Cintury,
Try this:

=IIf(Nz([txtUndistributedBakery])+Nz([txtUndistributedDairy])+Nz([txtUndistributedMeat])+Nz([txtUndistributedFruit])+Nz([txtUndistributedVeg])+Nz([txtUndistributedProduce])+Nz([txtUndistributedPrepared])+Nz([txtUndistributedBeverage])+Nz([txtUndistributedNonPerish])+Nz([txtUndistributedNonFood]))> 0, Nz([txtUndistributedBakery])+Nz([txtUndistributedDairy])+Nz([txtUndistributedMeat])+Nz([txtUndistributedFruit])+Nz([txtUndistributedVeg])+Nz([txtUndistributedProduce])+Nz([txtUndistributedPrepared])+Nz([txtUndistributedBeverage])+Nz([txtUndistributedNonPerish])+Nz([txtUndistributedNonFood]), "")

[Cintury]

Cintury,
Try this:

=IIf(Nz([txtUndistributedBakery])+Nz([txtUndistributedDairy])+Nz([txtUndistributedMeat])+Nz([txtUndistributedFruit])+Nz([txtUndistributedVeg])+Nz([txtUndistributedProduce])+Nz([txtUndistributedPrepared])+Nz([txtUndistributedBeverage])+Nz([txtUndistributedNonPerish])+Nz([txtUndistributedNonFood]))> 0, Nz([txtUndistributedBakery])+Nz([txtUndistributedDairy])+Nz([txtUndistributedMeat])+Nz([txtUndistributedFruit])+Nz([txtUndistributedVeg])+Nz([txtUndistributedProduce])+Nz([txtUndistributedPrepared])+Nz([txtUndistributedBeverage])+Nz([txtUndistributedNonPerish])+Nz([txtUndistributedNonFood]), "")

This one eliminated the total altogether.

[puppydogbuddy]

This one eliminated the total altogether.

Cintury,
Not sure what you mean. Is it working the way you want?

[Cintury]

Cintury,
Not sure what you mean. Is it working the way you want?

Sorry, what I mean't was the space there is now blank. No number, no #Error, just nothing. It is fine when there aren't any undistributed balances, but like in that screen shot (4 posts above) where it was 606 Vegetables left over the total should be 606.

[puppydogbuddy]

Sorry, what I mean't was the space there is now blank. No number, no #Error, just nothing. It is fine when there aren't any undistributed balances, but like in that screen shot (4 posts above) where it was 606 Vegetables left over the total should be 606.

Ok, try this:

=IIf(Val(Nz([txtUndistributedBakery]))+Val(Nz([txtUndistributedDairy]))+Val(Nz([txtUndistributedMeat]))+Val(Nz([txtUndistributedFruit]))+Val(Nz([txtUndistributedVeg]))+Val(Nz([txtUndistributedProduce]))+Val(Nz([txtUndistributedPrepared]))+Val(Nz([txtUndistributedBeverage]))+Val(Nz([txtUndistributedNonPerish]))+Val(Nz([txtUndistributedNonFood])))> 0, Val(Nz([txtUndistributedBakery]))+Val(Nz([txtUndistributedDairy]))+Val(Nz([txtUndistributedMeat]))+Val(Nz([txtUndistributedFruit]))+Val(Nz([txtUndistributedVeg]))+Val(Nz([txtUndistributedProduce]))+Val(Nz([txtUndistributedPrepared]))+Val(Nz([txtUndistributedBeverage]))+Val(Nz([txtUndistributedNonPerish]))+Val(Nz([txtUndistributedNonFood])), "")

[Cintury]

Ok, try this:

=IIf(Val(Nz([txtUndistributedBakery]))+Val(Nz([txtUndistributedDairy]))+Val(Nz([txtUndistributedMeat]))+Val(Nz([txtUndistributedFruit]))+Val(Nz([txtUndistributedVeg]))+Val(Nz([txtUndistributedProduce]))+Val(Nz([txtUndistributedPrepared]))+Val(Nz([txtUndistributedBeverage]))+Val(Nz([txtUndistributedNonPerish]))+Val(Nz([txtUndistributedNonFood])))> 0, Val(Nz([txtUndistributedBakery]))+Val(Nz([txtUndistributedDairy]))+Val(Nz([txtUndistributedMeat]))+Val(Nz([txtUndistributedFruit]))+Val(Nz([txtUndistributedVeg]))+Val(Nz([txtUndistributedProduce]))+Val(Nz([txtUndistributedPrepared]))+Val(Nz([txtUndistributedBeverage]))+Val(Nz([txtUndistributedNonPerish]))+Val(Nz([txtUndistributedNonFood])), "")

When I went back in I saw the problem for the case before this. (The box said "Unbound when I went to edit it) It gave me this error: The expression you have entered has a function containing the wrong number of arguments. So this error applies to the last (2) formulas you gave me.

[puppydogbuddy]

Ok,eliminate the extra right parentheses before the > 0 in both case as shown below:

Change this:
+Val(Nz([txtUndistributedNonFood])))> 0,

to this:
+Val(Nz([txtUndistributedNonFood]))> 0,

[Cintury]

Ok,eliminate the extra right parentheses before the > 0 in both case as shown below:

Change this:
+Val(Nz([txtUndistributedNonFood])))> 0,

to this:
+Val(Nz([txtUndistributedNonFood]))> 0,

It works now. I'm in the process of transferring it from the test replica to the server.
Any idea how I could stop the #Errors from showing in the cases with no food donations/agencies? I won't be printing these reports in the meantime just using them for the monthly balancing.

I just want to say thank you for all your hard work so far. I've learned a lot in the past couple of weeks thanks to your patience! :)

[puppydogbuddy]

It works now. I'm in the process of transferring it from the test replica to the server.
Any idea how I could stop the #Errors from showing in the cases with no food donations/agencies? I won't be printing these reports in the meantime just using them for the monthly balancing.

I just want to say thank you for all your hard work so far. I've learned a lot in the past couple of weeks thanks to your patience! :)

Cintury,
You are most welcome. I am glad I could help.

<<<Any idea how I could stop the #Errors from showing in the cases with no food donations/agencies? I won't be printing these reports in the meantime just using them for the monthly balancing>>>>>

What expression are you referring to, specifically, that is giving you the #Error?Post the Donor side and Agency Side.

[Cintury]

This is a SS: http://i19.photobucket.com/albums/b160/MadAlpha/ssRpt5.jpg

The drivers enter a zero donation for a donor who they went to but did not have any donation for that day. Every instance in the report where this happens it looks like the above SS.

There is no corresponding agency due to it being nothing delivered. So the statements, that you have given me, go through thier processes without any data resulting in the #Error. Something like (0 - x = #Error) Zero being the donation and x being the absence of data for the corresponding agency.

[puppydogbuddy]

This is a SS: http://i19.photobucket.com/albums/b160/MadAlpha/ssRpt5.jpg

The drivers enter a zero donation for a donor who they went to but did not have any donation for that day. Every instance in the report where this happens it looks like the above SS.

There is no corresponding agency due to it being nothing delivered. So the statements, that you have given me, go through thier processes without any data resulting in the #Error. Something like (0 - x = #Error) Zero being the donation and x being the absence of data for the corresponding agency.

Cintury,
Try changing this:
+Val(Nz([txtUndistributedNonFood]))> 0,
to this:
+Val(Nz([txtUndistributedNonFood]))<> 0,

[Cintury]

Cintury,
Try changing this:
+Val(Nz([txtUndistributedNonFood]))> 0,
to this:
+Val(Nz([txtUndistributedNonFood]))<> 0,

Is this just for the total column? If so what changes would I make to the others ie Bakery, Dairy...?