Solved Using a function as part of a query, with criteria (1 Viewer)

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
I have a query that pulls data from a number of tables.
My manager has asked that I add a field, using data from a function he created (copied?) years ago, which calculates the number of work days between two dates and returns this as a LONG number.
I should add that this function is used elsewhere in the database and has worked as expected for years, with no problems.

When I add it to a SELECT query, it returns a number and these numbers are correct.

However, if I add a criteria to the field that uses this query (e.g. <=14) I get a message 'Data type mismatch in criteria expression'.
I've narrowed it down to the point where I know it's definitely this field that's causing the problem.

Does anyone know of any limits/weaknesses within Access if using a function in combination with criteria in this way?
 

Minty

AWF VIP
Local time
Today, 19:41
Joined
Jul 26, 2013
Messages
10,355
Short answer no.
Have you tried saving the query with the expression in it, then used that query as a basis for applying the criteria?
 

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
Short answer no.
Have you tried saving the query with the expression in it, then used that query as a basis for applying the criteria?
Yes, I did. No luck.
My next step was to save the results as a table, then query from that using the criteria.
That works, I was just curious why the first was giving the error message.
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:41
Joined
Jan 23, 2006
Messages
15,364
Suggest you post the relevant query, function so readers understand your issue in context.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:41
Joined
Oct 29, 2018
Messages
21,358
Are there any null values in your records? Just curious...
 

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
Suggest you post the relevant query, function so readers understand your issue in context.
This is the query
Code:
UPDATE ((tblARank INNER JOIN APPLP2_V_CAS ON tblARank.VIN = APPLP2_V_CAS.VIN_NO) 
INNER JOIN APPLP2_V_CAS_CLM 
ON (APPLP2_V_CAS_CLM.CAS_ID = APPLP2_V_CAS.CAS_ID) 
AND (tblARank.WARRANTY_CLAIM_NUMBER = APPLP2_V_CAS_CLM.CLM_NO)) 
INNER JOIN MasterListCollection 
ON tblARank.MODEL = MasterListCollection.DataTableName 
SET APPLP2_V_CAS_CLM.CAS_ID = [REF_NO]
WHERE ((Not (APPLP2_V_CAS_CLM.CAS_ID) Is Null) 
AND ((funWorkDaysDifference([CALENDAR_PROCESS_DATE],Date()))<=14) 
AND ((tblARank.Disposition)<>8) 
AND ((MasterListCollection.NAMQ_Model)=True));
Without the <=14 criteria, it works perfectly.

This is the function
Code:
Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long
Dim lngstart As Long
Dim lngend As Long
Dim lngWorkdays As Long


'Check to see if dtStartDay > dtEndDay.  If so, then there were 0 workdays passed
If dtStartDay > dtEndDay Then
lngWorkdays = 0
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= dtEndDay
    If WeekDay(dtNominalEndDay, 2) <> 6 Then
        If WeekDay(dtNominalEndDay, 2) <> 7 Then
            lngTotalDays = lngTotalDays + 1
        End If
    End If
    dtNominalEndDay = dtNominalEndDay + 1
Wend

'convert end date and startdate into long integer format for the DCount operation to avoid misreading of dates as US format
lngstart = dtStartDay
lngend = dtEndDay

'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= " & lngend & " AND dtObservedDate >= " & lngstart & " AND Weekday(dtObservedDate,2) <> 6 AND Weekday(dtObservedDate,2) <> 7")

'lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "int(dtObservedDate) <= " & Int(dtEndDay) & " AND int(dtObservedDate) >= " & Int(dtStartDay) & " AND Weekday(dtObservedDate,2) <> 6 AND Weekday(dtObservedDate,2) <> 7")

'Here are how many total days are between the two dates - this is inclusive of the start and end date

If lngTotalDays - lngTotalHolidays > 0 Then
   lngWorkdays = lngTotalDays - lngTotalHolidays - 1
ElseIf lngTotalDays - lngTotalHolidays > 0 Then
   lngWorkdays = 0
Else
    lngWorkdays = 0
End If

funWorkDaysDifference = lngWorkdays

End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:41
Joined
Oct 29, 2018
Messages
21,358
No, when I run it as a select query, there are none.
How about the table itself, are there any nulls? Are you able to post a test db?
 

Minty

AWF VIP
Local time
Today, 19:41
Joined
Jul 26, 2013
Messages
10,355
I was about to suggest Adding a

And CALENDAR_PROCESS_DATE IS NOT NULL

Just out of interest.
 

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
How about the table itself, are there any nulls? Are you able to post a test db?
Sadly, it's huge, is an ODBC table I link to, and contains a lot of confidential data.
If I make a local copy and cut it down to a size where I can post it, I think that would negate any benefit?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:41
Joined
Oct 29, 2018
Messages
21,358
Sadly, it's huge, is an ODBC table I link to, and contains a lot of confidential data.
If I make a local copy and cut it down to a size where I can post it, I think that would negate any benefit?
We won't know for sure until you try...

In any case, the usual suspect when you get that type of error message is because of a null value. So, try to eliminate any as part of your troubleshooting steps, just to see if it helps isolate or fix the problem.

Good luck!
 

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
I was about to suggest Adding a

And CALENDAR_PROCESS_DATE IS NOT NULL

Just out of interest.
Unfortunately, I tried adding that to the criteria and also tried changing the field to Nz(funWorkDaysDifference([CALENDAR_PROCESS_DATE],Date()),0)
Neither helped.
 

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
We won't know for sure until you try...

In any case, the usual suspect when you get that type of error message is because of a null value. So, try to eliminate any as part of your troubleshooting steps, just to see if it helps isolate or fix the problem.

Good luck!
Thanks, I'll post if I locate anything
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:41
Joined
Oct 29, 2018
Messages
21,358
Unfortunately, I tried adding that to the criteria and also tried changing the field to Nz(funWorkDaysDifference([CALENDAR_PROCESS_DATE],Date()),0)
Neither helped.
How about?
Code:
 funWorkDaysDifference(Nz([CALENDAR_PROCESS_DATE],Date()),Date())
 

Alc

Registered User.
Local time
Today, 15:41
Joined
Mar 23, 2007
Messages
2,407
Two corrupt records out of 1MM+.
No way to read any of the fields in them other than the the ID. They were over 12 years old, so I had the admins run whatever their equivalent of a 'compact and repair' on the table.
All working now.

Thanks all for the suggestions that pointed me in the right direction.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:41
Joined
Oct 29, 2018
Messages
21,358
Two corrupt records out of 1MM+.
No way to read any of the fields in them other than the the ID. They were over 12 years old, so I had the admins run whatever their equivalent of a 'compact and repair' on the table.
All working now.

Thanks all for the suggestions that pointed me in the right direction.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom