Some of you may recall a post from me (ID# 28499 on 10/5) regarding an expression, part of which invokes a Workdays function (based on one in Peter Wayne's book) that internally runs a query to count holidays between two dates. The problem was that the expression worked fine as a report filter but would not work as a query. None of the help I received cleared the problem, and I feared that A5 couldn't handle running a query containing a function that also runs a query (a query within a query). Most of the error messages were unhelpful (something like "invalid query") in that they pointed to something being wrong with the structure of the query expression when in fact structurally there was nothing wrong. Running debug was similarly unhelpful.
To make a long story short(er), I finally got one variant of the query to work, whereas another almost identical one would not. When I greatly simplified the non-working query the error message came back with something like "invalid date constant." It turns out that what tripped things up were a few blank dates in the (table) field values used by the Workdays function. THIS WOULD APPEAR TO BE A BUG, unless there is some good reason blank field values should be a problem for an expression used in a query but not be a problem when the same expression is used in a report filter.
Anyway, at least in my application I can get around the problem by putting a few lines in the Workdays function so it won't trip when it encounters a blank date. My way of substituting a date that does no harm works for my application but may not for others. Suggestions for a better workaround are welcome.
Ray Lyons
function Workdays as N(First as D,Last as D,Name_of_Hol_Tbl as C)
'option strict
dim dow1 as n
dim dow2 as n
dim weekends as n
dim nholidays as n
dim offset as n
dim hol as p 'pointer to the holiday table
dim ip as p
dim query.description as c
dim query.filter as c
dim query.options as c
dim query.order as c
dim fld as p 'pointer to the field in holiday
dim field_name as c
dim indx as p
'**When dates are fields in a table, AND when this function runs within a query,
'**the blank date fields will result in errors that otherwise do not occur.
'**So, the next 6 lines (or alternatives) may be needed.
If Last={}
Last={01/01/0001} '**Use any date that won't cause a problem
End If
If First={}
First={01/01/0001}
End If
on error goto no_holiday_table
hol=table.open(Name_of_Hol_tbl)
on error goto 0
fld=hol.field_get(1) '**Pointer to 1st field in the file
if fld.type_get()"D" then '**Make sure the 1st field is a date
workdays=-1
exit function
end if
field_name=fld.name_get() '**name of the 1st field-will be used below
'**Each offset below either adds or subtracts weekend days to the date
'**in the date field. Then all you need is the # weekends in between
dow1=dow(first)
offset=case(dow1=7,2,dow1=1,1,.t.,0)
first=first+offset
dow2=dow(last)
offset=case(dow2=7,-1,dow2=1,-2,.t.,0)
last=last+offset
'**Now just calculate # of weekends and add a fudge factor in the event,
'**that first - last goes, say, from a Thurs to a Monday
weekends=int((last-first)/7)+iif(dow(first)>dow(last),1,0)
'**Now check for holidays
query.description="holidays between dates"
query.order=""
query.options=""
query.filter="between("+field_name+",first,last)"
ip=hol.query_create()
nholidays=ip.records_get()
'**Finally, just calculate the workdays
workdays=last-first+1-nholidays-2*weekends
indx=hol.index_get("holidays between dates")
indx.drop()
hol.close()
exit function
no_holiday_table:
on error goto 0
workdays=-1
exit function
end function
To make a long story short(er), I finally got one variant of the query to work, whereas another almost identical one would not. When I greatly simplified the non-working query the error message came back with something like "invalid date constant." It turns out that what tripped things up were a few blank dates in the (table) field values used by the Workdays function. THIS WOULD APPEAR TO BE A BUG, unless there is some good reason blank field values should be a problem for an expression used in a query but not be a problem when the same expression is used in a report filter.
Anyway, at least in my application I can get around the problem by putting a few lines in the Workdays function so it won't trip when it encounters a blank date. My way of substituting a date that does no harm works for my application but may not for others. Suggestions for a better workaround are welcome.
Ray Lyons
function Workdays as N(First as D,Last as D,Name_of_Hol_Tbl as C)
'option strict
dim dow1 as n
dim dow2 as n
dim weekends as n
dim nholidays as n
dim offset as n
dim hol as p 'pointer to the holiday table
dim ip as p
dim query.description as c
dim query.filter as c
dim query.options as c
dim query.order as c
dim fld as p 'pointer to the field in holiday
dim field_name as c
dim indx as p
'**When dates are fields in a table, AND when this function runs within a query,
'**the blank date fields will result in errors that otherwise do not occur.
'**So, the next 6 lines (or alternatives) may be needed.
If Last={}
Last={01/01/0001} '**Use any date that won't cause a problem
End If
If First={}
First={01/01/0001}
End If
on error goto no_holiday_table
hol=table.open(Name_of_Hol_tbl)
on error goto 0
fld=hol.field_get(1) '**Pointer to 1st field in the file
if fld.type_get()"D" then '**Make sure the 1st field is a date
workdays=-1
exit function
end if
field_name=fld.name_get() '**name of the 1st field-will be used below
'**Each offset below either adds or subtracts weekend days to the date
'**in the date field. Then all you need is the # weekends in between
dow1=dow(first)
offset=case(dow1=7,2,dow1=1,1,.t.,0)
first=first+offset
dow2=dow(last)
offset=case(dow2=7,-1,dow2=1,-2,.t.,0)
last=last+offset
'**Now just calculate # of weekends and add a fudge factor in the event,
'**that first - last goes, say, from a Thurs to a Monday
weekends=int((last-first)/7)+iif(dow(first)>dow(last),1,0)
'**Now check for holidays
query.description="holidays between dates"
query.order=""
query.options=""
query.filter="between("+field_name+",first,last)"
ip=hol.query_create()
nholidays=ip.records_get()
'**Finally, just calculate the workdays
workdays=last-first+1-nholidays-2*weekends
indx=hol.index_get("holidays between dates")
indx.drop()
hol.close()
exit function
no_holiday_table:
on error goto 0
workdays=-1
exit function
end function
Comment