asked on

Using a function as a query date range criteria

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?

Thanks,
Lenny Gray

BrandonGalderisi

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.

http://msdn.microsoft.com/en-us/library/aa172181(office.11).aspx

peter57r

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")

Dimitris

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

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

LennyGray

ASKER

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")

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LennyGray

ASKER

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.

Lenny

LennyGray

ASKER

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!