Field Criteria not working?

[jset818]

This simple criteria doesn't seem to work for some of my queries. The Contract Delivery field is a simple Working Days function, with a Start Date and an End Date. All I want is for it to pull all working days that are greater than 10, but it's pulling all of the data. The previous two queries that I worked on used the same criteria and workingdays function worked just fine. Does it have something to do with the quotes around my criteria? I don't want the quotes around 10, but Access keeps automatically adding it.

[June7]

The Contract Delivery looks like a text value because of the left align (although I wonder why the Project ID is right aligned - are there spaces in front?). Alpha sort/filter rules apply to text, even if the text is all digits. 10 comes before 2. Need to convert the value to a number. Try Val() or CInt() functions. Then remove quote marks from around 10.

[jset818]

The Contract Delivery looks like a text value because of the left align (although I wonder why the Project ID is right aligned - are there spaces in front?). Alpha sort/filter rules apply to text, even if the text is all digits. 10 comes before 2. Need to convert the value to a number. Try Val() or CInt() functions. Then remove quote marks from around 10.

Hmm, I didn't know you can tell what type of format they are in by their alignment. Not sure why Project ID is right aligned, the format is set to General. There are spaces behind it though. I tried Val and CInt function, but it's still pulling the same data. Before I had the same issue with another query where it keeps changing my number to text value. I went back to the original Excel file and reformatted the column to Number. The Start Date and End Date for the Working Days function are actually from a crosstab table. I traced back to the original linked Excel file. The Comment column on the left are the dates, they are formatted as Date and Comment Type is General. This is the Workingdays function I used.

Contract Delivery: WorkingDays2([Property Chosen by Client],[Contract Pkg Sent to Client])

[June7]

Would have to see the code for the WorkingDays2 function.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in name convention.

[jset818]

It's an old code that I got from another database and I think whoever created it also got it from another website.


Option Compare Database
Option Explicit


Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2


Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database


Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)


'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above


intCount = 0


Do While StartDate <= EndDate


rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If


StartDate = StartDate + 1


Loop


WorkingDays2 = intCount


Exit_WorkingDays2:
Exit Function


Err_WorkingDays2:
Select Case Err


Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select


End Function

[June7]

The function is declared as Integer so the query should be receiving integer number, not text. I don't know why it is acting like text. What happens if you remove the quote marks from "10"?

I am surprised there is not a circular reference error because field references itself in the expression:

WorkingDays - Contract Delivery

If you want to provide db for analysis, follow instructions at bottom of my post.

[jset818]

Access keeps adding quotes around the 10. I would remove it, click somewhere else, and then Access adds it back on. I think it's one of the new features of Access 2010.

[June7]

It's not new. It's because Access knows the data in the field is text type. I don't know why the data is text. The function is designed to return integer.

[jset818]

Okay, I know why it's not pulling the correct data. Access basically sees the criteria >"10" the same as >"1" because 10 has two digits. Is there another way to do >10?

[June7]

Not while the field is seen as text data. See comment in post 2.

Need to figure out why the field is treated as text. It needs to be a number.