Conditional Calculations

[Michael Trotter]

Is it possible to have a conditional calculation write a value to a table? I
am trying to do something like:

if type=x then [members]![account_due]=(calculation)

whereby the answer is written to the account_due field.

 

[John Vinson]

Is it possible to have a conditional calculation write a value to a table? I
am trying to do something like:

if type=x then [members]![account_due]=(calculation)

whereby the answer is written to the account_due field.

What's the context? How are you doing the writing?

Sure, you can use VBA code on a Form, or you can use the IIF()
function in an Append or Update query: update the AccountDue field to

IIF([Type] = "X", <calculation>, NULL)

or

IIF([Type] = "X", <calculation>, [AccountDue])

depending on whether you want AccountDue left empty or left unchanged
for other Types.

John W. Vinson[MVP]

 

[Michael Trotter]

I can't get the VBA code on the form to work. I have tried If..the statments
and select to no avail.

Is it possible to have a conditional calculation write a value to a table? I
am trying to do something like:

if type=x then [members]![account_due]=(calculation)

whereby the answer is written to the account_due field.

What's the context? How are you doing the writing?

Sure, you can use VBA code on a Form, or you can use the IIF()
function in an Append or Update query: update the AccountDue field to

IIF([Type] = "X", <calculation>, NULL)

or

IIF([Type] = "X", <calculation>, [AccountDue])

depending on whether you want AccountDue left empty or left unchanged
for other Types.

John W. Vinson[MVP]

 

[John Vinson]

I can't get the VBA code on the form to work. I have tried If..the statments
and select to no avail.

Please post your code and explain what you're trying to do, and in
what way it "doesn't work".

John W. Vinson[MVP]

 

[Gilberto Javier Juarez Cardenas]

sfdfdwwswse

 

[Gilberto Javier Juarez Cardenas]

kkjhui

 

[Michael Trotter]

Ihave tried various methods (listed below). Essentially I have a query that
calculates Accounts_Due based on specialisation. What I need to dois write
this value to the Accounts_Due row in the members table. Clear as mud?

I have tried the following:

Private Sub Report_Activate()
'Public Function Spec() As Currency
Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = AINL
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = ENL
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = ENPL
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = RNL
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = RNPL
End Select
End Sub


'Private Sub Report_Activate()
'Select Case [accounts due query with no resignation date at all]![TYPE]

'Case "AINL"
'Account_Due = AINL
'Case "ENL"
'Account_Due = ENL
'Case "ENPL"
'Account_Due = ENPL
'Case "RNL"
'Account_Due = RNL
'Case "RNPL"
'Account_Due = RNPL
'End Select

'If [TYPE] = AINL Then Account_Due = AINL
'Else: If [TYPE] = ENL Then Account_Due = ENL
'Else: If [TYPE] = ENPL Then Account_Due = ENPL
'Else: If [TYPE] = "EN ASS" Then Account_Due = "EN ASS"
'Else: If [TYPE] = RNL Then Account_Due = RNL
'Else: If [TYPE] = RNPL Then Account_Due = RNPL
'Else: If [TYPE] = "RN ASS" Then Account_Due = "RN ASS"
'End If
'End Sub

'Private Sub Report_Open(Cancel As Integer)

'Select Case [accounts due query with no resignation date at all]![TYPE]

'Case "AINL"
'Account_Due = AINL
'Case "ENL"
'Account_Due = ENL
'Case "ENPL"
'Account_Due = ENPL
'Case "RNL"
'Account_Due = RNL
'Case "RNPL"
'Account_Due = RNPL
'End Select
'End Sub




'Private Sub Report_Open(Cancel As Integer)
'If [TYPE] = AINL Then Account_Due = AINL
' ElseIf [TYPE] = ENL Then Account_Due = ENL
' ElseIf [TYPE] = ENPL Then Account_Due = ENPL
' ElseIf [TYPE] = "EN ASS" Then Account_Due = "EN ASS"
' ElseIf [TYPE] = RNL Then Account_Due = RNL
' ElseIf [TYPE] = RNPL Then Account_Due = RNPL
' ElseIf [TYPE] = "RN ASS" Then Account_Due = "RN ASS"
'End If

'End Sub

 

[John Vinson]

Ihave tried various methods (listed below). Essentially I have a query that
calculates Accounts_Due based on specialisation. What I need to dois write
this value to the Accounts_Due row in the members table. Clear as mud?

I have tried the following:

Private Sub Report_Activate()
'Public Function Spec() As Currency
Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = AINL
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = ENL
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = ENPL
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = RNL
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = RNPL
End Select
End Sub

What are AINL, ENL, etc.? undeclared and undefined VBA variables?
Fieldnames? Form controls? What is the datatype of Account_Due and
what do you expect to end up in this field?

John W. Vinson[MVP]

 

[Michael Trotter]

AINL, ENL, etc are,at this stage, calculations done in the query. eg
AINLcalculation).
Account_Due is a field in the members table (to be currency) and I want the
value of AINL etc to be placed in the field.

M

what do you expect to end up in this field?

Ihave tried various methods (listed below). Essentially I have a query that
calculates Accounts_Due based on specialisation. What I need to dois write
this value to the Accounts_Due row in the members table. Clear as mud?

I have tried the following:

Private Sub Report_Activate()
'Public Function Spec() As Currency
Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = AINL
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = ENL
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = ENPL
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = RNL
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = RNPL
End Select
End Sub

What are AINL, ENL, etc.? undeclared and undefined VBA variables?
Fieldnames? Form controls? What is the datatype of Account_Due and
what do you expect to end up in this field?

John W. Vinson[MVP]

 

[John Vinson]

AINL, ENL, etc are,at this stage, calculations done in the query. eg
AINLcalculation).
Account_Due is a field in the members table (to be currency) and I want the
value of AINL etc to be placed in the field.

Ah!

Ok, you cannot reuse a calculated field in a further expression.

Rather than using the name AINL, you'll need to include the entire
calculation expression.

John W. Vinson[MVP]

 

[Michael Trotter]

Thanks - on the form that contains the letter that is created I now have the
code below but I get the error:
'Run time error 2465:
Microsoft Office Access can't find the field'|' referred to in your
expression.

Debug highlights the days=line. If I remark it out I get the same error but
for the next line of code.

Here is the code:

Private Sub Report_Activate()
Dim days As Integer
days = Now() - [members]![PAID TO]


Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = days / 14 * 8.689
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = days / 14 * 14
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = days / 14 * 12
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = days / 14 * 15.2
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = days / 14 * 13.529
Case [members]![TYPE] = "EN ASS"
[members]![Account_Due] = days / 14 * 2.1155
Case [members]![TYPE] = "RN ASS"
[members]![Account_Due] = days / 14 * 2.1155
End Select
End Sub

 

[Douglas J. Steele]

What are [members]![PAID TO], [members]![TYPE] and [members]![Account_Due]?
Is Members the name of a table, and you're hoping to get a value from it, or
is it the name of a form or report?

If it's a table, you need to open a recordset and retrieve the values from
the recordset.

If it's the current form or report, try Me![PAID TO], Me![TYPE] and
Me![Account_Due]

If it's another open form, try Forms!Members![PAID TO], etc.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks - on the form that contains the letter that is created I now have
the
code below but I get the error:
'Run time error 2465:
Microsoft Office Access can't find the field'|' referred to in your
expression.

Debug highlights the days=line. If I remark it out I get the same error
but
for the next line of code.

Here is the code:

Private Sub Report_Activate()
Dim days As Integer
days = Now() - [members]![PAID TO]


Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = days / 14 * 8.689
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = days / 14 * 14
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = days / 14 * 12
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = days / 14 * 15.2
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = days / 14 * 13.529
Case [members]![TYPE] = "EN ASS"
[members]![Account_Due] = days / 14 * 2.1155
Case [members]![TYPE] = "RN ASS"
[members]![Account_Due] = days / 14 * 2.1155
End Select
End Sub

Ah!

Ok, you cannot reuse a calculated field in a further expression.

Rather than using the name AINL, you'll need to include the entire
calculation expression.

John W. Vinson[MVP]

 

[Michael Trotter]

Members is a table and I am trying to write the values to it.

What are [members]![PAID TO], [members]![TYPE] and [members]![Account_Due]?
Is Members the name of a table, and you're hoping to get a value from it, or
is it the name of a form or report?

If it's a table, you need to open a recordset and retrieve the values from
the recordset.

If it's the current form or report, try Me![PAID TO], Me![TYPE] and
Me![Account_Due]

If it's another open form, try Forms!Members![PAID TO], etc.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks - on the form that contains the letter that is created I now have
the
code below but I get the error:
'Run time error 2465:
Microsoft Office Access can't find the field'|' referred to in your
expression.

Debug highlights the days=line. If I remark it out I get the same error
but
for the next line of code.

Here is the code:

Private Sub Report_Activate()
Dim days As Integer
days = Now() - [members]![PAID TO]


Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = days / 14 * 8.689
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = days / 14 * 14
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = days / 14 * 12
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = days / 14 * 15.2
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = days / 14 * 13.529
Case [members]![TYPE] = "EN ASS"
[members]![Account_Due] = days / 14 * 2.1155
Case [members]![TYPE] = "RN ASS"
[members]![Account_Due] = days / 14 * 2.1155
End Select
End Sub

On Thu, 13 Oct 2005 22:34:01 -0700, Michael Trotter

AINL, ENL, etc are,at this stage, calculations done in the query. eg
AINLcalculation).
Account_Due is a field in the members table (to be currency) and I want
the
value of AINL etc to be placed in the field.

Ah!

Ok, you cannot reuse a calculated field in a further expression.

Rather than using the name AINL, you'll need to include the entire
calculation expression.

John W. Vinson[MVP]

 

[John Vinson]

Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = days / 14 * 8.689
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = days / 14 * 14
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = days / 14 * 12
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = days / 14 * 15.2
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = days / 14 * 13.529
Case [members]![TYPE] = "EN ASS"
[members]![Account_Due] = days / 14 * 2.1155
Case [members]![TYPE] = "RN ASS"
[members]![Account_Due] = days / 14 * 2.1155
End Select
End Sub

Try a totally different tack: create a small table TypeVals with
fields TYPE (as the Primary Key, containing the text strings AINL,
ENL, etc; and Factor, containing 3.689, 14, 12, and so on.

Run an Update query:

UPDATE Members INNER JOIN Typevals ON Members.Type = TypeVals.Type
SET Members.Account_Due = [days] / 14 * TypeVals.Factor


John W. Vinson[MVP]

 

[Michael Trotter]

Thanks - I have solved it using an IIF statement within the query itself.
This may not populate the table (but then again calculated values shouldn't
be stored) but it does allow me to populate the rquired field on th report.

Select Case Spec
Case [members]![TYPE] = "AINL"
[members]![Account_Due] = days / 14 * 8.689
Case [members]![TYPE] = "ENL"
[members]![Account_Due] = days / 14 * 14
Case [members]![TYPE] = "ENPL"
[members]![Account_Due] = days / 14 * 12
Case [members]![TYPE] = "RNL"
[members]![Account_Due] = days / 14 * 15.2
Case [members]![TYPE] = "RNPL"
[members]![Account_Due] = days / 14 * 13.529
Case [members]![TYPE] = "EN ASS"
[members]![Account_Due] = days / 14 * 2.1155
Case [members]![TYPE] = "RN ASS"
[members]![Account_Due] = days / 14 * 2.1155
End Select
End Sub

Try a totally different tack: create a small table TypeVals with
fields TYPE (as the Primary Key, containing the text strings AINL,
ENL, etc; and Factor, containing 3.689, 14, 12, and so on.

Run an Update query:

UPDATE Members INNER JOIN Typevals ON Members.Type = TypeVals.Type
SET Members.Account_Due = [days] / 14 * TypeVals.Factor


John W. Vinson[MVP]