IIf Date Query

[Gary Frazier]

Can you look at the following query criteria and provide some assistance?



This is in Access 2010 and I am adding days to a date based on a certain day of the week. Sun, Mon, Tue, Wed would add 9 days and that portion works fine. When I add the next part for Thu, Fri and Sat it gives me a -1 result for all days.

Sun, Mon, Tue, Wed needs to calculate or add 9 days to Date
Thu, Fri need to calculate or add 11 days to Date
Sat need to calculate or add 10 days to date

This is a database to calculate the expiration day after 7 working days from a specific date.

This works correctly to this point.
ExpDate: IIf([DayofWeek]=("Sun") Or [DayofWeek]=("Mon") Or [DayofWeek]=("Tue") Or [DayofWeek]=("Wed"),DateAdd("d",9,[Date]))

This is what I’ve tried. Returns a -1 result

ExpDate: IIf([DayofWeek]=("Sun") Or [DayofWeek]=("Mon") Or [DayofWeek]=("Tue") Or [DayofWeek]=("Wed"),DateAdd("d",9,[Date])) Or IIf([DayofWeek]=("Thu") Or [DayofWeek]=("Fri"),DateAdd("d",11,[Date])) Or IIf([DayofWeek]=("Sat"), DateAdd("d",10,[Date]))


I don’t think I need the IIf statements on the last two portions of the query for Thu, Fri and Sat but I can’t get the syntax correct.

Thanks,
Gary Frazier

[alansidman]

Try this:

ExpDate: IIf([DayofWeek]=("Sun") Or [DayofWeek]=("Mon") Or [DayofWeek]=("Tue") Or [DayofWeek]=("Wed"),DateAdd("d",9,[Date])),IIf([DayofWeek]=("Thu") Or [DayofWeek]=("Fri"),DateAdd("d",11,[Date])), DateAdd("d",10,[Date]))

The syntaxt for IIF is =IIF(Criteria, True Result, False Result). In a nested situation, such as yours, it would be:
=IIF(Criteria, True Result, IIF(Criteria, True Result, False Result))

[Gary Frazier]

I tried this already and again with your statement. It doesn’t like the comma between the IIf’s and if you replace the comma with Or or And it returns a -1 result.

[alansidman]

Here is a tutorial on iif statements. I think you may have misplaced something as you do need to have the commas for it to work.

http://www.techonthenet.com/access/f...vanced/iif.php

and here is what MS says about it

http://office.microsoft.com/en-us/ac...001228853.aspx

Alan

[RayMilhon]

Is there a special Reason your using IIF in the Query? Depending on the size of the DB this could get extremely slow. I would recommend the following. First in a VBA Module put in the following:

Public function ExpirationDate(byref strday as string) as date

     Select Case strday
                  Case is = "Sun", is = "Mon", Is = "Tues", Is = "Wed"
                             ExpirationDate = DateAdd("d",9,Date)
                  Case is = "Thu", is = "Fri"
                             ExpirationDate = DateAdd("d",11,Date)
                 Case is = "Sat"
                             ExpirationDate = DateAdd("d",10,Date)
     End Select
end Function

Then in your query put this in a column: Expdate:ExpirationDate([Dayofweek])

[Gary Frazier]

Is there a special Reason your using IIF in the Query? Depending on the size of the DB this could get extremely slow. I would recommend the following. First in a VBA Module put in the following:

Public function ExpirationDate(byref strday as string) as date

     Select Case strday
                  Case is = "Sun", is = "Mon", Is = "Tues", Is = "Wed"
                             ExpirationDate = DateAdd("d",9,Date)
                  Case is = "Thu", is = "Fri"
                             ExpirationDate = DateAdd("d",11,Date)
                 Case is = "Sat"
                             ExpirationDate = DateAdd("d",10,Date)
     End Select
end Function

Then in your query put this in a column: Expdate:ExpirationDate([Dayofweek])

This is the best so far!!! However I had to put the select in this way to get to pull the correct dates which is 7 working days from the entered date

Public Function ExpirationDate(ByRef strday As String) As Date
Select Case strday
Case Is = "Sun"
ExpirationDate = DateAdd("d", 0, Date)

Case Is = "Mon"
ExpirationDate = DateAdd("d", 1, Date)

Case Is = "Tue"
ExpirationDate = DateAdd("d", 2, Date)

Case Is = "Wed"
ExpirationDate = DateAdd("d", 3, Date)

Case Is = "Thu"
ExpirationDate = DateAdd("d", 6, Date)

Case Is = "Fri"
ExpirationDate = DateAdd("d", 7, Date)

Case Is = "Sat"
ExpirationDate = DateAdd("d", 7, Date)
End Select
End Function

I don't unders how "0" days added can advance the date 9 days

The "Sun" "Mon" "Tue" "Wed" Returned 19 Days, 10 days to many and all returned the same date, it should have advanced by 1 day

"Thu" "Fri" Returned 16 Days, 5 Days to many and all returned the same date, should have advanced by 1 day ofor fri
"Sat" Returned 13 Days, 3 Days to many

But this works good and for everyday of the week.

[RayMilhon]

I have a question. In the original it calls for the day of the week based on a 3 character string. I presumed that the [dayofweek] was a string in your DB but after looking at your entire post I'm wondering if it's actually a due date formatted for day of the week. If that's true you could revise it the code a bit.

Public function ExpirationDate(byref intday as integer) as date

     Select Case strday
                  Case 1 to 4
                             ExpirationDate = DateAdd("d",9,Now())
                  Case 5 to 6
                             ExpirationDate = DateAdd("d",11,Now())
                 Case is = 7
                             ExpirationDate = DateAdd("d",10,Now())
     End Select
end Function

then in your query the column would be ExpDate:ExpirationDate(Weekday([duedate])) ****Note Weekday is an Access 2010 function. I believe it is avail in 2003 but do not have a current installation of 2003 to confirm that.

[Gary Frazier]

I actually use this in the query as well
DayofWeek: IIf(Weekday([Date])=1,"Sun",IIf(Weekday([Date])=2,"Mon",IIf(Weekday([Date])=3,"Tue",IIf(Weekday([Date])=4,"Wed",IIf(Weekday([Date])=5,"Thu",IIf(Weekday([Date])=6,"Fri",IIf(Weekday([Date])=7,"Sat","nada")))))))

__________________________________________________ __________________________________________________ ____________

I have a question. In the original it calls for the day of the week based on a 3 character string. I presumed that the [dayofweek] was a string in your DB but after looking at your entire post I'm wondering if it's actually a due date formatted for day of the week. If that's true you could revise it the code a bit.

Public function ExpirationDate(byref intday as integer) as date

     Select Case strday
                  Case 1 to 4
                             ExpirationDate = DateAdd("d",9,Now())
                  Case 5 to 6
                             ExpirationDate = DateAdd("d",11,Now())
                 Case is = 7
                             ExpirationDate = DateAdd("d",10,Now())
     End Select
end Function

then in your query the column would be ExpDate:ExpirationDate(Weekday([duedate])) ****Note Weekday is an Access 2010 function. I believe it is avail in 2003 but do not have a current installation of 2003 to confirm that.

[RayMilhon]

Is your DayofWeek used anywhere Else? Also the [Date] In your DayofWeek Formula is that a field? If it is change it immediately to something more diffinitive. For example if it's a due date name it duedate Whatever that field is used for. It'll save you lots of headaches later.

I'd also like to make one other Suggestion.

Public function ExpirationDate(byref duedate as date,byref intday as integer) as date

     Select Case strday
                  Case 1 to 4
                             ExpirationDate = DateAdd("d",9,DueDate)
                  Case 5 to 6
                             ExpirationDate = DateAdd("d",11,DueDate)
                 Case is = 7
                             ExpirationDate = DateAdd("d",10,DueDate)
     End Select
end Function

Now your calling function would be ExpirationDate([Date],Weekday([date]))

Again change the [Date] to something more definitive that is in no way similar to an Access Reserved Word or Built in function

[Gary Frazier]

No, The DayofWeek is only in the query and the Date is a field in the database. This a small test database to see if I can get it working and then I will add it to the actual database I need it in. Date is the only field I have in the test database.

[RayMilhon]

I still recommend you change that field name. Date is a datatype Date() is a built in function. So again I Recommend you change it immediately. Even if it is a test database it's a bad habit to get into and If for some reason you forget to change it later it'll kill you. trust me on that. Try removing the Dayofweek from the query and test the code from my last post. I think it'll work better for you.

[Gary Frazier]

I will change the date field name to Written and adjust your select according and try it. All i should have to change is the name date in the select to written, correct?

[RayMilhon]

Yep, whereever you're using [Date] change to [Written] that would do it.

[Gary Frazier]

Yep, whereever you're using [Date] change to [Written] that would do it.

I still can't get it right, I keep geting an error in the query.

Can You build a test database and use 2 fields ?
the ID field AutoNumber
and DateJINWritten field Date/Time

Query = [ID] Formula [DateJINWritten] [DayofWeek: IIf(Weekday([Date])=1,"Sun",IIf(Weekday([Date])=2,"Mon",IIf(Weekday([Date])=3,"Tue",IIf(Weekday([Date])=4,"Wed",IIf(Weekday([Date])=5,"Thu",IIf(Weekday([Date])=6,"Fri",IIf(Weekday([Date])=7,"Sat","nada")))))))] Formula [ExpDate:????]


The result should be 7 working days from the DateJINWritten

[RayMilhon]

Here's a very simple example. Table 1 has 3 fields an Autonumber called ID date called Written and an unused field I called Weekday to show which day of the week Written is. There is a module called module1 with the Function ExpirationDate and a Query that contains 3 fields the ID and Written from the table and the expdate calculated by the function. Take a look and if you have any questions let me know.