By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Criteria for a calculated field in a query

P: 28
Hi!

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

This is my calculated field expression:

Expand|Select|Wrap|Line Numbers
  1. Within: IIf((Workdays([UC_Create_Date],[Date_Received])-1)<=7,-1,0)
I only want to see records with the "Yes" or "-1" value :)
Feb 9 '12 #1

✓ answered by NeoPa

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) :
  1. Lose the ByRefs for the date parameters.
  2. Declare any parameter that may cotain Null as a Variant.
  3. Handling [Date_Received] Is Null depends on your decision re how it needs to be handled, but Nz() may prove useful. EG :
    Expand|Select|Wrap|Line Numbers
    1. Nz([Date_Received], Date())

Your code might start something like :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function Workdays(ByVal datStartDate As Date, _
  5.                          ByVal varEndDate As Variant, _
  6.                          Optional strHolidays As String = "Holidays") As Integer
  7.     ' Returns the number of workdays between startDate
  8.     ' and endDate inclusive.  Workdays excludes weekends and
  9.     ' holidays. Optionally, pass this function the name of a table
  10.     ' or query as the third argument. If you don't the default
  11.     ' is "Holidays".
  12.     On Error GoTo Workdays_Error
  13.     Dim nWeekdays As Integer, nHolidays As Integer
  14.     Dim strWhere As String
  15.     Dim datEndDate as Date
  16.  
  17.     ' DateValue returns the date part only.
  18.     datEndDate = Nz(varEndDate, Date)
  19. ...
  20. End Function

Share this Question
Share on Google+
24 Replies


100+
P: 759
Exactly what you think:
Yes or -1 or True.
Feb 9 '12 #2

P: 28
@Mihail
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:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS StartDate DateTime, EndDate DateTime;
  2. 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
  3. FROM CSP
  4. 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));
  5.  
Something is wrong with my code at:

Expand|Select|Wrap|Line Numbers
  1. ((IIf((Workdays([UC_Create_Date],[Date_Received])-1)<=7,-1,0))=Yes)
  2.  
that expression by the way is the [Within] calculated field. Please help me. :( thanks!
Feb 9 '12 #3

100+
P: 759
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/answer...ry#post3702136
Feb 9 '12 #4

P: 1
Hi,

Try the same code by putting YES between simple quotes like this:
...='Yes' ...
Feb 9 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
It seems you're over-complicating things. All you need is :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. ((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7) AS Within
Feb 9 '12 #6

P: 28
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

Expand|Select|Wrap|Line Numbers
  1. ((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7)
  2.  
in my SQL WHERE condition

Expand|Select|Wrap|Line Numbers
  1. 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);
  2.  
I still receive the same error. :(

When I run the condition

Expand|Select|Wrap|Line Numbers
  1. WHERE ((Workdays([UC_Create_Date],[Date_Received]) - 1) <= 7);
  2.  
alone, I receive the error

"Data type mismatch in criteria expression"

Nothing's wrong with

Expand|Select|Wrap|Line Numbers
  1. WHERE (((CSP.UC_Create_Date) Between [StartDate] And [EndDate]) AND ((([CSP].[UC_Create_Date])>=[StartDate])<[EndDate]+1))
  2.  
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? :(
Feb 10 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
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 :-)
Feb 10 '12 #8

P: 28
@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(calculated field) is = TRUE
Feb 10 '12 #9

100+
P: 759
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.
Feb 10 '12 #10

NeoPa
Expert Mod 15k+
P: 31,494
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).
Feb 10 '12 #11

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Option Explicit
  4.  
  5. Public Function Workdays(ByRef startDate As Date, _
  6.      ByRef endDate As Date, _
  7.      Optional ByRef strHolidays As String = "Holidays" _
  8.      ) As Integer
  9.     ' Returns the number of workdays between startDate
  10.     ' and endDate inclusive.  Workdays excludes weekends and
  11.     ' holidays. Optionally, pass this function the name of a table
  12.     ' or query as the third argument. If you don't the default
  13.     ' is "Holidays".
  14.     On Error GoTo Workdays_Error
  15.     Dim nWeekdays As Integer
  16.     Dim nHolidays As Integer
  17.     Dim strWhere As String
  18.  
  19.     ' DateValue returns the date part only.
  20.     startDate = DateValue(startDate)
  21.     endDate = DateValue(endDate)
  22.  
  23.     nWeekdays = Weekdays(startDate, endDate)
  24.     If nWeekdays = -1 Then
  25.         Workdays = -1
  26.         GoTo Workdays_Exit
  27.     End If
  28.  
  29.     strWhere = "[Holiday] >= #" & startDate _
  30.         & "# AND [Holiday] <= #" & endDate & "#"
  31.  
  32.     ' Count the number of holidays.
  33.     nHolidays = DCount(Expr:="[Holiday]", _
  34.         Domain:=strHolidays, _
  35.         Criteria:=strWhere)
  36.  
  37.     Workdays = nWeekdays - nHolidays
  38.  
  39. Workdays_Exit:
  40.     Exit Function
  41.  
  42. Workdays_Error:
  43.     Workdays = -1
  44.     MsgBox "Error " & Err.Number & ": " & Err.Description, _
  45.         vbCritical, "Workdays"
  46.     Resume Workdays_Exit
  47.  
  48. End Function
  49.  
  50. Public Function Weekdays(ByRef startDate As Date, _
  51.     ByRef endDate As Date _
  52.     ) As Integer
  53.     ' Returns the number of weekdays in the period from startDate
  54.     ' to endDate inclusive. Returns -1 if an error occurs.
  55.     ' If your weekend days do not include Saturday and Sunday and
  56.     ' do not total two per week in number, this function will
  57.     ' require modification.
  58.     On Error GoTo Weekdays_Error
  59.  
  60.     ' The number of weekend days per week.
  61.     Const ncNumberOfWeekendDays As Integer = 2
  62.  
  63.     ' The number of days inclusive.
  64.     Dim varDays As Variant
  65.  
  66.     ' The number of weekend days.
  67.     Dim varWeekendDays As Variant
  68.  
  69.     ' Temporary storage for datetime.
  70.     Dim dtmX As Date
  71.  
  72.     ' If the end date is earlier, swap the dates.
  73.     If endDate < startDate Then
  74.         dtmX = startDate
  75.         startDate = endDate
  76.         endDate = dtmX
  77.     End If
  78.  
  79.     ' Calculate the number of days inclusive (+ 1 is to add back startDate).
  80.     varDays = DateDiff(Interval:="d", _
  81.         date1:=startDate, _
  82.         date2:=endDate) + 1
  83.  
  84.     ' Calculate the number of weekend days.
  85.     varWeekendDays = (DateDiff(Interval:="ww", _
  86.         date1:=startDate, _
  87.         date2:=endDate) _
  88.         * ncNumberOfWeekendDays) _
  89.         + IIf(DatePart(Interval:="w", _
  90.         Date:=startDate) = vbSunday, 1, 0) _
  91.         + IIf(DatePart(Interval:="w", _
  92.         Date:=endDate) = vbSaturday, 1, 0)
  93.  
  94.     ' Calculate the number of weekdays.
  95.     Weekdays = (varDays - varWeekendDays)
  96.  
  97. Weekdays_Exit:
  98.     Exit Function
  99.  
  100. Weekdays_Error:
  101.     Weekdays = -1
  102.     MsgBox "Error " & Err.Number & ": " & Err.Description, _
  103.         vbCritical, "Weekdays"
  104.     Resume Weekdays_Exit
  105. End Function
  106.  
  107.  
It basically computes the number of working days from a start date to an end date. It excludes weekends and holidays. :)
Feb 14 '12 #12

P: 28
Hi this is my new SQL code

Expand|Select|Wrap|Line Numbers
  1. SELECT temp.Within
  2. FROM (SELECT ((Workdays([UC_Create_Date],[Date_Received])-1)<=7) AS Within FROM tbl) AS temp
  3. WHERE temp.Within = TRUE;
  4.  
I also tried = "True", = Yes, = "Yes", = -1, = "-1", = 0, ="0"
I still receive data type mismatch. :(
My expression for
Expand|Select|Wrap|Line Numbers
  1. ((Workdays([UC_Create_Date],[Date_Received])-1)<=7) 
Produces true or false. :(
Feb 14 '12 #13

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 14 '12 #14

P: 28
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.
Feb 15 '12 #15

NeoPa
Expert Mod 15k+
P: 31,494
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].

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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE (Temp.Within)
Temp.Within is already a Boolean value, so comparing it to True to create a Boolean value is pointless.
Feb 15 '12 #16

P: 28
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?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database 
  2.  
  3. Option Explicit 
  4.  
  5. Public Function Workdays(ByRef startDate As Date, _ 
  6.      ByRef endDate As Date, _ 
  7.      Optional ByRef strHolidays As String = "Holidays" _ 
  8.      ) As Integer 
  9.     ' Returns the number of workdays between startDate 
  10.     ' and endDate inclusive.  Workdays excludes weekends and 
  11.     ' holidays. Optionally, pass this function the name of a table 
  12.     ' or query as the third argument. If you don't the default 
  13.     ' is "Holidays". 
  14.     On Error GoTo Workdays_Error 
  15.     Dim nWeekdays As Integer 
  16.     Dim nHolidays As Integer 
  17.     Dim strWhere As String 
  18.  
  19.     ' DateValue returns the date part only. 
  20.     startDate = DateValue(startDate) 
  21.     endDate = DateValue(endDate) 
  22.  
  23.     nWeekdays = Weekdays(startDate, endDate) 
  24.     If nWeekdays = -1 Then 
  25.         Workdays = -1 
  26.         GoTo Workdays_Exit 
  27.     End If 
  28.  
  29.     strWhere = "[Holiday] >= #" & startDate _ 
  30.         & "# AND [Holiday] <= #" & endDate & "#" 
  31.  
  32.     ' Count the number of holidays. 
  33.     nHolidays = DCount(Expr:="[Holiday]", _ 
  34.         Domain:=strHolidays, _ 
  35.         Criteria:=strWhere) 
  36.  
  37.     Workdays = nWeekdays - nHolidays 
  38.  
  39. Workdays_Exit: 
  40.     Exit Function 
  41.  
  42. Workdays_Error: 
  43.     Workdays = -1 
  44.     MsgBox "Error " & Err.Number & ": " & Err.Description, _ 
  45.         vbCritical, "Workdays" 
  46.     Resume Workdays_Exit 
  47.  
  48. End Function 
  49.  
  50. Public Function Weekdays(ByRef startDate As Date, _ 
  51.     ByRef endDate As Date _ 
  52.     ) As Integer 
  53.     ' Returns the number of weekdays in the period from startDate 
  54.     ' to endDate inclusive. Returns -1 if an error occurs. 
  55.     ' If your weekend days do not include Saturday and Sunday and 
  56.     ' do not total two per week in number, this function will 
  57.     ' require modification. 
  58.     On Error GoTo Weekdays_Error 
  59.  
  60.     ' The number of weekend days per week. 
  61.     Const ncNumberOfWeekendDays As Integer = 2 
  62.  
  63.     ' The number of days inclusive. 
  64.     Dim varDays As Variant 
  65.  
  66.     ' The number of weekend days. 
  67.     Dim varWeekendDays As Variant 
  68.  
  69.     ' Temporary storage for datetime. 
  70.     Dim dtmX As Date 
  71.  
  72.     ' If the end date is earlier, swap the dates. 
  73.     If endDate < startDate Then 
  74.         dtmX = startDate 
  75.         startDate = endDate 
  76.         endDate = dtmX 
  77.     End If 
  78.  
  79.     ' Calculate the number of days inclusive (+ 1 is to add back startDate). 
  80.     varDays = DateDiff(Interval:="d", _ 
  81.         date1:=startDate, _ 
  82.         date2:=endDate) + 1 
  83.  
  84.     ' Calculate the number of weekend days. 
  85.     varWeekendDays = (DateDiff(Interval:="ww", _ 
  86.         date1:=startDate, _ 
  87.         date2:=endDate) _ 
  88.         * ncNumberOfWeekendDays) _ 
  89.         + IIf(DatePart(Interval:="w", _ 
  90.         Date:=startDate) = vbSunday, 1, 0) _ 
  91.         + IIf(DatePart(Interval:="w", _ 
  92.         Date:=endDate) = vbSaturday, 1, 0) 
  93.  
  94.     ' Calculate the number of weekdays. 
  95.     Weekdays = (varDays - varWeekendDays) 
  96.  
  97. Weekdays_Exit: 
  98.     Exit Function 
  99.  
  100. Weekdays_Error: 
  101.     Weekdays = -1 
  102.     MsgBox "Error " & Err.Number & ": " & Err.Description, _ 
  103.         vbCritical, "Weekdays" 
  104.     Resume Weekdays_Exit 
  105. End Function 
  106.  
As you can see, I'm still new in VBA and been working with it for only 2 weeks. Thanks so much!
Feb 15 '12 #17

NeoPa
Expert Mod 15k+
P: 31,494
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) :
  1. Lose the ByRefs for the date parameters.
  2. Declare any parameter that may cotain Null as a Variant.
  3. Handling [Date_Received] Is Null depends on your decision re how it needs to be handled, but Nz() may prove useful. EG :
    Expand|Select|Wrap|Line Numbers
    1. Nz([Date_Received], Date())

Your code might start something like :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function Workdays(ByVal datStartDate As Date, _
  5.                          ByVal varEndDate As Variant, _
  6.                          Optional strHolidays As String = "Holidays") As Integer
  7.     ' Returns the number of workdays between startDate
  8.     ' and endDate inclusive.  Workdays excludes weekends and
  9.     ' holidays. Optionally, pass this function the name of a table
  10.     ' or query as the third argument. If you don't the default
  11.     ' is "Holidays".
  12.     On Error GoTo Workdays_Error
  13.     Dim nWeekdays As Integer, nHolidays As Integer
  14.     Dim strWhere As String
  15.     Dim datEndDate as Date
  16.  
  17.     ' DateValue returns the date part only.
  18.     datEndDate = Nz(varEndDate, Date)
  19. ...
  20. End Function
Feb 15 '12 #18

P: 28
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Option Explicit
  4.  
  5. Public Function Workdays(ByVal startDate As Date, _
  6.      ByVal varEndDate As Variant, _
  7.      Optional ByRef strHolidays As String = "Holidays" _
  8.      ) As Integer
  9.     ' Returns the number of workdays between startDate
  10.     ' and endDate inclusive.  Workdays excludes weekends and
  11.     ' holidays. Optionally, pass this function the name of a table
  12.     ' or query as the third argument. If you don't the default
  13.     ' is "Holidays".
  14.     On Error GoTo Workdays_Error
  15.     Dim nWeekdays As Integer
  16.     Dim nHolidays As Integer
  17.     Dim strWhere As String
  18.     Dim endDate As Date
  19.  
  20.     ' DateValue returns the date part only.
  21.     startDate = DateValue(startDate)
  22.     endDate = Nz(varEndDate, Date)
  23.  
  24.     nWeekdays = Weekdays(startDate, endDate)
  25.     If nWeekdays = -1 Then
  26.         Workdays = -1
  27.         GoTo Workdays_Exit
  28.     End If
  29.  
  30.     strWhere = "[Holiday] >= #" & startDate _
  31.         & "# AND [Holiday] <= #" & endDate & "#"
  32.  
  33.     ' Count the number of holidays.
  34.     nHolidays = DCount(Expr:="[Holiday]", _
  35.         Domain:=strHolidays, _
  36.         Criteria:=strWhere)
  37.  
  38.     Workdays = nWeekdays - nHolidays
  39.  
  40. Workdays_Exit:
  41.     Exit Function
  42.  
  43. Workdays_Error:
  44.     Workdays = -1
  45.     MsgBox "Error " & Err.Number & ": " & Err.Description, _
  46.         vbCritical, "Workdays"
  47.     Resume Workdays_Exit
  48.  
  49. End Function
  50.  
  51. Public Function Weekdays(ByRef startDate As Date, _
  52.     ByRef endDate As Date _
  53.     ) As Integer
  54.     ' Returns the number of weekdays in the period from startDate
  55.     ' to endDate inclusive. Returns -1 if an error occurs.
  56.     ' If your weekend days do not include Saturday and Sunday and
  57.     ' do not total two per week in number, this function will
  58.     ' require modification.
  59.     On Error GoTo Weekdays_Error
  60.  
  61.     ' The number of weekend days per week.
  62.     Const ncNumberOfWeekendDays As Integer = 2
  63.  
  64.     ' The number of days inclusive.
  65.     Dim varDays As Variant
  66.  
  67.     ' The number of weekend days.
  68.     Dim varWeekendDays As Variant
  69.  
  70.     ' Temporary storage for datetime.
  71.     Dim dtmX As Date
  72.  
  73.     ' If the end date is earlier, swap the dates.
  74.     If endDate < startDate Then
  75.         dtmX = startDate
  76.         startDate = endDate
  77.         endDate = dtmX
  78.     End If
  79.  
  80.     ' Calculate the number of days inclusive (+ 1 is to add back startDate).
  81.     varDays = DateDiff(Interval:="d", _
  82.         date1:=startDate, _
  83.         date2:=endDate) + 1
  84.  
  85.     ' Calculate the number of weekend days.
  86.     varWeekendDays = (DateDiff(Interval:="ww", _
  87.         date1:=startDate, _
  88.         date2:=endDate) _
  89.         * ncNumberOfWeekendDays) _
  90.         + IIf(DatePart(Interval:="w", _
  91.         Date:=startDate) = vbSunday, 1, 0) _
  92.         + IIf(DatePart(Interval:="w", _
  93.         Date:=endDate) = vbSaturday, 1, 0)
  94.  
  95.     ' Calculate the number of weekdays.
  96.     Weekdays = (varDays - varWeekendDays)
  97.  
  98. Weekdays_Exit:
  99.     Exit Function
  100.  
  101. Weekdays_Error:
  102.     Weekdays = -1
  103.     MsgBox "Error " & Err.Number & ": " & Err.Description, _
  104.         vbCritical, "Weekdays"
  105.     Resume Weekdays_Exit
  106. End Function
  107.  
I also followed your suggestion on
Expand|Select|Wrap|Line Numbers
  1. Nz([Date_Received], Date()) 
  2.  
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.
Feb 16 '12 #19

NeoPa
Expert Mod 15k+
P: 31,494
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.
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?
Feb 16 '12 #20

P: 28
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 17 '12 #21

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 17 '12 #22

P: 28
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?
Feb 21 '12 #23

P: 28
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!
Feb 21 '12 #24

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 21 '12 #25

Post your reply

Sign in to post your reply or Sign up for a free account.