B
Bill_De
I am having trouble with an expression in my queries. I have three tables:
tblRegistration which has number of registrations, cost, and a drop down box
to select type of payment. then tblpaytype alows the selection in
tblRegistration of Paypal, ECheck, Credit card, Check, cash. Then I have a
one record table tblpercent which I input the percentages and surcharges for
Pay Pal, ECheck and credit cards.
I created the query with the tblregistration only and draged the fields
Registration ID, Payment date, transaction number, payment type, number of
registrations, and cost down from the table. When I ran the query I was able
to select the type of payment, and enter the number of registrations and cost
and the query worked fine and allowed me to enter additional records.
I then created the following expression: AmountPaid:[numberof
registrations]*[cost]. I then ran the query and everything worked fine.
I was told that in the one record table tblpercent that I should not have a
primary key and that I should place the table in the query with no join
lines. I did this and then entered the following expression-
Fees:iif([paymenttype]=1,[amountpaid]*[tblpercent]![PPPercent],iif([paymenttype]=2,[AmountPaid]*[tblpercent]![ECPercent]))
1 is the paymentID for Pay Pal and 2 is the paymentID for e-Check PPPercent
is the percentage charged by paypal present 2.9% ECPercent is the e-check
percentage present 2.5%.
After inserting this table tblpercent into the query and writing the above
expression when I run the query I get the field names but no record lines to
enter new records. If I remove the part of the expression referencing the
tblpercent - [tblpercent]![PPPercent] and enter the figure 0.029 then run the
query the expression gives me the value I need $5.07. If I make the field
LineID the primary key and link it to tblRegistration the number field LineID
then I can make entries but I have to enter the number 1 each time in lineID.
Also if I place the tblpaytype into the query I then can't add any records to
the query.
Can anyone tell me how I can correct this so the expression works without
having to have the line ID record where I have to enter the one line from
tblpercent. Note: I have a seperate table for percentages as I will need to
use the above expressions in three different reports and three different
queries and by having the table I can enter any changes to percentages.
I just need to know how to reference the one record table percentages in the
expression and still get the query to run the expression.
Thanks
Bill_De
tblRegistration which has number of registrations, cost, and a drop down box
to select type of payment. then tblpaytype alows the selection in
tblRegistration of Paypal, ECheck, Credit card, Check, cash. Then I have a
one record table tblpercent which I input the percentages and surcharges for
Pay Pal, ECheck and credit cards.
I created the query with the tblregistration only and draged the fields
Registration ID, Payment date, transaction number, payment type, number of
registrations, and cost down from the table. When I ran the query I was able
to select the type of payment, and enter the number of registrations and cost
and the query worked fine and allowed me to enter additional records.
I then created the following expression: AmountPaid:[numberof
registrations]*[cost]. I then ran the query and everything worked fine.
I was told that in the one record table tblpercent that I should not have a
primary key and that I should place the table in the query with no join
lines. I did this and then entered the following expression-
Fees:iif([paymenttype]=1,[amountpaid]*[tblpercent]![PPPercent],iif([paymenttype]=2,[AmountPaid]*[tblpercent]![ECPercent]))
1 is the paymentID for Pay Pal and 2 is the paymentID for e-Check PPPercent
is the percentage charged by paypal present 2.9% ECPercent is the e-check
percentage present 2.5%.
After inserting this table tblpercent into the query and writing the above
expression when I run the query I get the field names but no record lines to
enter new records. If I remove the part of the expression referencing the
tblpercent - [tblpercent]![PPPercent] and enter the figure 0.029 then run the
query the expression gives me the value I need $5.07. If I make the field
LineID the primary key and link it to tblRegistration the number field LineID
then I can make entries but I have to enter the number 1 each time in lineID.
Also if I place the tblpaytype into the query I then can't add any records to
the query.
Can anyone tell me how I can correct this so the expression works without
having to have the line ID record where I have to enter the one line from
tblpercent. Note: I have a seperate table for percentages as I will need to
use the above expressions in three different reports and three different
queries and by having the table I can enter any changes to percentages.
I just need to know how to reference the one record table percentages in the
expression and still get the query to run the expression.
Thanks
Bill_De