Calculated field on form

[Guest]

I have created a database that will track my companies sales and
opportunities. For opportunities i would like to include a probability of
sale field (Probability) that will be calculated by several different fields
located throughout the form. This is where i am having troubles. The
formula that will be used to calculate the Probability needs to include
CompanyPriority, ContactPriority, and ReportsPurchased. The main form lists
the Company details, including its CompanyPriority. The other data is listed
on subforms that appear on different tabs. The ContactPriority is listed on
the Contacts tab on the subform subfrmContacts and the ReportsPurchased needs
to be the count of reports listed on the Purchases tab on the subform
subfrmPurchases. Would i have to use VBA to accomplish this? And if so where
would i start?
Thank you very much for all the help you can provide.

 

[Stefan Hoffmann]

hi J,

This is where i am having troubles. The
formula that will be used to calculate the Probability needs to include
CompanyPriority, ContactPriority, and ReportsPurchased. The main form lists
the Company details, including its CompanyPriority. The other data is listed
on subforms that appear on different tabs. The ContactPriority is listed on
the Contacts tab on the subform subfrmContacts and the ReportsPurchased needs
to be the count of reports listed on the Purchases tab on the subform
subfrmPurchases. Would i have to use VBA to accomplish this? And if so where
would i start?

As you haven't posted your formula: it depends on the complexity of the
formula.

First of all, you need a query which joins all your tables to gather the
information for your formula.

Maybe you can calculate it in SQL using appropriate GROUP BY and
sub-selects.
Otherwise you use this query in VBA to loop thru it, and calculate it
step by step in a public function.


mfG
--> stefan <--

 

[Guest]

ok, i have taken the time to create the probability using SQL and by using
the aggregate functions. The problem i seem to be having is that the
Probability will be displayed on the Opportunities tab, which has the sub
form subfrmOpportunities on it. I need to have it so that the subform
remains updateable, and that is why i choose to have it a calculated field
that is not bound to the subforms query. This is why i was thinking that it
had to be done using VBA. If i can just use the SQL on that text box, how
would i join it to the other text boxes so that it will know how to calculate
the probability based on the type of report. I know that seems like a mouth
full and that it has probably complicated the problem even more so, which is
why it almost seems easier to use VBA to fill in the Probablility. Again
thank you so much for your help. The people who monitor these forums do an
excellent job and i just wanted you to know that.

 

[Stefan Hoffmann]

hi J,

I need to have it so that the subform
remains updateable, and that is why i choose to have it a calculated field
that is not bound to the subforms query. This is why i was thinking that it
had to be done using VBA.

Try to use =DLookup() in your TextBox, so you don't need to modify the
form record source.

Otherwise you have to join the original record source with your
proability calculating query (it may be updatable).

Again
thank you so much for your help. The people who monitor these forums do an
excellent job and i just wanted you to know that.

Thanks.

mfG
--> stefan <--

 

[Guest]

I am having some troubles with the DLookup() function. I haven't used it
very often
and i am no sure if i am using it correctly. The function is below:

=DLookUp("[Probability]","qryProbability","[CategoryID] = '" &
Forms!subfrmOpportunitiesA!CategoryID & "' And CompanyID] = " &
Forms!subfrmOpportunitiesA!CompanyID)

I have it set up so that the Probability field is returned where the
CategoryID on the form is equal to the CategoryID in the query as well as the
CompanyID on the form is equal to the CompanyID in the query. Is this
correct, becuase in the text box it says #Name?

Thank you for your help.

 

[Stefan Hoffmann]

hi J,

=DLookUp("[Probability]","qryProbability","[CategoryID] = '" &
Forms!subfrmOpportunitiesA!CategoryID & "' And CompanyID] = " &

It must be "' AND [CompanyID] = ". There is a bracket missing before
CompanyID.

Forms!subfrmOpportunitiesA!CompanyID)

mfG
--> stefan <--

 

[Guest]

Thanks stefan, but i still get "#Name?" in that text box, do you have any
other suggestions?

hi J,

=DLookUp("[Probability]","qryProbability","[CategoryID] = '" &
Forms!subfrmOpportunitiesA!CategoryID & "' And CompanyID] = " &

It must be "' AND [CompanyID] = ". There is a bracket missing before
CompanyID.

Forms!subfrmOpportunitiesA!CompanyID)

mfG
--> stefan <--

 

[Stefan Hoffmann]

Thanks stefan, but i still get "#Name?" in that text box, do you have any
other suggestions?

=DLookUp("[Probability]","qryProbability","[CategoryID] = '" &
Forms!subfrmOpportunitiesA!CategoryID & "' And CompanyID] = " &

It must be "' AND [CompanyID] = ". There is a bracket missing before
CompanyID.

In the German localized version of Access i need to use the ; as
separator. So try =DLookup("";"";"")


mfG
--> stefan <--

 

[Guest]

No luck, i get an error when i try to leave the cell where i enter the
formula. It deals with bad syntax.

Thanks stefan, but i still get "#Name?" in that text box, do you have any
other suggestions?

=DLookUp("[Probability]","qryProbability","[CategoryID] = '" &
Forms!subfrmOpportunitiesA!CategoryID & "' And CompanyID] = " &
It must be "' AND [CompanyID] = ". There is a bracket missing before
CompanyID.

In the German localized version of Access i need to use the ; as
separator. So try =DLookup("";"";"")


mfG
--> stefan <--