Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Issue with VBA Function when adding criteria in Access Query

Posted on 2015-02-21
7
Medium Priority
?
217 Views
Last Modified: 2016-02-10
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

Open in new window

0
Comment
Question by:Anthony6890
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40623458
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 40623504
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 40623510
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40623518
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
 
LVL 9

Accepted Solution

by:
Ramanhp earned 2000 total points
ID: 40623553
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40624218
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
 
LVL 1

Author Closing Comment

by:Anthony6890
ID: 40624254
This solution worked except I had to divide by 100 I stead of 100000. Thus worked great. Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question