Re: Type mismatch

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 01/11/05 

Date: Tue, 11 Jan 2005 10:14:01 -0500

The third argument of the DateAdd function must be a date data type. The
DateAdd function does not accept an SQL statement in any of its arguments.
You'd need to either get the query's result via a recordset or via the DMin
domain function, put that value into a variable, and then use that variable
in the DateAdd function.

Also, the SQL statement that you have posted will not work as you intend.
The Min aggregate function in SQL does not give you the minimum among
various fields; it returns the minimum value of a single field from the
selected records. 

-- 
        Ken Snell
<MS ACCESS MVP>
"Alain" <abilodeau@devencore.com> wrote in message 
news:rAREd.24481$TN6.790978@news20.bellglobal.com...
> Hi,
>
> I am trying to create a function that need to return a date but getting 
> the error 13 type mismatch in my select :
>
> Public Function ActivationDate(ActDateNotice As Date, IdBranch As Integer) 
> As Date
> Dim temp As Date
>
> temp = "SELECT 
> MIN(ExerDateNotice,ExerDateRenOption,ExerDateExpRights,ExerDateCanrights,ExerDatePurOption)FROM 
> [General information] WHERE IdBranch = " & IdBranch
>
> ActivationDate = DateAdd("m", -12, temp)
> End Function
>
> From my understanding, it is something that has to be done with data type, 
> I have tried different data type in the function even using everything as 
> Variant but still getting the same error. The fields in the table are the 
> correct type, date are short date and idbranch is a autonumber
>
> Can anyone tell me what I am doing wrong
>
> Thanks
>
> Alain
>
>

Relevant Pages

  • Re: Type mismatch
    ... > select the lowest date field in a specific recordset, ... >> The third argument of the DateAdd function must be a date data type. ... the SQL statement that you have posted will not work as you intend. ...
    (microsoft.public.access.formscoding)
  • Re: SQL Query
    ... Therefore, God exists. ... A SQL statement begins with "SELECT," ... This means that you are specifying the "Shipping_Group" column of the ... It also means that the "Shipping_Group" column is of a text data type. ...
    (microsoft.public.frontpage.programming)
  • Re: SQL Query
    ... A SQL statement begins with "SELECT," ... alphabetical order. ... This means that you are specifying the "Shipping_Group" column of the ... It also means that the "Shipping_Group" column is of a text data type. ...
    (microsoft.public.frontpage.programming)
  • Re: How do you hide Columns in a SubForm
    ... RowSource, using a SQL statement, so i wouldn't be surprised that the ... Dim strSubformSQL As String, siteID As String ... the first thing i'd check would be the data type of the criteria fields. ...
    (microsoft.public.access.modulesdaovba)
  • Possible Oracle 9.2.0.7 performance issue with DBD::Oracles column_info()
    ... My coworkers and I have a question regarding the SQL statement that DBD::Oracle's column_infofunction executes. ... We depend upon column_infoto discover the data type of a large object column is so we can bind it properly. ... While this worked great for us in the past, when we upgraded to Oracle 9.2.0.7 we noticed significant performance drops whenever this method was called. ... A data load test with dbi profiler running showed that column_info's SQL accounts for 78.33% of all dbi calls made during the test. ...
    (perl.dbi.users)
Loading