If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report design question
I am creating a detailed Sales Journal. I have a "Net Sale" field from a
query. I also include payment terms (cash, check, credit in check boxes) in the same query. On my report, I would like to like each line to display the "Net Sale" in the respective "Cash", "Check", or "Credit" columns based on which box is checked. For example, if an individual sale line was a cash sale (denoted by a check on the cash check box, I want the net sale amount for that line to appear in the cash column. I think the VBA code that I would want to use is something like: If Me.Cash (the checkbox field for cash) = True Then Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale field) Else Me.Cashbal = " " The first question is if this is the proper use of code. The second question is where should such code be placed. Since the report fields themselves do not accept event code (greyed out), it seems that the code should be placed at the form level. But where? On Open? On Activate? Please advise. -- Robert Robinson |
#2
|
|||
|
|||
Report design question
I am not sure you need any code if the payment terms don't change frequently.
You could create your text boxes for each column and set the control sources to: =IIf([PmtTerms]="cash", [Net Sale], Null) Create additional similar text boxes for other terms. I would probably add a field to the payment terms lookup table that identifies the horizontal position of the column in the report. You could then add the table to your report's record source and use code in the on format to set the Left property of you [Net Sale] text box. -- Duane Microsoft Access MVP "Robert Robinson" wrote: I am creating a detailed Sales Journal. I have a "Net Sale" field from a query. I also include payment terms (cash, check, credit in check boxes) in the same query. On my report, I would like to like each line to display the "Net Sale" in the respective "Cash", "Check", or "Credit" columns based on which box is checked. For example, if an individual sale line was a cash sale (denoted by a check on the cash check box, I want the net sale amount for that line to appear in the cash column. I think the VBA code that I would want to use is something like: If Me.Cash (the checkbox field for cash) = True Then Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale field) Else Me.Cashbal = " " The first question is if this is the proper use of code. The second question is where should such code be placed. Since the report fields themselves do not accept event code (greyed out), it seems that the code should be placed at the form level. But where? On Open? On Activate? Please advise. -- Robert Robinson |
#3
|
|||
|
|||
Report design question
Wow. That was fast.
I did not even think about creating an expression in the control source. My check boxes are individual (yep, I shoulda bound them together in 1 control...rookie mistake...will do that next time) so each will have a True/False value. Thank you for your help. -- Robert Robinson "Duane Hookom" wrote: I am not sure you need any code if the payment terms don't change frequently. You could create your text boxes for each column and set the control sources to: =IIf([PmtTerms]="cash", [Net Sale], Null) Create additional similar text boxes for other terms. I would probably add a field to the payment terms lookup table that identifies the horizontal position of the column in the report. You could then add the table to your report's record source and use code in the on format to set the Left property of you [Net Sale] text box. -- Duane Microsoft Access MVP "Robert Robinson" wrote: I am creating a detailed Sales Journal. I have a "Net Sale" field from a query. I also include payment terms (cash, check, credit in check boxes) in the same query. On my report, I would like to like each line to display the "Net Sale" in the respective "Cash", "Check", or "Credit" columns based on which box is checked. For example, if an individual sale line was a cash sale (denoted by a check on the cash check box, I want the net sale amount for that line to appear in the cash column. I think the VBA code that I would want to use is something like: If Me.Cash (the checkbox field for cash) = True Then Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale field) Else Me.Cashbal = " " The first question is if this is the proper use of code. The second question is where should such code be placed. Since the report fields themselves do not accept event code (greyed out), it seems that the code should be placed at the form level. But where? On Open? On Activate? Please advise. -- Robert Robinson |
#4
|
|||
|
|||
Report design question
OK.
To compensate for the setup of my fields, I created the following formula for the control box of my "Cashbal" field: =IIf([Cash]="Yes",[Net Sale],0) I am getting the 0 just fine, but I should see the net sale amount in the cashbal field for a couple of lines. The first part of my statement is wrong, but I do not know how. -- Robert Robinson "Duane Hookom" wrote: I am not sure you need any code if the payment terms don't change frequently. You could create your text boxes for each column and set the control sources to: =IIf([PmtTerms]="cash", [Net Sale], Null) Create additional similar text boxes for other terms. I would probably add a field to the payment terms lookup table that identifies the horizontal position of the column in the report. You could then add the table to your report's record source and use code in the on format to set the Left property of you [Net Sale] text box. -- Duane Microsoft Access MVP "Robert Robinson" wrote: I am creating a detailed Sales Journal. I have a "Net Sale" field from a query. I also include payment terms (cash, check, credit in check boxes) in the same query. On my report, I would like to like each line to display the "Net Sale" in the respective "Cash", "Check", or "Credit" columns based on which box is checked. For example, if an individual sale line was a cash sale (denoted by a check on the cash check box, I want the net sale amount for that line to appear in the cash column. I think the VBA code that I would want to use is something like: If Me.Cash (the checkbox field for cash) = True Then Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale field) Else Me.Cashbal = " " The first question is if this is the proper use of code. The second question is where should such code be placed. Since the report fields themselves do not accept event code (greyed out), it seems that the code should be placed at the form level. But where? On Open? On Activate? Please advise. -- Robert Robinson |
#5
|
|||
|
|||
Report design question
Nevermind.
I found my mistake. My condition had quotes around it. Revised it thus: =IIf([Cash]=-1,[Net Sale],0) Works great now. Thanks again. -- Robert Robinson "Duane Hookom" wrote: I am not sure you need any code if the payment terms don't change frequently. You could create your text boxes for each column and set the control sources to: =IIf([PmtTerms]="cash", [Net Sale], Null) Create additional similar text boxes for other terms. I would probably add a field to the payment terms lookup table that identifies the horizontal position of the column in the report. You could then add the table to your report's record source and use code in the on format to set the Left property of you [Net Sale] text box. -- Duane Microsoft Access MVP "Robert Robinson" wrote: I am creating a detailed Sales Journal. I have a "Net Sale" field from a query. I also include payment terms (cash, check, credit in check boxes) in the same query. On my report, I would like to like each line to display the "Net Sale" in the respective "Cash", "Check", or "Credit" columns based on which box is checked. For example, if an individual sale line was a cash sale (denoted by a check on the cash check box, I want the net sale amount for that line to appear in the cash column. I think the VBA code that I would want to use is something like: If Me.Cash (the checkbox field for cash) = True Then Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale field) Else Me.Cashbal = " " The first question is if this is the proper use of code. The second question is where should such code be placed. Since the report fields themselves do not accept event code (greyed out), it seems that the code should be placed at the form level. But where? On Open? On Activate? Please advise. -- Robert Robinson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reporting subreport total on main report | BobV | Setting Up & Running Reports | 22 | November 1st, 2005 03:19 AM |
HELP! Design Question for Expense Report Database | Felix Holmes | Setting Up & Running Reports | 0 | June 10th, 2005 05:03 AM |
A report design question | Al | Setting Up & Running Reports | 3 | March 11th, 2005 09:41 PM |
A report design question | Al | Setting Up & Running Reports | 4 | August 11th, 2004 09:10 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |