Returning User Defined Type values to queries

I have a user defined type BoatDetails in a standard module

Public Type BoatDetails
BtID As String
BtName As String
BtClass As String
End Type

I am trying to call this function from a query using AK2000

Public Function CombineBoatDetails(MemID As Long) As BoatDetails

....
End function

The function CombineBoatDetails(582).BtName returns the correct value when
viewed in the Immediate window.

Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a query
grid. If I type it the SQL there is no problem, but the query doesn't run.

SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS BoatNames
FROM Member;


If I type it into the query grid I get
"The expression you entered has an invalid . (dot) or ! operator or invalid
parenthesises"

Am I doing something wrong, or is the theory correct that you can't use User
Defined Typed in queries

Thanks

Phil

Re: Returning User Defined Type values to queries

Phil Stanton wrote:

> I have a user defined type BoatDetails in a standard module
>
> Public Type BoatDetails
> BtID As String
> BtName As String
> BtClass As String
> End Type
>
> I am trying to call this function from a query using AK2000
>
> Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>
> ...
> End function
>
> The function CombineBoatDetails(582).BtName returns the correct value when
> viewed in the Immediate window.
>
> Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a query
> grid. If I type it the SQL there is no problem, but the query doesn't run.
>
> SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS BoatNames
> FROM Member;
>
>
> If I type it into the query grid I get
> "The expression you entered has an invalid . (dot) or ! operator or invalid
> parenthesises"
>
> Am I doing something wrong, or is the theory correct that you can't use User
> Defined Typed in queries

I wouldn't think so. Tho I see nothing wrong with calling a function
that would return values understood by the query.

You have
Public Function CombineBoatDetails(MemID As Long) As BoatDetails

I'm not sure how a SQL statement would recongnize a structured/type
variable. It's similar to the hyperlinkpart function.
? hyperlinkpart("Display#Address",acAddress)
return Address from the immediate window but chokes on it in a SQL
statement. You have to supply the number 2 instead of acAddress in the
query.

Why not have your function pass back a string? As In
Public Function CombineBoatDetails(MemID As Long) As String

Then in the query enter
CombineBoatDetails([MemberID]) AS BoatNames
or from the query builder enter in the column
BoatNames : CombineBoatDetails([MemberID])

The above will return a value it understands...like a string, a date, or
a number. Passing back a type of variable type BoatDetails is most
likely a type not understood by SQL.

TriggerHippie
http://www.youtube.com/watch?v=8bQgFM3bNyw

>
> Thanks
>
> Phil
>
>

Re: Returning User Defined Type values to queries

Hi Salad,

Trying to get the function to pass 3 variables, BoatIDs, Boat Names and Boat
Classes. I can easily get over the problem by calling 3 similar functions to
give the required outputs, but thought it might be interesting to do it this
way. Also thought the query might be faster as calling functions seems to
slow a query by a factor of 100 or so

Phil

"Salad" wrote in message
news:KLudnTVskLDmu2TanZ2dnUVZ_uevnZ2d@earthlink.com...
> Phil Stanton wrote:
>
>> I have a user defined type BoatDetails in a standard module
>>
>> Public Type BoatDetails
>> BtID As String
>> BtName As String
>> BtClass As String
>> End Type
>>
>> I am trying to call this function from a query using AK2000
>>
>> Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>
>> ...
>> End function
>>
>> The function CombineBoatDetails(582).BtName returns the correct value
>> when viewed in the Immediate window.
>>
>> Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a
>> query grid. If I type it the SQL there is no problem, but the query
>> doesn't run.
>>
>> SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS BoatNames
>> FROM Member;
>>
>>
>> If I type it into the query grid I get
>> "The expression you entered has an invalid . (dot) or ! operator or
>> invalid parenthesises"
>>
>> Am I doing something wrong, or is the theory correct that you can't use
>> User Defined Typed in queries
>
> I wouldn't think so. Tho I see nothing wrong with calling a function that
> would return values understood by the query.
>
> You have
> Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>
> I'm not sure how a SQL statement would recongnize a structured/type
> variable. It's similar to the hyperlinkpart function.
> ? hyperlinkpart("Display#Address",acAddress)
> return Address from the immediate window but chokes on it in a SQL
> statement. You have to supply the number 2 instead of acAddress in the
> query.
>
> Why not have your function pass back a string? As In
> Public Function CombineBoatDetails(MemID As Long) As String
>
> Then in the query enter
> CombineBoatDetails([MemberID]) AS BoatNames
> or from the query builder enter in the column
> BoatNames : CombineBoatDetails([MemberID])
>
> The above will return a value it understands...like a string, a date, or a
> number. Passing back a type of variable type BoatDetails is most likely a
> type not understood by SQL.
>
> TriggerHippie
> http://www.youtube.com/watch?v=8bQgFM3bNyw
>
>>
>> Thanks
>>
>> Phil
>>

Re: Returning User Defined Type values to queries

Phil Stanton wrote:
> Hi Salad,
>
> Trying to get the function to pass 3 variables, BoatIDs, Boat Names and Boat
> Classes. I can easily get over the problem by calling 3 similar functions to
> give the required outputs, but thought it might be interesting to do it this
> way. Also thought the query might be faster as calling functions seems to
> slow a query by a factor of 100 or so
>
> Phil
>

Return the three values as a delimited string.

Re: Returning User Defined Type values to queries

Phil Stanton wrote:

> Hi Salad,
>
> Trying to get the function to pass 3 variables, BoatIDs, Boat Names and Boat
> Classes. I can easily get over the problem by calling 3 similar functions to
> give the required outputs, but thought it might be interesting to do it this
> way. Also thought the query might be faster as calling functions seems to
> slow a query by a factor of 100 or so
>
> Phil

rkc gave a great solution.

I'm not aware of a query being slowed down by a factor of 100. What
I've seen is that you might get a repaint of the screen column as it
calculates it as you pageup/dn thru it.

I think you'd be safe returning a field type recognized by a table. I
doubt you'll get it to recognize an unrecognized/user-defined field
type. Sometimes what we wish might exceed other's limits.

Sea
http://www.youtube.com/watch?v=VnCS25z18pI

>
> "Salad" wrote in message
> news:KLudnTVskLDmu2TanZ2dnUVZ_uevnZ2d@earthlink.com...
>
>>Phil Stanton wrote:
>>
>>
>>>I have a user defined type BoatDetails in a standard module
>>>
>>> Public Type BoatDetails
>>> BtID As String
>>> BtName As String
>>> BtClass As String
>>>End Type
>>>
>>>I am trying to call this function from a query using AK2000
>>>
>>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>>
>>>...
>>>End function
>>>
>>>The function CombineBoatDetails(582).BtName returns the correct value
>>>when viewed in the Immediate window.
>>>
>>>Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a
>>>query grid. If I type it the SQL there is no problem, but the query
>>>doesn't run.
>>>
>>>SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS BoatNames
>>>FROM Member;
>>>
>>>
>>>If I type it into the query grid I get
>>>"The expression you entered has an invalid . (dot) or ! operator or
>>>invalid parenthesises"
>>>
>>>Am I doing something wrong, or is the theory correct that you can't use
>>>User Defined Typed in queries
>>
>>I wouldn't think so. Tho I see nothing wrong with calling a function that
>>would return values understood by the query.
>>
>>You have
>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>
>>I'm not sure how a SQL statement would recongnize a structured/type
>>variable. It's similar to the hyperlinkpart function.
>>? hyperlinkpart("Display#Address",acAddress)
>>return Address from the immediate window but chokes on it in a SQL
>>statement. You have to supply the number 2 instead of acAddress in the
>>query.
>>
>>Why not have your function pass back a string? As In
>>Public Function CombineBoatDetails(MemID As Long) As String
>>
>>Then in the query enter
>>CombineBoatDetails([MemberID]) AS BoatNames
>>or from the query builder enter in the column
>>BoatNames : CombineBoatDetails([MemberID])
>>
>>The above will return a value it understands...like a string, a date, or a
>>number. Passing back a type of variable type BoatDetails is most likely a
>>type not understood by SQL.
>>
>>TriggerHippie
>>http://www.youtube.com/watch?v=8bQgFM3bNyw
>>
>>
>>>Thanks
>>>
>>>Phil
>>>
>
>

Re: Returning User Defined Type values to queries

OK, I give in. Do it the long way with the delimited varaibles

Thanks

Phil


"Salad" wrote in message
news:JtudndXDgbJnrWTanZ2dnUVZ_sOrnZ2d@earthlink.com...
> Phil Stanton wrote:
>
>> Hi Salad,
>>
>> Trying to get the function to pass 3 variables, BoatIDs, Boat Names and
>> Boat Classes. I can easily get over the problem by calling 3 similar
>> functions to give the required outputs, but thought it might be
>> interesting to do it this way. Also thought the query might be faster as
>> calling functions seems to slow a query by a factor of 100 or so
>>
>> Phil
>
> rkc gave a great solution.
>
> I'm not aware of a query being slowed down by a factor of 100. What I've
> seen is that you might get a repaint of the screen column as it calculates
> it as you pageup/dn thru it.
>
> I think you'd be safe returning a field type recognized by a table. I
> doubt you'll get it to recognize an unrecognized/user-defined field type.
> Sometimes what we wish might exceed other's limits.
>
> Sea
> http://www.youtube.com/watch?v=VnCS25z18pI
>
>>
>> "Salad" wrote in message
>> news:KLudnTVskLDmu2TanZ2dnUVZ_uevnZ2d@earthlink.com...
>>
>>>Phil Stanton wrote:
>>>
>>>
>>>>I have a user defined type BoatDetails in a standard module
>>>>
>>>> Public Type BoatDetails
>>>> BtID As String
>>>> BtName As String
>>>> BtClass As String
>>>>End Type
>>>>
>>>>I am trying to call this function from a query using AK2000
>>>>
>>>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>>>
>>>>...
>>>>End function
>>>>
>>>>The function CombineBoatDetails(582).BtName returns the correct value
>>>>when viewed in the Immediate window.
>>>>
>>>>Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a
>>>>query grid. If I type it the SQL there is no problem, but the query
>>>>doesn't run.
>>>>
>>>>SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS BoatNames
>>>>FROM Member;
>>>>
>>>>
>>>>If I type it into the query grid I get
>>>>"The expression you entered has an invalid . (dot) or ! operator or
>>>>invalid parenthesises"
>>>>
>>>>Am I doing something wrong, or is the theory correct that you can't use
>>>>User Defined Typed in queries
>>>
>>>I wouldn't think so. Tho I see nothing wrong with calling a function
>>>that would return values understood by the query.
>>>
>>>You have
>>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>>
>>>I'm not sure how a SQL statement would recongnize a structured/type
>>>variable. It's similar to the hyperlinkpart function.
>>>? hyperlinkpart("Display#Address",acAddress)
>>>return Address from the immediate window but chokes on it in a SQL
>>>statement. You have to supply the number 2 instead of acAddress in the
>>>query.
>>>
>>>Why not have your function pass back a string? As In
>>>Public Function CombineBoatDetails(MemID As Long) As String
>>>
>>>Then in the query enter
>>>CombineBoatDetails([MemberID]) AS BoatNames
>>>or from the query builder enter in the column
>>>BoatNames : CombineBoatDetails([MemberID])
>>>
>>>The above will return a value it understands...like a string, a date, or
>>>a number. Passing back a type of variable type BoatDetails is most
>>>likely a type not understood by SQL.
>>>
>>>TriggerHippie
>>>http://www.youtube.com/watch?v=8bQgFM3bNyw
>>>
>>>
>>>>Thanks
>>>>
>>>>Phil
>>>>
>>

Re: Returning User Defined Type values to queries

On Apr 6, 4:36 pm, "Phil Stanton" wrote:
> OK, I give in. Do it the long way with the delimited varaibles
>
> Thanks
>
> Phil
>
> "Salad" wrote in message
>
> news:JtudndXDgbJnrWTanZ2dnUVZ_sOrnZ2d@earthlink.com...
>
> > Phil Stanton wrote:
>
> >> Hi Salad,
>
> >> Trying to get the function to pass 3 variables, BoatIDs, Boat Names and
> >> Boat Classes. I can easily get over the problem by calling 3 similar
> >> functions to give the required outputs, but thought it might be
> >> interesting to do it this way. Also thought the query might be faster as
> >> calling functions seems to slow a query by a factor of 100 or so
>
> >> Phil
>
> > rkc gave a great solution.
>
> > I'm not aware of a query being slowed down by a factor of 100. What I've
> > seen is that you might get a repaint of the screen column as it calculates
> > it as you pageup/dn thru it.
>
> > I think you'd be safe returning a field type recognized by a table. I
> > doubt you'll get it to recognize an unrecognized/user-defined field type.
> > Sometimes what we wish might exceed other's limits.
>
> > Sea
> >http://www.youtube.com/watch?v=VnCS25z18pI
>
> >> "Salad" wrote in message
> >>news:KLudnTVskLDmu2TanZ2dnUVZ_uevnZ2d@earthlink.com...
>
> >>>Phil Stanton wrote:
>
> >>>>I have a user defined type BoatDetails in a standard module
>
> >>>> Public Type BoatDetails
> >>>> BtID As String
> >>>> BtName As String
> >>>> BtClass As String
> >>>>End Type
>
> >>>>I am trying to call this function from a query using AK2000
>
> >>>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>
> >>>>...
> >>>>End function
>
> >>>>The function CombineBoatDetails(582).BtName returns the correct value
> >>>>when viewed in the Immediate window.
>
> >>>>Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a
> >>>>query grid. If I type it the SQL there is no problem, but the query
> >>>>doesn't run.
>
> >>>>SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS BoatNames
> >>>>FROM Member;
>
> >>>>If I type it into the query grid I get
> >>>>"The expression you entered has an invalid . (dot) or ! operator or
> >>>>invalid parenthesises"
>
> >>>>Am I doing something wrong, or is the theory correct that you can't use
> >>>>User Defined Typed in queries
>
> >>>I wouldn't think so. Tho I see nothing wrong with calling a function
> >>>that would return values understood by the query.
>
> >>>You have
> >>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>
> >>>I'm not sure how a SQL statement would recongnize a structured/type
> >>>variable. It's similar to the hyperlinkpart function.
> >>>? hyperlinkpart("Display#Address",acAddress)
> >>>return Address from the immediate window but chokes on it in a SQL
> >>>statement. You have to supply the number 2 instead of acAddress in the
> >>>query.
>
> >>>Why not have your function pass back a string? As In
> >>>Public Function CombineBoatDetails(MemID As Long) As String
>
> >>>Then in the query enter
> >>>CombineBoatDetails([MemberID]) AS BoatNames
> >>>or from the query builder enter in the column
> >>>BoatNames : CombineBoatDetails([MemberID])
>
> >>>The above will return a value it understands...like a string, a date, or
> >>>a number. Passing back a type of variable type BoatDetails is most
> >>>likely a type not understood by SQL.
>
> >>>TriggerHippie
> >>>http://www.youtube.com/watch?v=8bQgFM3bNyw
>
> >>>>Thanks
>
> >>>>Phil

Air Code ... should work

Function CombineBoatDetails(MemID As Long)

Dim aDetails(0 to 2)

' rest of function

aDetails(0) = BoatIDs
aDetails(1) = BoatNames
aDetails(2) = BoatClasses

CombineBoatDetails=aDetails

End Function

SELECT Member.MemberID, CombineBoatDetails(MemberID)(1) AS BoatNames
FROM Member;

Re: Returning User Defined Type values to queries

Thanks Lyle.

Same problem Get
The expression you entered has an invalid . (dot) or ! operator or invalid
parenthesises"
when I convert the SQL back to a query, highlighted at the start of the
bracket round the (2)

Phil


"lyle" wrote in message
news:69f462b1-07f8-4f08-ace3-ec080a93a285@d2g2000pra.googleg roups.com...
> On Apr 6, 4:36 pm, "Phil Stanton" wrote:
>> OK, I give in. Do it the long way with the delimited varaibles
>>
>> Thanks
>>
>> Phil
>>
>> "Salad" wrote in message
>>
>> news:JtudndXDgbJnrWTanZ2dnUVZ_sOrnZ2d@earthlink.com...
>>
>> > Phil Stanton wrote:
>>
>> >> Hi Salad,
>>
>> >> Trying to get the function to pass 3 variables, BoatIDs, Boat Names
>> >> and
>> >> Boat Classes. I can easily get over the problem by calling 3 similar
>> >> functions to give the required outputs, but thought it might be
>> >> interesting to do it this way. Also thought the query might be faster
>> >> as
>> >> calling functions seems to slow a query by a factor of 100 or so
>>
>> >> Phil
>>
>> > rkc gave a great solution.
>>
>> > I'm not aware of a query being slowed down by a factor of 100. What
>> > I've
>> > seen is that you might get a repaint of the screen column as it
>> > calculates
>> > it as you pageup/dn thru it.
>>
>> > I think you'd be safe returning a field type recognized by a table. I
>> > doubt you'll get it to recognize an unrecognized/user-defined field
>> > type.
>> > Sometimes what we wish might exceed other's limits.
>>
>> > Sea
>> >http://www.youtube.com/watch?v=VnCS25z18pI
>>
>> >> "Salad" wrote in message
>> >>news:KLudnTVskLDmu2TanZ2dnUVZ_uevnZ2d@earthlink.com...
>>
>> >>>Phil Stanton wrote:
>>
>> >>>>I have a user defined type BoatDetails in a standard module
>>
>> >>>> Public Type BoatDetails
>> >>>> BtID As String
>> >>>> BtName As String
>> >>>> BtClass As String
>> >>>>End Type
>>
>> >>>>I am trying to call this function from a query using AK2000
>>
>> >>>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>
>> >>>>...
>> >>>>End function
>>
>> >>>>The function CombineBoatDetails(582).BtName returns the correct value
>> >>>>when viewed in the Immediate window.
>>
>> >>>>Why can't I key in BoatNames:CombineBoatDetails(MemberID).BtName in a
>> >>>>query grid. If I type it the SQL there is no problem, but the query
>> >>>>doesn't run.
>>
>> >>>>SELECT Member.MemberID, CombineBoatDetails(MemberID).BtName AS
>> >>>>BoatNames
>> >>>>FROM Member;
>>
>> >>>>If I type it into the query grid I get
>> >>>>"The expression you entered has an invalid . (dot) or ! operator or
>> >>>>invalid parenthesises"
>>
>> >>>>Am I doing something wrong, or is the theory correct that you can't
>> >>>>use
>> >>>>User Defined Typed in queries
>>
>> >>>I wouldn't think so. Tho I see nothing wrong with calling a function
>> >>>that would return values understood by the query.
>>
>> >>>You have
>> >>>Public Function CombineBoatDetails(MemID As Long) As BoatDetails
>>
>> >>>I'm not sure how a SQL statement would recongnize a structured/type
>> >>>variable. It's similar to the hyperlinkpart function.
>> >>>? hyperlinkpart("Display#Address",acAddress)
>> >>>return Address from the immediate window but chokes on it in a SQL
>> >>>statement. You have to supply the number 2 instead of acAddress in
>> >>>the
>> >>>query.
>>
>> >>>Why not have your function pass back a string? As In
>> >>>Public Function CombineBoatDetails(MemID As Long) As String
>>
>> >>>Then in the query enter
>> >>>CombineBoatDetails([MemberID]) AS BoatNames
>> >>>or from the query builder enter in the column
>> >>>BoatNames : CombineBoatDetails([MemberID])
>>
>> >>>The above will return a value it understands...like a string, a date,
>> >>>or
>> >>>a number. Passing back a type of variable type BoatDetails is most
>> >>>likely a type not understood by SQL.
>>
>> >>>TriggerHippie
>> >>>http://www.youtube.com/watch?v=8bQgFM3bNyw
>>
>> >>>>Thanks
>>
>> >>>>Phil
>
> Air Code ... should work
>
> Function CombineBoatDetails(MemID As Long)
>
> Dim aDetails(0 to 2)
>
> ' rest of function
>
> aDetails(0) = BoatIDs
> aDetails(1) = BoatNames
> aDetails(2) = BoatClasses
>
> CombineBoatDetails=aDetails
>
> End Function
>
> SELECT Member.MemberID, CombineBoatDetails(MemberID)(1) AS BoatNames
> FROM Member;

Re: Returning User Defined Type values to queries

Phil Stanton wrote:
> Thanks Lyle.
>
> Same problem Get
> The expression you entered has an invalid . (dot) or ! operator or invalid
> parenthesises"
> when I convert the SQL back to a query, highlighted at the start of the
> bracket round the (2)
>
> Phil
>
>
> "lyle" wrote in message
> news:69f462b1-07f8-4f08-ace3-ec080a93a285@d2g2000pra.googleg roups.com...

>> Air Code ... should work
>>
>> Function CombineBoatDetails(MemID As Long)
>>
>> Dim aDetails(0 to 2)
>>
>> ' rest of function
>>
>> aDetails(0) = BoatIDs
>> aDetails(1) = BoatNames
>> aDetails(2) = BoatClasses
>>
>> CombineBoatDetails=aDetails
>>
>> End Function
>>
>> SELECT Member.MemberID, CombineBoatDetails(MemberID)(1) AS BoatNames
>> FROM Member;

Even if it did work you would still have to call the function three
times to get all three values as separate fields.

Re: Returning User Defined Type values to queries

I now concatanate the 3 fields with a "#" delimiter after the BoatID and a
"^" after the boat names.. Call the function once in one query and use a
second query with lots in InStr expressions to extract the 3 fields.
Certainly faster than calling the function 3 times ....I think

Phil

"rkc" wrote in message
news:47f9663e$0$6134$4c368faf@roadrunner.com...
> Phil Stanton wrote:
>> Thanks Lyle.
>>
>> Same problem Get
>> The expression you entered has an invalid . (dot) or ! operator or
>> invalid parenthesises"
>> when I convert the SQL back to a query, highlighted at the start of the
>> bracket round the (2)
>>
>> Phil
>>
>>
>> "lyle" wrote in message
>> news:69f462b1-07f8-4f08-ace3-ec080a93a285@d2g2000pra.googleg roups.com...
>
>>> Air Code ... should work
>>>
>>> Function CombineBoatDetails(MemID As Long)
>>>
>>> Dim aDetails(0 to 2)
>>>
>>> ' rest of function
>>>
>>> aDetails(0) = BoatIDs
>>> aDetails(1) = BoatNames
>>> aDetails(2) = BoatClasses
>>>
>>> CombineBoatDetails=aDetails
>>>
>>> End Function
>>>
>>> SELECT Member.MemberID, CombineBoatDetails(MemberID)(1) AS BoatNames
>>> FROM Member;
>
> Even if it did work you would still have to call the function three times
> to get all three values as separate fields.
>
>
>

Re: Returning User Defined Type values to queries

Phil Stanton wrote:
> I now concatanate the 3 fields with a "#" delimiter after the BoatID and a
> "^" after the boat names.. Call the function once in one query and use a
> second query with lots in InStr expressions to extract the 3 fields.
> Certainly faster than calling the function 3 times ....I think
>

If your using an Access version greater than 97 take a look at the
Split() function in the VBA Help files. It does need to be as
complicated as you're making it.