Using a custom type function / nested types in a query (1 Viewer)

MikhaR

New member
Local time
Today, 02:26
Joined
May 5, 2021
Messages
2
Hi.

Here is an example of a function that is of a custom type:

Code:
Public Type typ1
      answer1 as integer
      answer2 as integer
End Type

Public Function fun1 (var1 as variant, var2 as variant) as typ1
      fun1.answer1 = var1 + 1
      fun1.answer2 = var2 + 2
End Function


Now, in a query I want to be able to use fun1(1,1).answer1 in criteria but receiving the following error:

The expression you have entered has an invalid . (dor) or ! operator or invalid parenthesis.

Is there any workaround here?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:26
Joined
May 7, 2009
Messages
13,756
Code:
Public Type typ1
      answer1 As Integer
      answer2 As Integer
End Type

Public Function fun1(var1 As Variant, var2 As Variant) As typ1
    Dim f As typ1
      f.answer1 = var1 + 1
      f.answer2 = var2 + 2
      fun1 = f
End Function


Private Sub test()
Dim m As typ1
m = fun1(1, 2)
Debug.Print m.answer1, m.answer2

End Sub
 

MikhaR

New member
Local time
Today, 02:26
Joined
May 5, 2021
Messages
2
Code:
Public Type typ1
      answer1 As Integer
      answer2 As Integer
End Type

Public Function fun1(var1 As Variant, var2 As Variant) As typ1
    Dim f As typ1
      f.answer1 = var1 + 1
      f.answer2 = var2 + 2
      fun1 = f
End Function


Private Sub test()
Dim m As typ1
m = fun1(1, 2)
Debug.Print m.answer1, m.answer2

End Sub
In the post, I was explaining that I'm having trouble using this syntax in a Query, not in VBA
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:26
Joined
May 21, 2018
Messages
5,404
I do not believe a custom type can be used in a query. I assume you are trying something like
TestFun:fun1([id],[field2]).answer1
I am guessing that is not your real example, because clearly that could be done with two functions and there is no real purpose for what you are showing. If you need to set the type, you might be able to do a wrapper for each Answer1 and Answer2.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:26
Joined
May 7, 2009
Messages
13,756
you can't directly call it in a query.
you need to create another function that will Return either answer1 or answer2:

Code:
Public Function haveFun1(var1 As Variant, var2 As Variant, ByVal intAnswer As Integer) As Variant
    Dim t As typ1
    t = fun1(var1, var2)
    If intAnswer = 1 Then
        haveFun1 = t.answer1
    ElseIf intAnswer = 2 Then
        haveFun1 = t.answer2
    End If
    
End Function

on your Query:

select ID, num1, num2, haveFun1([num1],[num2],1) As Answer1, haveFun1([num1],[num2],2) As Answer2 from yourTable;
 

Users who are viewing this thread

Top Bottom