If Then Statements in Access

[kkyork]

Let me just first preface this by saying I am relatively new to access. Used it many years ago (version 2000) but have recently picked it back up again (now version 2010) so my database skills are a little rusty. Here's the situation I am trying to rectify and would love to get some perspective on it. I have a table of employee hours by date ("Employee Hours"). I also have another table which includes the start and end dates of each payroll period ("Payroll Calendar"). I would like to assign the employee hours to the corresponding payroll period in the "Employee Hours" table. If I were using excel I would have created a forumula that looks something like this...

=IF(AND(Date1>=PayrollBeginDate1,Date1<=PayrollEnd Date1),Pay Period 1,IF(AND(Date1>=PayrollBeginDate2,Date1<=PayrollEn dDate2),Pay Period 2,if(AND(Date1>=PayrollBeginDate3,Date1<=PayrollEn dDate3),Pay Period 3,IF...etc,etc.

Is there a way to do a similar function in access? I tried using the lookup but obvsiously having to key in 50,000+ entries is not really realistic.

Thanks!

[jzwp11]

Welcome to the forum!

You should be able to do what you want without using any IF...THEN statements or IIF() functions. A subquery should be able to do what you want provided your tables are structured properly

Could you provide the list of field names in the Calendar Payroll table? and Employee Hours?

[John_G]

Hi -

Using the DLookup function would probably work for you. Assuming the pay period can be identified by an integer number (which makes sense), then you could write a function something like this:

function GetPayPeriod(paydate as date) as integer
Dim FormattedPayDate as string
FormattedPayDate = "#" & format(paydate,"mm/dd/yyyy") & "#"
PeriodNumber = Dlookup("Period_ID","[Payroll Calendar]","[Start Date] <= " & FormattedPayDate & " AND [End Date] > " & FormattedPayDate)
end function

I use FormattedPayDate just to make the DLookup simpler, and to ensure that the date is in the format that DLookup requires, i.e. mm/dd/yyyy. For example, FormattedPayDate for today would be #05/14/2012# .

I have made the assumption that a paydate the same as the Start Date or End Date of a pay period will return the number of the pay period that starts with your paydate.

to use it : MyPayPeriod = GetPayPeriod(MyPayDate)

HTH

John

[kkyork]

Thanks everyone!