Combining values from different forms

[DavidWrightSr]

I have a subform,(named 'Fldetail'), in which I would like to calculate a sales tax based on the sales price * a tax rate stored in a separate Form. This form is called 'Values' and contains the 'Tax Rate' in a row called that.

The amount to use in the calculation is in the same row as the result to be calculated and is called 'Amount'.
To further complicate things, the final calculation is dependent on a type field labeled 'S' (for Sales Item) in the same row and a field from the main form called 'Tax Code', (the main form is named 'Florist'), so the final calculation is:
If 'S' and 'Tax Code' are true then the result is ('Tax Rate' * 'Amount') + 'Amount'.

My problem is that I have not been able to determine the proper syntax to locate the 'Tax Rate' in the other form and combine it with the values from the row with 'Amount' and 'S' to produce the desired result or the appropriate 'IF' conditional.



I have tried numerous times to solve this problem, but have failed to do so. The combination of brackets and ! signs completely baffle me.

Any help would be greatly appreciated.

David Wright Sr.

[pbaldy]

Does this help?

http://access.mvps.org/access/forms/frm0031.htm

[andy49]

I would have thought use the builder to create:

Iif(s=true and tax code = true, tax rate * amount) + amount.

The builder will help you pull in require info and keep syntax errors to a minimum.


Sent from my iPhone using Tapatalk

[DavidWrightSr]

I would have thought use the builder to create:

Iif(s=true and tax code = true, tax rate * amount) + amount.

The builder will help you pull in require info and keep syntax errors to a minimum.


Sent from my iPhone using Tapatalk

The code says exactly what I want except that my problem is that 'S' comes from the subform, 'Tax Code' from the main form and 'Tax Rate' from a different form called Values. I can't figure out the syntax for putting these all together. I suppose that I could simplify somewhat by including 'Tax Rate' in the query for the main form.

Thanks

David
P.S. I would like to put the results in a VBA procedure to execute after update of the field.
P.P.S. (to baldy) Thanks for the link, but I had the same kind of problems interpreting how these were to be used.

[pbaldy]

P.P.S. (to baldy) Thanks for the link, but I had the same kind of problems interpreting how these were to be used.

hard to say where you're going wrong without seeing what you're trying.

[ssanfu]

I have a subform,(named 'Fldetail'), in which I would like to calculate a sales tax based on the sales price * a tax rate stored in a separate Form. This form is called 'Values'

Just for clarity, forms do not "store" data. Tables store data; forms are a way to view the data stored in tables.
"Values" is a reserved word in Access and shouldn't be used as an object name. A better name would be "frmValues".

Is it possible for the tax rate to change?
If yes, I would save the tax rate in the record. Otherwise, next year, if you look back at a record, the calculated value will be different if the tax rate was changed.

Where are you doing the calculation?
In a query? (design view or SQL view?)
In the control source of a control?
In VBA code?

Are "S" and "TaxCode" boolean (T/F) fields?


If you are in the subform, "S" and "Amount" are available in the sub form, "TaxCode" is on the main form (ie parent form) and the "Tax rate" is on a different form.
NOTE: The form "Values" MUST be open for the calculation to work correctly.

Iif(s=true and tax code = true, tax rate * amount) + amount

The syntax for the immediate if function (IIF() ) is "IIF(condition, Value if True, Value If False)"
You are missing the FALSE part.

The condition clause is:

S=TRUE and Forms!Florist.[tax code] = TRUE

The TRUE clause is:

(Forms!Values.[tax rate] * amount) + amount

The FALSE clause is: 0

Putting it together:

IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)

If you were in the query design view, you might use

ExtendedAmount: IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)

In SQL view, you might use:

 IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0) AS ExtendedAmount

In the Control Source property of a control:

= IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)

If the form "Values" is not open, you might try using the DLOOKUP() function to get the tax rate.

[DavidWrightSr]

Many Thanks. I'll see what I can do with all of this. It looks promising.

David Wright

[DavidWrightSr]

[From: ssanfu]Just for clarity, forms do not "store" data. Tables store data; forms are a way to view the data stored in tables.
"Values" is a reserved word in Access and shouldn't be used as an object name. A better name would be "frmValues".

I have changed all of my objects to reflect whether they are 'frm's , 'rpt's, 'tbl's etc.


Is it possible for the tax rate to change?
If yes, I would save the tax rate in the record. Otherwise, next year, if you look back at a record, the calculated value will be different if the tax rate was changed.

Yes, I will be doing that.

Where are you doing the calculation?
In a query? (design view or SQL view?)
In the control source of a control?
In VBA code?

I am trying to modify the SQL code in the subform design.

Are "S" and "TaxCode" boolean (T/F) fields?

'S' is a text field one of the designators as to the type of record, it is, e.g'Sales', 'Payment, 'Balance Forward' etc., 'TaxCode' is boolean

If you are in the subform, "S" and "Amount" are available in the sub form, "TaxCode" is on the main form (ie parent form) and the "Tax rate" is on a different form.
NOTE: The form "Values" MUST be open for the calculation to work correctly.

I don't understand what you mean here by 'Open'? As I see it, the only 'open' objects are the ones on the main form and the subform. Or, is that accomplished by 'Forms!Values.[tax rate]' in the code?

Iif(s=true and tax code = true, tax rate * amount) + amount

The syntax for the immediate if function (IIF() ) is "IIF(condition, Value if True, Value If False)"
You are missing the FALSE part.

The condition clause is:

S=TRUE and Forms!Florist.[tax code] = TRUE

The TRUE clause is:

(Forms!Values.[tax rate] * amount) + amount

The FALSE clause is: 0

Putting it together:

IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)

If you were in the query design view, you might use

ExtendedAmount: IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)

In SQL view, you might use:

 IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0) AS ExtendedAmount

I'll try this one. One problem I haven't mentioned is that I am using Access 2000 and I don't believe that 'Iif' works there, so I am trying to create a VBA procedure to do this. You ask, "why am I using 2000"? The answer is money. If all else fails, I have to dig up the cash to purchase a later version.

In the Control Source property of a control:

= IIF(S=TRUE and Forms!Florist.[tax code] = TRUE, (Forms!Values.[tax rate] * Amount) + Amount, 0)

If the form "Values" is not open, you might try using the DLOOKUP() function to get the tax rate

[pbaldy]

I'll let Steve address your responses to his post, but IIf() definitely works in A2000.

[DavidWrightSr]

Thanks. I hadn't tried it myself, but was going by a page I was looking at that said support only went back to version 2003. "Everything you read on the internet is true".

Thanks very much.

David

[pbaldy]

No problem. Did I mention I was abducted by aliens?

[DavidWrightSr]

[To: ssanfu] (I am assuming that this is Steve who pbaldy referred to.)

I tried the following: (I left off the 'S' text to make it simpler)

=IIf(Forms!frmFlorist.[Tax Code]=True,(Forms!frmValues.[Tax Rate]*[Amount]+[Amount]))

The result was still a #NAME? error.

Still don't understand about having frmValues Open.

Should the [Tax Code] and [Tax Rate] come from the field's control source or from the name?

Sorry to be such a pain.

David

[andy49]

Sorry to cut in here but you've no false for your iif statement.

What do you want in the textbox if there is [tax code]= false?

Try putting "false" or "" between the last two )) to see if this helps.




Sent from my iPhone using Tapatalk

[ssanfu]

I don't understand what you mean here by 'Open'? As I see it, the only 'open' objects are the ones on the main form and the subform.

Still don't understand about having frmValues Open.

A form is OPEN if it is loaded in memory; it can be visible or hidden. If a form is not open, you cannot get values from it.

Or, is that accomplished by 'Forms!Values.[tax rate]' in the code?

Yes, but ONLY IF the form is open.
You use "Forms!Values.[tax rate]" to refer to a control named "[tax rate]" on a form named "Values" to get a value.

Should the [Tax Code] and [Tax Rate] come from the field's control source or from the name?

Since you are going to store the tax rate in the record, you will just use the field name [Tax Rate]. (much easier)
And since the [Tax Code] is on the main form, you will still need to refer to the control like this: Forms!Florist.[tax code] ...


You say "S" is a text type field, so try:
If you were in the query design view, try:

ExtendedAmount: IIF([S]="Sales" and Forms!Florist.[tax code] = TRUE, ([tax rate] * Amount) + Amount, 0)

In SQL view, try:

 IIF([S] = "Sales" and Forms!Florist.[tax code] = TRUE, ([tax rate] * Amount) + Amount, 0) AS ExtendedAmount

I am trying to modify the SQL code in the subform design.

If you still have problems with the SQL, post the SQL of the sub form.






Some suggestions:
------------------------------
Use only letters and numbers (exception is the underscore) for object names. (especially field names)
-> Do not use spaces, punctuation or special characters in object names!!! (the name [tax rate] is BAD because it has a space)
- Do not begin object names with a number.
- Better to not use look up FIELDS, multi-value fields or calculated fields in tables.

[DavidWrightSr]

I have still not resolved this problem, but it is necessary for me to revisit it.
Let me make it clearer what I am trying to do.

1. I have a subform showing in datasheet format.

In addition to any existing records, the last record is a blank one which can be filled in to create a new record It contains the following fields.

1. Acctno [the key field which ties the subform to ID on the main form]
2. Date [filled in automatically to contain the current date.]
3. Type [a Field which determines what type of record is contained.]

This can be ‘S’ for Sales, ‘P’ for Payment, ‘B’ for Balance Forward.”S” is automatically in as this is the most common action.]

1. Amount [The dollar amount of the transaction.]
2. Tax. [The calculated tax for any “Sales”]. Empty for other Types.

f) Description [A descriptive text for the transaction, e., “Flower Arrangement”, “Payment” etc.

2) The complete calculation for Tax depends on
a) Having an ‘S’ in Type
b) A taxcode in the main form. The default is ‘True’ for tax being applied. ‘False’ for No Tax.
c) An amount being entered.
d) A tax rate from a Form called ‘Values.[tax rate].

The full calculation that I envision is:
The Tax result = [tax rate] * Amount provided that [Taxcode]=false and Type=”S”. (This obviously a pseudo code since I have not been able to create valid code to solve the problem.

I have tried a very simple version, just to check what I think should work by trying by using the value .07 as tax rate, and eliminating the Type and Tax Code provisions to calculate [tax] as .07*[Amount]. I tried this on both entering and exiting the tax field, but nothing happens. =[tax]=.07 * [Amount] produces no results.