Results 1 to 1 of 1

Thread: Call function from query in access

  1. #1

    Thread Starter
    Join Date
    Mar 2006

    Call function from query in access


    I have a function which returns a string. I would like to be able to call it from an Access query. When I try to run this query it pops up an error stating "Data type mismatch in query expression". GetDateCriteria returns a string that contains what I would type in the criteria section of the query designer. I think the problem may be that in the query, there is an equal sign between my field and function call, but I don't want anything at all to be there. If I take out the equal sign, then the query won't run at all, because it says it's missing an operator.

    I just tried changing the function to return "#1/1/00#" and it still give me the data type mismatch error, so that makes me think that since TmLdWrkDate is a date field, Access doesn't like the text string being returned.

    Any ideas on how to solve this problem? Code and query are below.


    VB Code:
    1. Public Function GetDateCriteria(frmName As String, startDate As String, endDate As String) As String
    3.     Dim retval As Variant
    5.     If IsNull(Forms(frmName).Controls(startDate)) Then
    6.         If IsNull(Forms(frmName).Controls(endDate)) Then
    7.             retval = "Is Not Null"
    8.         Else
    9.             retval = "<= #" & Forms(frmName).Controls(endDate) & "#"
    10.         End If
    11.     Else
    12.         If IsNull(Forms(frmName).Controls(endDate)) Then
    13.             retval = ">= #" & Forms(frmName).Controls(startDate) & "#"
    14.         Else
    15.             retval = ">= #" & Str(Forms(frmName).Controls(startDate)) & "# And <= #" & Str(Forms(frmName).Controls(endDate)) & "#"
    16.         End If
    17.     End If
    19.     GetDateCriteria = retval
    21. End Function

    SELECT tblReportVoidedAcctsHistory.UnitNum, tblReportVoidedAcctsHistory.Insurance, tblReportVoidedAcctsHistory.PatNum, tblReportVoidedAcctsHistory.AnalystWrkDate, tblReportVoidedAcctsHistory.TLWrkDate, tblReportVoidedAcctsHistory.TmLeadID, tblReportVoidedAcctsHistory.TLName, tblReportVoidedAcctsHistory.DiscrepType, tblReportVoidedAcctsHistory.PaperworkAmount, tblReportVoidedAcctsHistory.AnalystID, tblReportVoidedAcctsHistory.AnalystComm, tblReportVoidedAcctsHistory.TLorMgrComm, tblReportVoidedAcctsHistory.Status
    FROM tblReportVoidedAcctsHistory
    WHERE (((tblReportVoidedAcctsHistory.TLWrkDate)=GetDateCriteria("frmReportSelection","txtStartDate","txtEndDate")));
    Last edited by Ranthalion; Apr 27th, 2006 at 07:40 AM. Reason: Additional Info

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width