Re: Type mismatch
From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 01/12/05
- Next message: Rufus: "Re: "PopUp Message" notification? warning?"
- Previous message: tina: "Re: Control with condition"
- In reply to: Alain: "Re: Type mismatch"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 11 Jan 2005 19:48:20 -0500
No, not directly it cannot contain the names of more than one field if you
want to compare the values from those fields. You'd need to write your own
function to do this, or use a union query to get a record for each field and
then use Min on that field from the union query.
Here is a function that I wrote to get the minimum value of an array of
values:
Public Function MinValueVariantArray(ParamArray ValuesArray() As Variant) As
Variant
' ** THIS FUNCTION RETURNS THE MINIMUM VALUE FROM AN ARRAY OF VALUES.
Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant
xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB >= 0 And xlngLB >= 0 Then
xvarTp = ValuesArray(xlngLB)
For xlngCnt = xlngLB + 1 To xlngUB
If ValuesArray(xlngCnt) < xvarTp Then xvarTp = ValuesArray(xlngCnt)
Next xlngCnt
End If
MinValueVariantArray = xvarTp
End Function
--
Ken Snell
<MS ACCESS MVP>
"Alain" <abilodeau@devencore.com> wrote in message
news:y6TEd.25180$TN6.820797@news20.bellglobal.com...
> 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
>>>
>>>
>>
>>
>
>
- Next message: Rufus: "Re: "PopUp Message" notification? warning?"
- Previous message: tina: "Re: Control with condition"
- In reply to: Alain: "Re: Type mismatch"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
