• Status: Solved
• Priority: Medium
• Security: Public
• Views: 224

# Issue with VBA Function when adding criteria in Access Query

Hi All,

I have a very simple function that I wrote to compare two number fields.  One field has 5 digits and another field has 7 digits.  I only care about the first 5 digits in the second field so I have a small function that trims the number and then does a compare and produces a result of 1 if it matches the conditions of the if statement, otherwise it produces a 0.  When I enter the function into an access query it runs without issue.  As soon as I put criteria that I want the function to only equal 1, below the function, the query freezes upon running and I cannot get out of it.  Below is the function.  If someone could help me I'd really appreciate it...

``````Option Compare Database

Public Function fnOVERBILL(BILLDATE As Double, MBHTDT As Double) As Double

Dim TERM As Double

TERM = CLng(Left(MBHTDT, 5))

If (BILLDATE > TERM) And (MBHTDT > 0) Then
fnOVERBILL = 1
Else
fnOVERBILL = 0

End If

End Function
``````
0
Anthony6890
1 Solution

CIOCommented:
Try using this expression in the query:

Test: Nz(BILLDATE, 0) > Val(Left(Nz([MBHTDT], 0), 5)) And Nz([MBHTDT], 0) > 0

and set the criteria for this column to: True

/gustav
0

Author Commented:
Thanks for getting back to me.  I just tried that and it still locks up on me.  Access enters a Not Responding phase.

-Anthony
0

Author Commented:
Gustav,

When I use your formula, and I don't put true in the criteria it runs without issue.  I know that there are currently no results for this query which is why I want to test it out.  It can produce a 0 for everyone; however, if I put "True" in the criteria I shouldn't have any results happen.

-Anthony
0

CIOCommented:
Will this - without criteria - return any results:

Test1: Val(Left(Nz([MBHTDT], 0), 5))
Test2 Nz([BILLDATE], 0)

If so, try with these and the criteria as written:

Test1: Val(Left(Nz([MBHTDT], 0), 5))
Criteria: <[BILLDATE]

Test2 Nz([BILLDATE], 0)
Criteria: >0

/gustav
0

Commented:
I recommend try dividing the number of length 7 with 100000, and take the int part only and then do the matching criteria as per your logic..
0

Commented:
1.  Are these actually date values, or is the data type of the field actually number?

2.  If number, what do the extra two characters in the MBHTDT column mean?

3.  Do you have any records where either the BILLDATE or the MBHTDT is NULL?
0

Author Commented:
This solution worked except I had to divide by 100 I stead of 100000. Thus worked great. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.