Criteria for a calculated field in a query

**NeoPa** · Feb 15 '12, 02:46 AM

Originally posted by Migi48

Migi48:
The function "Workdays" is working fine.

No. It isn't. Not remotely.

It may work fine when passed date values, but it will only crash if ever a Null parameter is passed, as you've just explained is done as many records have Null values for [Date_Received].

Originally posted by Migi48

Migi48:
I believe that the Data Type mismatch refers to when I add the WHERE clause "WHERE temp.Within = TRUE;"

If you believe that then why not test it out by removing that line. See what happens.

When you fix the code, you can put it back simply as :

Code:

WHERE (Temp.Within)

Temp.Within is already a Boolean value, so comparing it to True to create a Boolean value is pointless.

**migi48** · Feb 15 '12, 04:27 AM

When I remove that WHERE clause, It successfully displays all records I want. Except it displays both TRUE and FALSE records. Records with NULL value for Date_Received displays #ERROR. I didn't see it as a problem before, since I know that there should be NULL values for Date_Received and the function just outputs #ERROR. And it still displays anyway.

Hmmmm. Anyway I belive you and following your expert advice, how could I fix my code for my workdays module?

Code:

Option Compare Database 
  
Option Explicit 
  
Public Function Workdays(ByRef startDate As Date, _ 
     ByRef endDate As Date, _ 
     Optional ByRef strHolidays As String = "Holidays" _ 
     ) As Integer 
    ' Returns the number of workdays between startDate 
    ' and endDate inclusive.  Workdays excludes weekends and 
    ' holidays. Optionally, pass this function the name of a table 
    ' or query as the third argument. If you don't the default 
    ' is "Holidays". 
    On Error GoTo Workdays_Error 
    Dim nWeekdays As Integer 
    Dim nHolidays As Integer 
    Dim strWhere As String 
  
    ' DateValue returns the date part only. 
    startDate = DateValue(startDate) 
    endDate = DateValue(endDate) 
  
    nWeekdays = Weekdays(startDate, endDate) 
    If nWeekdays = -1 Then 
        Workdays = -1 
        GoTo Workdays_Exit 
    End If 
  
    strWhere = "[Holiday] >= #" & startDate _ 
        & "# AND [Holiday] <= #" & endDate & "#" 
  
    ' Count the number of holidays. 
    nHolidays = DCount(Expr:="[Holiday]", _ 
        Domain:=strHolidays, _ 
        Criteria:=strWhere) 
  
    Workdays = nWeekdays - nHolidays 
  
Workdays_Exit: 
    Exit Function 
  
Workdays_Error: 
    Workdays = -1 
    MsgBox "Error " & Err.Number & ": " & Err.Description, _ 
        vbCritical, "Workdays" 
    Resume Workdays_Exit 
  
End Function 
  
Public Function Weekdays(ByRef startDate As Date, _ 
    ByRef endDate As Date _ 
    ) As Integer 
    ' Returns the number of weekdays in the period from startDate 
    ' to endDate inclusive. Returns -1 if an error occurs. 
    ' If your weekend days do not include Saturday and Sunday and 
    ' do not total two per week in number, this function will 
    ' require modification. 
    On Error GoTo Weekdays_Error 
  
    ' The number of weekend days per week. 
    Const ncNumberOfWeekendDays As Integer = 2 
  
    ' The number of days inclusive. 
    Dim varDays As Variant 
  
    ' The number of weekend days. 
    Dim varWeekendDays As Variant 
  
    ' Temporary storage for datetime. 
    Dim dtmX As Date 
  
    ' If the end date is earlier, swap the dates. 
    If endDate < startDate Then 
        dtmX = startDate 
        startDate = endDate 
        endDate = dtmX 
    End If 
  
    ' Calculate the number of days inclusive (+ 1 is to add back startDate). 
    varDays = DateDiff(Interval:="d", _ 
        date1:=startDate, _ 
        date2:=endDate) + 1 
  
    ' Calculate the number of weekend days. 
    varWeekendDays = (DateDiff(Interval:="ww", _ 
        date1:=startDate, _ 
        date2:=endDate) _ 
        * ncNumberOfWeekendDays) _ 
        + IIf(DatePart(Interval:="w", _ 
        Date:=startDate) = vbSunday, 1, 0) _ 
        + IIf(DatePart(Interval:="w", _ 
        Date:=endDate) = vbSaturday, 1, 0) 
  
    ' Calculate the number of weekdays. 
    Weekdays = (varDays - varWeekendDays) 
  
Weekdays_Exit: 
    Exit Function 
  
Weekdays_Error: 
    Weekdays = -1 
    MsgBox "Error " & Err.Number & ": " & Err.Description, _ 
        vbCritical, "Weekdays" 
    Resume Weekdays_Exit 
End Function

As you can see, I'm still new in VBA and been working with it for only 2 weeks. Thanks so much!

**NeoPa** · Feb 15 '12, 04:25 PM

Originally posted by Migi48

Migi48:
how could I fix my code for my workdays module?

Well, the first step would certainly need to be to decide how you want it handled when [Date_Received] is null.

Other steps (I'll include some comments I already made in post #14) :

Lose the ByRefs for the date parameters.
Declare any parameter that may cotain Null as a Variant.
Handling [Date_Received] Is Null depends on your decision re how it needs to be handled, but Nz() may prove useful. EG :
Code:
```
Nz([Date_Received], Date())
```

Your code might start something like :

Code:

Option Compare Database
Option Explicit

Public Function Workdays(ByVal datStartDate As Date, _
                         ByVal varEndDate As Variant, _
                         Optional strHolidays As String = "Holidays") As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer, nHolidays As Integer
    Dim strWhere As String
    Dim datEndDate as Date

    ' DateValue returns the date part only.
    datEndDate = Nz(varEndDate, Date)
...
End Function

**migi48** · Feb 16 '12, 01:37 AM

I followed you instruction and removed my ByRef and added a parameter for the endDate as a Variant. I also already followed your code.
Here's my actual code now:

Code:

Option Compare Database

Option Explicit

Public Function Workdays(ByVal startDate As Date, _
     ByVal varEndDate As Variant, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    Dim endDate As Date
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = Nz(varEndDate, Date)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

I also followed your suggestion on

Code:

Nz([Date_Received], Date())

Could this already handle the [Date_Received] IS NULL? Honestly, I don't really know how should I handle that since I was only concerned about records with [Date_Received] values. I thought leaving [Date_Received] NULL would not be a problem.

**NeoPa** · Feb 16 '12, 02:00 AM

Originally posted by Migi48

Migi48:
Could this already handle the [Date_Received] IS NULL? Honestly, I don't really know how should I handle that since I was only concerned about records with [Date_Received] values.

It would handle it basically if you wanted to treat any unreceived items as if they were received today.

Originally posted by Migi48

Migi48:
I thought leaving [Date_Received] NULL would not be a problem.

That may well be true (that you thought that), but you had no good reason to think that way. Finding the difference between two dates, when one of them isn't anything at all, cannot make a lot of sense under any circumstances. The numeric result would be the number of days since an arbitrary date that MS have used in order to have a reference point to store dates by. 30 December 1899 as it happens, but in real terms absolutely meaningless.

I find no other question in your latest post, which is fine, but I would have thought you might let us know how your new code worked for you. Are there any problems still? Does it work perfectly?

**migi48** · Feb 17 '12, 01:19 AM

Hi NeoPa,

I have implemented the new Code for the module. And it was able to remove the #ERROR's when running the query. I believe that it automatically inputs the date today? Im not really sure. However, when I ran some tests on adding WHERE clauses on the querry:

Code:

WHERE (Temp.Within)

It still gives an error of "Data Type Mismatch". Now I dont understand the problem. without it, it successfully displays all Within records with TRUE and FALSE values in "0"=TRUE "-1"=FALSE format.

**NeoPa** · Feb 17 '12, 10:59 AM

If your SQL is still the same then the Temp.Within should still return a boolean value (IE. True/False). Don't fall into the trap of using strings for "TRUE" and "FALSE", or even "-1" and "0", as these are absolutely not the same things at all. Your earlier SQL seemed fine though, I must say. The other thing to check (as things seem to be coming together nicely) is that you check every record result carefully. Only one, hidden in there somewhere, which isn't either True or False will cause the query to fail.

**migi48** · Feb 21 '12, 01:15 AM

THANKS NEOPA! IT WORKED! :D

"The other thing to check (as things seem to be coming together nicely) is that you check every record result carefully. Only one, hidden in there somewhere, which isn't either True or False will cause the query to fail."

The last record produced and error and I just deleted it. :D
However, How should I handle unreceived records? (records with no Date_Received) Can't I just make it NULL and not mess up my temp.Within?

**migi48** · Feb 21 '12, 01:36 AM

Hello NeoPa!

Nevermind :D I figured to handle it by simply not showing records with NULL Date_Received in the query. :D Thanks so much again for the great help! :D You rock!

**NeoPa** · Feb 21 '12, 01:43 PM

Unreceived records should be handled by the changed code ;-) There should be no need to exclude them from the query. If you post your current code I'll see if I can find the problem for you.

Criteria for a calculated field in a query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment