IIF AND??? 1

[merlynsdad]

In a calculated query field I'm converting time from 5 time zones to CST, and my code (in part) looks like this:

CTHour: IIf([site_name]="buffalo",Hour([CD_ANSWER_TM])-1,
IIf([site_name]="puertorico",Hour([CD_ANSWER_TM])-2,
IIf([site_name]="detroit",Hour([CD_ANSWER_TM])-1,
IIF([site_name])="seattle",Hour([CD_ANSWER_TM])+2))))

That part works, but because Puerto Rico does not go on daylight time, I need to put an AND in if the date is between 3/13/2011 and 11/6/2011 so that during that period of time the THEN part of the "puertorico" line is Hour([CD_ANSWER_TM])-1. I've tried something like

IIF([site_name])="puertorico" and (([date])>#03/13/2011# and <#11/06/2011#),Hour([CD-ANSWER_TM])-1

but got an invalid syntax error. Will Access even take an IIF AND command, and if so what is the syntax? Thanks.

 

[dhookom]

I don't think it makes sense to build a complex expression like this. The minimum I would do is to create a small user-defined function that performs this.

Ideally you should have a table that stores the plus or minus value rather than hard-coding values.

If you really like confusing expressions, you can try:

IIF([site_name])="puertorico" and (([date])>#03/13/2011# and [Date]<#11/06/2011#),Hour([CD-ANSWER_TM])-1

Data actually belongs in your tables, not in expressions.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I would do what Duane suggests because you are likely going to want to have more locations or the rules for time changes may change like they did here.
I would build the table tblTimeDifferences

Location      dtmStart	  dtmEnd      difference
buffalo       1/1/2010    12/31/2010  -1
detroit       1/1/2010	  12/31/2010  -1
PuertoRico    1/1/2010	  3/12/2010   -1
PuertoRico    3/13/2010	  11/6/2010   -2
PuertoRico    11/7/2010	  12/31/2010  -1
Seattle       1/1/2010    12/31/2010   2

1) put everything into a base year. I picked 2010
2) if the time period overlaps from fall into spring then create 2 records. One for the fall to the end of the year and one for the beginning of the year to the spring ending date.

Now you can add thousands of location without ever modifying code.

then

Public Function getTimeDiff(strLocation As Variant) As Variant
  Dim baseDate As Date
  'convert the date into the base year of 2010
  baseDate = DateSerial(2010, Month(Date), Day(Date))
  Dim strSql As String
  strSql = "Location = '" & strLocation & "' AND dtmStart <= #" & baseDate & "# AND dtmEnd >= #" & baseDate & "#"
  getTimeDiff = DLookup("difference", "tblTimeDifference", strSql)
  If IsNull(getTimeDiff) Then
    MsgBox "Illegal value or no data for this location:" & strLocation
  End If
End Function

so in your query
SELECT getTimeDiff([site_Name]) AS TimeDiff, DateAdd("h",[timeDiff],[cd_answer_tm]) AS CTHOUR
FROM Table1;

Your original is unlikely going to give you the right answer
. If the cd_anser_tm for seattl was 11:00 pm the answer you would get for CTHOUR in your sql is (23 + 2) = 25. I doubt that is really what you want.

 

[MajP]

BTW. I used the second function to get the values from my table. However, you could use the table in a pure Sql solution.

SELECT 
 Table1.Site_Name, 
 tblTimeDifference.difference, 
 DateAdd("h",[difference],[cd_answer_tm]) AS CTHour
FROM 
 Table1 
INNER JOIN 
 tblTimeDifference ON Table1.Site_Name = tblTimeDifference.Location
WHERE 
 (((tblTimeDifference.dtmStart)<=DateSerial(2010,Month(Date()),Day(Date()))) AND ((tblTimeDifference.dtmEnd)>=DateSerial(2010,Month(Date()),Day(Date()))));

 

[merlynsdad]

OK, thank you guys, the function works well and gives me the data I need. But now I'm trying to bring this query over to Excel as a pivot table. I've brought queries over before, but never with a function attached. When I click on the query in the "Choose Columns" box I'm getting an error message "Undefined function 'getTimeDiff' in expression. This is the function that I'm running in the query. I'm guessing Excel either can't find the function or isn't looking for it. How do I bring this query, function intact, into Excel?