Simple Module Coding: altering equations in Control Source

[hagerbmr]

I am designing a database for finances and have encountered only one problem. There are three fields in a form, all for currency. Enter two numbers which log into a table, then a query takes the difference and logs it for reference on another form. The third box has the simple equation x-y (where x and y are the names of first two boxes). I simply am trying to create a checkbox that will leave the information in the first to boxes, but turns the third box to zero. I need to be able to change the function in the control box. So I came with a module to do it, but can't seem to get all functions to work. Here it is in pseudo-code that I want to put as a function in box 3.
If checkbox = -1 (the value for when the checkbox is clicked I believe)
Set function (or control source) = 0.0 (or null value)
Set (function in query = 0 instead of x-y)
Else
Set function (or control source) = x-y (default)

I have only programmed in C, and although similar, VB keeps messing me up. Any help would be greatly appreciated. If your confused on the setup, I can send the DB to you to see when I am doing. Thanks

 

[TonyJollans]

Hi hagerbmr,

You don't need code just to show the value on the form. In the control source for the third field (using the names from your pseudocode) put ..

=IIf([checkbox],0,[x]-[y])

You may need some code if you are trying to do something else as well though. I didn't understand the bit about setting the function in the query. Post back if you want more.

Enjoy,
Tony

 

[hagerbmr]

Thanks Tony,
That works great. Yet, there is that other step that is still needed. What I do is take the information from Box 1 and 2 (X and Y), and insert them from the table that stores their value and reference in a query. The query takes the info and does the same expression (x-y), and uses that to compute the sum of all the values (x-y). What I need to do is add all the values of the data that are NOT going to be voided. What I mean is, if you enter the data into X and Y, but still click the checkbox, it will enter data X and Y into the table and still compute their result into the sum of all data. But it will display a zero value on the form if you click the checkbox. I need to be able to null show the value X and Y, and if the checkbox is clicked, make them NOT add up into the sum of data. I could accomplish this simply if I can make the expression you told me
[=IIf([checkbox],0,[x]-[y])], and let it return the 0 or x-y value into a field in the table. But I don't believe you can do this for this type of expression. I tried z (another field in the table) Z=IIf([checkbox],0,[x]-[y]) and Z:IIf([checkbox],0,[x]-[y]). But neither will work.
So, to sum up this problem, I either need a way to assign this statement to input the value into the field, or make the checkbox effect the sum expression in a query to null a row of values.
Hope this makes sense, and thanks

John

 

[hagerbmr]

Another problem arose with the checkbox issue. I am using it in a form, with multiple entries. It is for account's with multiple invoice violations, and there will be multiple entries per account. I need to be able to void out certain individual entries per account. While the checkbox did work, it made ALL entries void, not just the one I selected. The only thing I can think of is autocreating a new checkbox for each entry, but that will be more trouble then it is worth. Especially if I can't get the part from my previous entry to work..... anybody got any ideas?

I can always reset the database and design it differently if anyone can think of a easy way to accomplish these tasks.

Thanks again,
The now getting desperate -John

 

[TonyJollans]

Hi John,

I'm a bit tied up at the moment and will try to look at this properly later but very briefly ..

If your checkbox is unbound (i.e. not in the table/query) that is what will happen. I think you need to have individual checkboxes for each entry; I don't quite see how it makes sense without. Could you not add one to your table?

If you do have it in your table then you can use it within a query to do your checking and get 0 or x-y or whatever.

Enjoy,
Tony

 

[hagerbmr]

Alright Tony,

Your right about everything, and it is all working fine up to the very end now. I created a column for yes/no, and that works ok. My only problem is calculating the data. My exact expression in my query is:

v_Amount: Sum([t_table]![V_Ucarrieramount]-[t_table]![V_Acarrieramount]+[t_table]![V_AdminFee])

simply x=sum of (a-b+c)

I then tried numerous ways to sum up just the data with with checkbox=no, so I tried that in criteria. But I still need to see all the other data in my query, I just want to sum up those with the checkbox=no. I still need it to show everything with checkbox=no or yes.
I then tried Dsum with the expression as:

v_amount: Dsum("[t_table]![V_Ucarrieramount]-[t_table]![V_Acarrieramount]+[t_table]![V_AdminFee]", "[t_table]", "[t_table]![v_checkbox]=no&quot

I tried this mutiple times, and either it gives me wacky numbers, or just won't work. I did research and it says the last part "[t_table]![v_checkbox]=no" will affect my critera anyways and only show those with checkbox=no.

Bascially I just need to either write a macro to sum up the expression with check=no, or find a way to do it in the build log. Any ideas?
(BTW, thanks for the help)
-john

 

[TonyJollans]

Hi John,

It sounds like you're making some progress but I do appreciate you can feel like you're going round in circles and getting nowhere sometimes, so I hope this helps.

You want to have all your records in the query so don't put anything in the criteria. Only after you've got all the records can you start to sum them (or some of them) up. I'm not entirely sure which part of the sum is subject to the checkbox so, sticking with the x and y from before, the SQL for your query should be something like ..

SELECT x, y, iif(checkboxcolumn, 0, x-y) As Diff FROM Table WHERE whatever

And if you have a form based on the query, a control in the footer with control source ..

=Sum([Diff])

.. should give you what (I think) you want. Come back if I've misread you.

Enjoy,
Tony

 

[hagerbmr]

Ok Tony,

I got that database up and running thanks to your help. Now I have moved on to one with a very similar problem. I solved my last data problem with the expression in query = to
Expr1: IIf([V_Chk],0,(Sum([t_table]![V_Ucarrieramount]-[t_table]![V_Acarrieramount]+[t_table]![V_AdminFee])))
Very similar to what you told me in the earlier reply.

Ok now my new problem: I have basically the same setup, but instead of one checkbox, I have 4. It does the same thing, if all are unchecked it computes the sum. If any are checked the sum goes to zero. I could do this simply if in expressions I could use if and ifelse statements. I believe there is a way to do it in VB, but I seem to be too ignorant to get the declarations right. But in pseudo-code it basically looks like

True=checked (I believe)

If [checkbox1]=True
Then [this whole statement or expression should assign sum=0] x=0
Elseif [checkbox2]=True
Then x=0
.... etc until last box
Else x=sum([y]-[z])
Endif

Something along those lines, (and it is not necessary to set the sum=x, I just don't know how to make the whole expression = 0 any other way.)

What I mean is, it can simply take the form of Expr1=0 or anything along those lines as I did in the first program.

Thank
JOhn

 

[TonyJollans]

Hi John,

Your pseudocode is fine although, if I understand correctly, it could be a single condition ..

IF checkbox1 OR checkbox2 OR checkbox3 OR checkbox4 THEN ...

(note that IF checkbox1 is the same as IF checkbox1 = True)

What I can't really tell from your post is where the checkboxes / fields are coming from to get to your code. If they are in a recordset then the names will need qualifying with the recordset - recordset!checkbox1, etc. If they are on a form the names will need qualifying with the form (Me!checkbox1 inside form code or Forms!Formname!checkbox1 if outside form code)

Hope that helps, but if not, do come back.

Enjoy,
Tony