Re: Type mismatch

From: Alain (abilodeau_at_devencore.com)
Date: 01/11/05 

Date: Tue, 11 Jan 2005 11:26:06 -0500

Hi Ken,
Thanks for the input, question, can the expression of the DMin contain more
than one field ??
What I really need to perform without a whole bunch of If statement, is to
select the lowest date field in a specific recordset, I have some old code
to do the same job but checking 3 date field only not 5 so I am looking for
a better way to select the lowest date by rewriting the function., Any
pointers wil lbe appreciated

Thanks

"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:udQHtB$9EHA.208@TK2MSFTNGP12.phx.gbl...
> 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: Type mismatch
    ... The third argument of the DateAdd function must be a date data type. ... DateAdd function does not accept an SQL statement in any of its arguments. ... > Dim temp As Date ...
    (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)