need help on an expression

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

This is what I have: I one form that is called "Company
Information" were I have my [Tax Rate] were I have my
tax% stored. Then I have a form called "Payment mgr" were
I have a field called [Tax Rate]. In that field I want it
to show one of 2 things. Depending on the option group
that I have called [Tax Status] Options: Taxable = Yes
Non-Taxable=No Depending on the Tax Status option I want
the Show if yes then [Tax Rate] = [Tax Rate] from the
form called "Company Information" if No= 0.00% to show up
in the [Tax Rate] in the form called "Payment mgr"

I need 3 things:

1. How can I show an expression that will do that on
the form "Payment mgr" that will do an if else and look
up the value from "Company Information"
2. How to look up a value from "company Information"
in a Query Statement.
3. How to do the same as one in a Query Statement.

If any one can help, and you can email me as well

Thanks.
 
See below:

Andrew said:
This is what I have: I one form that is called "Company
Information" were I have my [Tax Rate] were I have my
tax% stored. Then I have a form called "Payment mgr" were
I have a field called [Tax Rate]. In that field I want it
to show one of 2 things. Depending on the option group
that I have called [Tax Status] Options: Taxable = Yes
Non-Taxable=No Depending on the Tax Status option I want
the Show if yes then [Tax Rate] = [Tax Rate] from the
form called "Company Information" if No= 0.00% to show up
in the [Tax Rate] in the form called "Payment mgr"

I need 3 things:

1. How can I show an expression that will do that on
the form "Payment mgr" that will do an if else and look
up the value from "Company Information"
Click to expand...

If [Tax Status] has already been chosen and you are just viewing the data
then put this into the OnCurrent event of "Payment mgr".

IF [Tax Status] then
[Tax Rate] = [Forms]![Company Information]![Tax Rate]
else
[Tax Rate] = 0
end if

If you will be editing [Tax Status] then copy the code into the OnClick
event of each option group button.
2. How to look up a value from "company Information"
in a Query Statement.
Click to expand...

You cannot lookup information from a form using a query. A form is just an
instance of some data. You can refer to what is being shown on the form or
you can refer to the record set that the form is based on. However, since
the recordset on a form is usually from a table or another query, just base
future queries on that source and not the form. If you are trying to find
some specific piece of information, it is easier to use the DLookup command.
This will find one specifci piece of information based on some criteria you
set (usually a field on the current form). Check the Help for info on this.
3. How to do the same as one in a Query Statement.
Click to expand...

Same comment as above. What you could have done is for "Payment mgr", set
the recrodset to a query. On this query you could add an expression similar
to the code for question 1 except using the IIF function. Then the form
could show [Tax Rate] based on this expression.

TaxRate: IIF([Tax Status]=Yes,[Company Information].[Tax Rate],0)

Repalce the fields with the actual names of the field in the table not the
names from the form.
If any one can help, and you can email me as well

Thanks.
Click to expand...

Respond to this posting if you have any further questions.

Kelvin
 
I can get it to work if I set the value to .0825 but if I
use [Foms]![Company Information]![Tax Rate] I get a run
time error cannot find form 'Company information'

[Tax Rate] = [Forms]![Company Information]![Tax Rate]

and the other for the Query cannot find 'company
Information'

TaxRate: IIF([Tax Status]=Yes,[Company Information].[Tax
Rate],0)
-----Original Message-----
See below:

Andrew said:
This is what I have: I one form that is called "Company
Information" were I have my [Tax Rate] were I have my
tax% stored. Then I have a form called "Payment mgr" were
I have a field called [Tax Rate]. In that field I want it
to show one of 2 things. Depending on the option group
that I have called [Tax Status] Options: Taxable = Yes
Non-Taxable=No Depending on the Tax Status option I want
the Show if yes then [Tax Rate] = [Tax Rate] from the
form called "Company Information" if No= 0.00% to show up
in the [Tax Rate] in the form called "Payment mgr"

I need 3 things:

1. How can I show an expression that will do that on
the form "Payment mgr" that will do an if else and look
up the value from "Company Information"
Click to expand...

If [Tax Status] has already been chosen and you are just viewing the data
then put this into the OnCurrent event of "Payment mgr".

IF [Tax Status] then
[Tax Rate] = [Forms]![Company Information]![Tax Rate]
else
[Tax Rate] = 0
end if

If you will be editing [Tax Status] then copy the code into the OnClick
event of each option group button.
2. How to look up a value from "company Information"
in a Query Statement.
Click to expand...

You cannot lookup information from a form using a query. A form is just an
instance of some data. You can refer to what is being shown on the form or
you can refer to the record set that the form is based on. However, since
the recordset on a form is usually from a table or another query, just base
future queries on that source and not the form. If you are trying to find
some specific piece of information, it is easier to use the DLookup command.
This will find one specifci piece of information based on some criteria you
set (usually a field on the current form). Check the Help for info on this.
3. How to do the same as one in a Query Statement.
Click to expand...

Same comment as above. What you could have done is for "Payment mgr", set
the recrodset to a query. On this query you could add an expression similar
to the code for question 1 except using the IIF function. Then the form
could show [Tax Rate] based on this expression.

TaxRate: IIF([Tax Status]=Yes,[Company Information].[Tax Rate],0)

Repalce the fields with the actual names of the field in the table not the
names from the form.
If any one can help, and you can email me as well

Thanks.
Click to expand...

Respond to this posting if you have any further questions.

Kelvin


.
Click to expand...
 
Andrew said:
I can get it to work if I set the value to .0825 but if I
use [Foms]![Company Information]![Tax Rate] I get a run
time error cannot find form 'Company information'

[Tax Rate] = [Forms]![Company Information]![Tax Rate]
Click to expand...

This needs to be the name of the form. I did not know what your form is
actually called. Also the form has to be open for this to work.
and the other for the Query cannot find 'company
Information'

TaxRate: IIF([Tax Status]=Yes,[Company Information].[Tax
Rate],0)
Click to expand...

[Company Information] this time is the name of the table. Did you include
this table as part of the query?

Kelvin
 
On the first one, the form will not be open all the time
I was using a DLookUp() to get the info before I decided
to use the option buttons Taxable and Non-Taxable, I
have it working right as this moment I have a hidden
field that does the DLookUp() and then I just pointed the
[Tax Rate] = [CIT]. that is working now, on the second
one I don't have the table included in the Query, because
I really can't create a relationship with it.
The 'Company Information' Form and Table only inters data
once for the purposes of the business that is going to
own the database, but the other fields that need that
[Tax Rate] information from 'Company Information' are
going to be many different records. I am going to try to
create a relationship so I can include in the query but I
don't think is it going to work. If you can think of a
better way to do the first one on the if then statement
that would help, and on the second one I will keep
working at it..

-----Original Message-----
Andrew said:
I can get it to work if I set the value to .0825 but if I
use [Foms]![Company Information]![Tax Rate] I get a run
time error cannot find form 'Company information'

[Tax Rate] = [Forms]![Company Information]![Tax Rate]
Click to expand...

This needs to be the name of the form. I did not know what your form is
actually called. Also the form has to be open for this to work.
and the other for the Query cannot find 'company
Information'

TaxRate: IIF([Tax Status]=Yes,[Company Information]. [Tax
Rate],0)
Click to expand...

[Company Information] this time is the name of the table. Did you include
this table as part of the query?

Kelvin


.
Click to expand...
 
I got it to work, I had to add a extra field that looked
up the Company Information ID and transfer it to the
table that I needed it on, and I was able to get the [tax
rate] and the relationship in the query to work, thanks
for the help, and if you think of any way to get the data
on the form with the if then statement with out me having
having the form open I could use that, but they are
working right as of now.. and thanks again keven...
 
Back
Top