Using a function as a query date range criteria

Posted on 2008-11-04
Last Modified: 2013-11-27
Generally, I will use functions to pass variables to queries.

However, I am having a unique problem (or senior moment <grin>) and the string that I am passing to the query is resulting a "type mismatch" error when the query executes.

Here is my function:
Public Function StudyDateRange() As String
Dim strTheString As String
Dim strTheStartDate As String
Dim strTheEndDate As String
    strTheStartDate = DLookup("dteStudyStartDate", "tblCF_StudyDateRange", True)
    strTheEndDate = DLookup("dteStudyEndDate", "tblCF_StudyDateRange", True)
    strTheString = "Between #" & strTheStartDate & "# And #" & strTheEndDate & "# Or Is Null"
    StudyDateRange = strTheString
End Function

In the query criteria of a date filed, I coded: StudyDateRange()

What am I doing wrong?

Lenny Gray
Question by:LennyGray
    LVL 39

    Expert Comment

    Your test (DLOOKUP) is asking if dteStudyStartDate in tblCF_StudyDateRange is True.

    I think you may be looking to use the dmax() and dmin() funciton.
    LVL 77

    Expert Comment

    The criteria is being interpreted as a literal string, not as an expression.
    I'm not sure how you would convert it into a valid criteria expression using a function.
    Have you tried to use:

    Between  DLookup("dteStudyStartDate", "tblCF_StudyDateRange") and
    DLookup("dteStudyEndDate", "tblCF_StudyDateRange")

    LVL 12

    Expert Comment

    I think that in the place holder " ##########" you need to add the column name

     strTheString = "Between #" & strTheStartDate & "# And #" & strTheEndDate & "# Or ########## Is Null"
    LVL 10

    Author Comment

    Brandon -

    The table is a single-record table. So the criteria being forced to True is valid.

    Peter57r -

    I tried your solution to no avail. Even after I changed it to:    

    strTheString = "Between " & DLookup("dteStudyStartDate", "tblCF_StudyDateRange") & " and " & DLookup("dteStudyEndDate", "tblCF_StudyDateRange")
    LVL 77

    Accepted Solution

    That's not my solution.
    My solution was to use the expression in the criteria cell - not to assign it via a string.
    LVL 10

    Author Comment

    Oh, thanks Peter, but I know that I could do that already.

    I am looking for a generic (function-based) criteria that I can use at will.

    The user defines a study period, which is a date range that I apply to pre-written queries. By passing a function as the criteria, I am able to keep the same query and let it be altered by the function values at runtime.

    LVL 10

    Author Closing Comment

    Thanks, Peter. I misunderstood. When I applied your approach, it gave me the flexibility that I was seeking with the function.

    Sorry for the mis-read on my part!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now