Calculations from 1 field used in another

[Steven Craig Basham]

Hello!
I'm trying to use the calculations done in one field of a query into
another field in the query using the EXPR name I've assigned and referencing
that in the next field instead of the actual calculation.

The reason I needed to do this was because I'm running over my character
limit for the next field.

Can I do this? If not, any suggestions on a workaround... here's my two
fields for the query...
FIELD 1:
Days: DateDiff("d",[hire date],DLookUp("[payperiod]","PayPeriod","ppid =" &
[PPID:]))

FIELD 2:
Earned: IIf(([days] > 0 and [days] < 14 ),[days]/14*4.308,0)

..... this one is actually much longer because it calculates earned for up to
10 years... the [days] I was hoping to use as an alias for the
aforementioned field 1 expression.

Thanks for the advice...!
Steve

 

[John Vinson]

Hello!
I'm trying to use the calculations done in one field of a query into
another field in the query using the EXPR name I've assigned and referencing
that in the next field instead of the actual calculation.

You can't other than by basing a second Query on this Query.

The reason I needed to do this was because I'm running over my character
limit for the next field.

Can I do this? If not, any suggestions on a workaround... here's my two
fields for the query...
FIELD 1:
Days: DateDiff("d",[hire date],DLookUp("[payperiod]","PayPeriod","ppid =" &
[PPID:]))

FIELD 2:
Earned: IIf(([days] > 0 and [days] < 14 ),[days]/14*4.308,0)

.... this one is actually much longer because it calculates earned for up to
10 years... the [days] I was hoping to use as an alias for the
aforementioned field 1 expression.

Sounds like you'll need to do it a different way altogether. I can
make two suggestions:

- Write a simple VBA function to do this calculation, frex

Public Function CalcEarned(HireDate As Date, ID As Long) As Currency
Dim Days As Long
Days = DateDiff("d",[hire date], _
DLookUp("[payperiod]","PayPeriod","ppid =" & ID))
Select Case Days
Case < 14
CalcEarned = [Days] / 14 * 4.308
Case < 28
CalcEarned = <expression>
Case ...
...
Case Else
MsgBox "Error! Hiredate doesn't give valid value"
End Select
End Sub

Or, probably better in that it doesn't embed these odd 4.308 numbers
in cryptic code, use a Table solution: create a small table tblFactor
with three fields: Low, High, and Factor. This would have values like

Low: 0
High: 14
Factor: 4.308

Low: 15
High: 28
Factor: 4.***

etc up through ten years. You can then join this Table into your query
with a JOIN clause like

INNER JOIN tblFactor ON DateDiff(...) >= Low AND DateDiff(...) <= High

 

[John Spencer (MVP)]

Workaround one, write a custom function to do the calculation.

Workaround Two, use two queries. Query one, will not have Earned in it. Then
Use Query One as the basis for the second query. When you do so, you can refer
to the Days "field" easily.