Criteria for a calculated field in a query

[migi48]

Hi!

I need help on what criteria should I put in order for my query to display records where Within(fieldnam e of the calculated field) = Yes (it's a Yes/No format)

This is my calculated field expression:

Within: IIf((Workdays([UC_Create_Date],[Date_Received])-1)<=7,-1,0)

I only want to see records with the "Yes" or "-1" value :)

[Mihail]

Exactly what you think:
Yes or -1 or True.

[migi48]

Exactly what you think:
Yes or -1 or True.

But when I try to write the code, it gives me an error of:

"This expression is typed incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression parts of the expresion variables."

How could I get the variable of my calculated field? when I try [Within], it only prompts me to input a within parameter. :( but that field is a calculated field.

Here's my SQL code for better understanding:

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT CSP.Card_Number, CSP.EMBOSS_NAME, CSP.UC_Create_Date, CSP.Date_Received, IIf((Workdays([UC_Create_Date],[Date_Received])-1)<=7,-1,0) AS Within, IIf((Workdays([UC_Create_Date],[Date_Received])-1)>7,-1,0) AS Beyond, CSP.[IS], CSP.ID, CSP.Add_Info
FROM CSP
WHERE (((CSP.UC_Create_Date) Between [StartDate] And [EndDate]) AND ((IIf((Workdays([UC_Create_Date],[Date_Received])-1)<=7,-1,0))=Yes) AND ((([CSP].[UC_Create_Date])>=[StartDate])<[EndDate]+1));

Something is wrong with my code at:

((IIf((Workdays([UC_Create_Date],[Date_Received])-1)<=7,-1,0))=Yes)

that expression by the way is the [Within] calculated field. Please help me. :( thanks!

[Mihail]

Sorry by my knowledge in SQL is almost null.
I handle that by creating a query in design view, then switching to SQL view, then COPY that string into VBA.
Of course I try to avoid that (using SQL in VBA) as more as I can.
Take a look here to understand why:

http://bytes.com/topic/access/answers/931516-discussion-using-sql-string-vba-vs-passing-parameters-query#post3702136

[Taki]

Hi,

Try the same code by putting YES between simple quotes like this:
...='Yes' ...

[NeoPa]

It seems you're over-complicating things. All you need is :

WHERE ((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7)

The result of this calcilation is a Boolean value anyway, so why compare it to True or False (or Yes or No, whatever)? That is entirely unnecessary.

You could simplify it further, of course, but maybe 7 is an important number to show in the calculation. I don't know. It's your choice.

PS. It also makes more sense to calculate the field as :

((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7) AS Within

[migi48]

Hi guys!

@Mihail - I did both methods but I still get same error :(

@Taki - I already tried adding quotes but still same error :(

@NeoPa - Good point! :) but when I tried to add

((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7)

in my SQL WHERE condition

WHERE (((CSP.UC_Create_Date) Between [StartDate] And [EndDate]) AND ((([CSP].[UC_Create_Date])>=[StartDate])<[EndDate]+1)) AND ((Workdays([UC_Create_Date],[Date_Received])-1)<=7);

I still receive the same error. :(

When I run the condition

WHERE ((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7);

alone, I receive the error

"Data type mismatch in criteria expression"

Nothing's wrong with

WHERE (((CSP.UC_Create_Date) Between [StartDate] And [EndDate]) AND ((([CSP].[UC_Create_Date])>=[StartDate])<[EndDate]+1))

alone, because it diplays the information that I need. I only receive the error when I add the new condition. How could I add this condition? :(

[NeoPa]

That would indicate that your starting code was never working anyway. How is the Workdays() function declared? What is the return type?

@Taki Your suggestion would result in a string value, which would not produce any meaningful results, but thanks for trying :-)

[migi48]

@NeoPa

Workingdays() is declared in a module. basically it computes the number of working days between a two dates. The function actually works. The return type of the function is a number which is the number of working days computed. The calculated field query is (If the working days between create_date and date received <=7 it will display true or Yes). I named that field "Within" which means it was received within 7 working days since date created.

Now I want my query to only show all records where Within(calculat ed field) is = TRUE

[Mihail]

I think that is one case that NeoPa do not agree :) because I feel that is necessary to see your database.
I think that somewhere something is misspelled.
So, migi, remove, if necessary, records from your database, in order to keep the file to a reasonable length, ZIP it and attache it to your next post.
I am almost sure that someone will find your mistake.

[NeoPa]

Migi48:
The return type of the function is a number

This is not a valid Type in VBA Ginette :-(

If you can post the header line of the function declaration that would tell me what I need to know. Actually, the code of the whole function would be nice. That way I can check through it to ensure all possible situations are adequately handled.

Alternatively, as Mihail would like the opportunity to look at (a potentially sanitised version of) your database, here are some instructions on what you need to do to prepare for this - Attach Database (or other work).

[migi48]

Hi guys! So here's the module/functions:

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

It basically computes the number of working days from a start date to an end date. It excludes weekends and holidays. :)

[migi48]

Hi this is my new SQL code

SELECT temp.Within
FROM (SELECT ((Workdays([UC_Create_Date],[Date_Received])-1)<=7) AS Within FROM tbl) AS temp
WHERE temp.Within = TRUE;

I also tried = "True", = Yes, = "Yes", = -1, = "-1", = 0, ="0"
I still receive data type mismatch. :(
My expression for

((Workdays([UC_Create_Date],[Date_Received])-1)<=7)

Produces true or false. :(

[NeoPa]

This indicates to me that one of the following is probably true :

1. [UC_Create_Date] is not a Date/Time field.
2. [Date_Received] is not a Date/Time field.
3. One of the records has no value for [UC_Create_Date] ([UC_Create_Date] Is Null).
4. One of the records has no value for [Date_Received] ([Date_Received] Is Null).

Can you check these for me and report back.

PS. Actually, even if none of those is true then the function declaration defines the first two parameters as ByRef parameters and also resets them (quite unnecessarily as it happens). You might want to try redefining your function so that it works reliably with the data it's dealing with. ByRef is not good for working with fields within a query, not as a general rule. Also, unless you're absolutely sure neither of the fields will ever be without valid Date/Time data, the parameters should be declared as Variants ant processed into Date/Times from there.

[migi48]

Hi NeoPa,

Hmmm..

1. False
2. False
3. False
4. True

Yes ofcourse some Records still don't have Date_Receive value. simply because in the process, the actual form is still not received. So these records would be NULL. However my problem is not with UC_Create_Date and Date_Received. I believe that the Data Type mismatch refers to when I add the WHERE clause "WHERE temp.Within = TRUE;". The function "Workdays" is working fine.