Working day function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used the working day function (from The Access Web) to construct a
query which calculates working days and leaves out weekends. I am now trying
to filter out working days over 3. However, when I put >3 in the criteria
box, I get an error message saying something like 'type mismatch'. How do I
get around this?
 
I suggest you post the entire SQL of your query.
(Menubar: View: SQL then copy and paste)

The WorkingDays function should return an integer value, so what you posted
seems as if it should work UNLESS your date parameters are ever null (blank)
or are not dates.
If that is the case, then you will need to handle that in your query. The
following might work to do this.

Field: IIF(IsDate(Field1) and IsDate(Field2), WorkingDays(Field1,Field2),0)
Criteria: > 3

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Here is the sql. I tried your suggestion. Not sure if I am doing something
wrong but I get an invalid parenthesis message.
 
Oops forget to post the sql

SELECT TableSupport.RequestDate, TableSupport.Project,
TableSupport.ReceivedBy AS [Received By], TableSupport.ActionDateOffered,
WorkingDays([RequestDate],[ActionDateOffered]) AS [Number of Days],
TableSupport.RequestArea, TableSupport.ActionedBy
FROM TableSupport;
 
I would think that the following would work:

SELECT TableSupport.RequestDate
, TableSupport.Project
, TableSupport.ReceivedBy AS [Received By]
, TableSupport.ActionDateOffered
, IIF(IsDate(RequestDate) and
IsDate(ActionDateOffered),WorkingDays([RequestDate],[ActionDateOffered]) ,0)
AS [Number of Days]
, TableSupport.RequestArea
, TableSupport.ActionedBy
FROM TableSupport
WHERE IIF(IsDate(RequestDate) and
IsDate(ActionDateOffered),WorkingDays([RequestDate],[ActionDateOffered]) ,0)
3
Click to expand...

If that fails, you could try some troubleshooting.
Select RequestDate, ActionDateOffered
FROM TableSupport
WHERE IsDate(RequestDate) is False or IsDate(ActionDateOffered) is False

If that returns no records then I am stumped.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

C Tate said:
Oops forget to post the sql

SELECT TableSupport.RequestDate, TableSupport.Project,
TableSupport.ReceivedBy AS [Received By], TableSupport.ActionDateOffered,
WorkingDays([RequestDate],[ActionDateOffered]) AS [Number of Days],
TableSupport.RequestArea, TableSupport.ActionedBy
FROM TableSupport;


John Spencer said:
I suggest you post the entire SQL of your query.
(Menubar: View: SQL then copy and paste)

The WorkingDays function should return an integer value, so what you
posted
seems as if it should work UNLESS your date parameters are ever null
(blank)
or are not dates.
If that is the case, then you will need to handle that in your query. The
following might work to do this.

Field: IIF(IsDate(Field1) and IsDate(Field2),
WorkingDays(Field1,Field2),0)
Criteria: > 3

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
Excellent!
John Spencer said:
I would think that the following would work:

SELECT TableSupport.RequestDate
, TableSupport.Project
, TableSupport.ReceivedBy AS [Received By]
, TableSupport.ActionDateOffered
, IIF(IsDate(RequestDate) and
IsDate(ActionDateOffered),WorkingDays([RequestDate],[ActionDateOffered])
,0) AS [Number of Days]
, TableSupport.RequestArea
, TableSupport.ActionedBy
FROM TableSupport
WHERE IIF(IsDate(RequestDate) and
IsDate(ActionDateOffered),WorkingDays([RequestDate],[ActionDateOffered])
,0)
3
Click to expand...

If that fails, you could try some troubleshooting.
Select RequestDate, ActionDateOffered
FROM TableSupport
WHERE IsDate(RequestDate) is False or IsDate(ActionDateOffered) is False

If that returns no records then I am stumped.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

C Tate said:
Oops forget to post the sql

SELECT TableSupport.RequestDate, TableSupport.Project,
TableSupport.ReceivedBy AS [Received By], TableSupport.ActionDateOffered,
WorkingDays([RequestDate],[ActionDateOffered]) AS [Number of Days],
TableSupport.RequestArea, TableSupport.ActionedBy
FROM TableSupport;


John Spencer said:
I suggest you post the entire SQL of your query.
(Menubar: View: SQL then copy and paste)

The WorkingDays function should return an integer value, so what you
posted
seems as if it should work UNLESS your date parameters are ever null
(blank)
or are not dates.
If that is the case, then you will need to handle that in your query.
The
following might work to do this.

Field: IIF(IsDate(Field1) and IsDate(Field2),
WorkingDays(Field1,Field2),0)
Criteria: > 3

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have used the working day function (from The Access Web) to construct
a
query which calculates working days and leaves out weekends. I am now
trying
to filter out working days over 3. However, when I put >3 in the
criteria
box, I get an error message saying something like 'type mismatch'. How
do
I
get around this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top