Query calculation

[mkelly]

I have a query and in it are three check boxes $25, $10 and $5.

Is it possible to build a calculation into the query that if check box $25 is yes then $25.00-if check box $10 is yes than $10.00-if check box $5 is yes then $5.00.

If this is possable could you please tell me how I could do it.

any help appriciated.

thanks

 

[dcx693]

if check box $25 is yes then $25.00

If check box $25, then what exactly takes on the value of $25.00? The calculated field? What if you have multiple check boxes checked?

 

[mkelly]

only one of the check boxes will be checked so one will be yes and the other two will be no. I need it to take the check box marked yes and calculate the or enter the right dollar amount into a new field on the query

 

[dcx693]

I wouldn't keep 3 fields in your table just to store what basically amounts to one piece of information. Why not create a form with an option group so users can choose one of the 3 pre-selected values and store that selection in your table?

 

[mkelly]

It is an existing database my wife uses at her company and she asked me if I could come up with monthly billing reports. To much data to change it now.

I just need a way that if one of the check boxes =yes to place that amount into a new field.

 

[Travis]

So then it looks like you have no say in the design of the structure.

Then you have two options.

1. IIF (and Embedded IIF)
2. Create A Public Function


IIF (Assumes only one can be true)
Query Field
=IIF([Chk25]=True,25,IIF([Chk15]=True,15,IIF([Chk10]=True,10,0)))

(Assumes any could be true)
=IIF([Chk25]=True,25,0)+IIF([Chk15]=True,15,0)+IIF([Chk10]=True,10,0)


Public Function

'Place in a Public Module
Public Function GetTotal(fChk25,fChk15,fChk10) as Double

'Assumes only one can be true

If fChk25=True Then
GetTotal=25
Else If fChk15=True Then
GetTotal=15
Else If fChk10=True then
GetTotal=10
Else
GetTotal=0
End If

'Assumes any Could be true

If fChk25=True Then
GetTotal=GetTotal+25
End If

If fChk15=True Then
GetTotal=GetTotal+15
End If

If fChk10=True then
GetTotal=GetTotal+10
End If

End Function

Place this in the QBE Grid
=GetTotal([Chk25],[Chk15],[Chk10])

 

[mkelly]

Thank you, thank you

Works great much appriciated.....