ACCESS FEED: Re: [MS_AccessPros] Multiple if statements in a query

Senin, 14 Juli 2014

Re: [MS_AccessPros] Multiple if statements in a query

Arthur-

Switch([PayFrequencyTypeID] = 4, [IncomeAmount] * 12, [PayFrequencyTypeID] = 6, [IncomeAmount], [PayFrequencyTypeID] = 7, [IncomeAmount] * 2080, [PayFrequencyTypeID] = 2, [IncomeAmount] * 52, [PayFrequencyTypeID] = 3, [IncomeAmount] * 26)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 14, 2014, at 12:39 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have the following VBA code that need to translate into a query expression:

If Me.PayFrequencyTypeID = 4 Then 'Monthly
    Me.txtAnnualAmount = Me.IncomeAmount * 12
    ElseIf Me.PayFrequencyTypeID = 6 Then 'Annual
      Me.txtAnnualAmount = Me.IncomeAmount
      ElseIf Me.PayFrequencyTypeID = 7 Then 'Hourly
         Me.txtAnnualAmount = Me.IncomeAmount * 2080
         ElseIf Me.PayFrequencyTypeID = 2 Then 'Weekly
         Me.txtAnnualAmount = Me.IncomeAmount * 52
             ElseIf Me.PayFrequencyTypeID = 3 Then 'Bi-Weekly
               Me.txtAnnualAmount = Me.IncomeAmount * 26

    End If

This is located in the current event of my subform.

Is there a way to do it? This is the query that I need to update with the expression:

SELECT tblHouseholdIncome.IncomeID, tblHouseholdIncome.HouseholdID, tblHouseholdIncome.IncomeTypeID, tblHouseholdIncome.IncomeAmount, tblHouseholdIncome.PayFrequencyTypeID, tblHousehold.ApplicationID, tlkpIncomeType.IncomeTypeName, tlkpPayFrequencyType.PayFrequencyTypeName, 0 AS [Annual Income]
FROM ((tblHouseholdIncome INNER JOIN tblHousehold ON tblHouseholdIncome.HouseholdID = tblHousehold.HouseholdID) LEFT JOIN tlkpIncomeType ON tblHouseholdIncome.IncomeTypeID = tlkpIncomeType.IncomeTypeID) LEFT JOIN tlkpPayFrequencyType ON tblHouseholdIncome.PayFrequencyTypeID = tlkpPayFrequencyType.PayFrequencyTypeID;

I have 0 as a placeholder in my annual income right now.

Thank you,

Arthur Lorenzini

Sioux Falls, SD

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•