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

Senin, 14 Juli 2014

Re: [MS_AccessPros] Multiple if statements in a query

WOW. I will need to research this. That was cool. Never heard of Switch

Jim Wagner

On Monday, July 14, 2014 11:02 AM, "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

That was SWEEET!!!! I did not even know about the switch command. I was looking at the IIF statement and it did not seem to fit the bill. This is the final query code:

SELECT tblHouseholdIncome.IncomeID, tblHouseholdIncome.HouseholdID, tblHouseholdIncome.IncomeTypeID, tblHouseholdIncome.IncomeAmount, tblHousehold.ApplicationID, tlkpIncomeType.IncomeTypeName, tlkpPayFrequencyType.PayFrequencyTypeName, Switch([tblHouseholdIncome].[PayFrequencyTypeID]=4,[IncomeAmount]*12,[tblHouseholdIncome].[PayFrequencyTypeID]=6,[IncomeAmount],[tblHouseholdIncome].[PayFrequencyTypeID]=7,[IncomeAmount]*2080,[tblHouseholdIncome].[PayFrequencyTypeID]=2,[IncomeAmount]*52,[tblHouseholdIncome].[PayFrequencyTypeID]=3,[IncomeAmount]*26) 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;

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On Monday, July 14, 2014 12:55 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

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: Jim Wagner <luvmymelody@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•