Type mismatch

[Alain]

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

 

[Guest]

hi,
guessing here but i think the problem is that your select
seem to be select 5 different dates and temp is declared
as A date. you may have to rewrite a little - have 5 temps
and 5 selects.

 

[Ken Snell [MVP]]

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.

 

[Alain]

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

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>

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

 

[Ken Snell [MVP]]

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>

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

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>

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