Help with Type Declaration please

I have not played with 'Type's before, but want to use the same code to
combine Boat IDs, Boat Names and Class of boat where one person has more
than 1 boat

The code is below, and I type into the Immediate Window

?combineboatdetails(582).Btclass (582 is the MemberID who has 2
boats)

The procedure runs through correctly, and on the 3 lines before the stop
command I see the correct results :-

BoatDetail.BtID "165, 269"
BoatDetail.BtName "Joanne, Phantom"
BoatDetail.BtClass "National 12, Dinghy"

The problem is nothing is appearing in the immediate window. What Am I doing
wrong>

Thanks

Phil

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

Function CombineBoatDetails(MemID As Long) As BoatDetails

'Called from QAppendMemArchive. Combines boat names

Dim MyDb As Database
Dim BoatSet As Recordset
Dim SQLStg As String
Dim BoatIDs As String, BoatNames As String, BoatClasses As String
Dim BoatDetail As BoatDetails

SQLStg = "SELECT jnMemSpaceBoat.MemberID, Boat.BoatID, Boat.BoatName,
BoatClass.BoatClass "
SQLStg = SQLStg & "FROM (BoatClass INNER JOIN Boat ON
BoatClass.BoatClassID = Boat.BoatClassID) "
SQLStg = SQLStg & "INNER JOIN jnMemSpaceBoat ON Boat.BoatID =
jnMemSpaceBoat.BoatID "
SQLStg = SQLStg & "WHERE jnMemSpaceBoat.MemberID = " & MemID
SQLStg = SQLStg & " ORDER BY Boat.BoatName;"

Set MyDb = CurrentDb()
Set BoatSet = MyDb.OpenRecordset(SQLStg)

With BoatSet
Do Until .EOF
BoatIDs = BoatIDs & CStr(!BoatID) & ", "
BoatNames = BoatNames & !BoatName & ", "
BoatClasses = BoatClasses & !BoatClass & ", "
.MoveNext
Loop
.Close
Set BoatSet = Nothing
End With

If BoatNames = "" Then Exit Function

BoatIDs = Trim(BoatIDs)
BoatIDs = Left(BoatIDs, Len(BoatIDs) - 1)
BoatNames = Trim(BoatNames)
BoatNames = Left(BoatNames, Len(BoatNames) - 1)
BoatClasses = Trim(BoatClasses)
BoatClasses = Left(BoatClasses, Len(BoatClasses) - 1)

BoatDetail.BtID = BoatIDs
BoatDetail.BtName = BoatNames
BoatDetail.BtClass = BoatClasses
Stop
End Function

Re: Help with Type Declaration please

"Phil Stanton" wrote in
news:m9udnU7FMrgFGWvanZ2dnUVZ8q6onZ2d@plusnet:

> I have not played with 'Type's before, but want to use the same code
> to combine Boat IDs, Boat Names and Class of boat where one person has
> more than 1 boat
>
> The code is below, and I type into the Immediate Window
>
> ?combineboatdetails(582).Btclass (582 is the MemberID who has 2
> boats)
>
> The procedure runs through correctly, and on the 3 lines before the
> stop command I see the correct results :-
>
> BoatDetail.BtID "165, 269"
> BoatDetail.BtName "Joanne, Phantom"
> BoatDetail.BtClass "National 12, Dinghy"
>
> The problem is nothing is appearing in the immediate window. What Am I
> doing wrong>
>
> Thanks
>
> Phil
>
> Public Type BoatDetails
> BtID As String
> BtName As String
> BtClass As String
> End Type
>
> Function CombineBoatDetails(MemID As Long) As BoatDetails
>
> 'Called from QAppendMemArchive. Combines boat names
>
> Dim MyDb As Database
> Dim BoatSet As Recordset
> Dim SQLStg As String
> Dim BoatIDs As String, BoatNames As String, BoatClasses As String
> Dim BoatDetail As BoatDetails
>
> SQLStg = "SELECT jnMemSpaceBoat.MemberID, Boat.BoatID,
> Boat.BoatName,
> BoatClass.BoatClass "
> SQLStg = SQLStg & "FROM (BoatClass INNER JOIN Boat ON
> BoatClass.BoatClassID = Boat.BoatClassID) "
> SQLStg = SQLStg & "INNER JOIN jnMemSpaceBoat ON Boat.BoatID =
> jnMemSpaceBoat.BoatID "
> SQLStg = SQLStg & "WHERE jnMemSpaceBoat.MemberID = " & MemID
> SQLStg = SQLStg & " ORDER BY Boat.BoatName;"
>
> Set MyDb = CurrentDb()
> Set BoatSet = MyDb.OpenRecordset(SQLStg)
>
> With BoatSet
> Do Until .EOF
> BoatIDs = BoatIDs & CStr(!BoatID) & ", "
> BoatNames = BoatNames & !BoatName & ", "
> BoatClasses = BoatClasses & !BoatClass & ", "
> .MoveNext
> Loop
> .Close
> Set BoatSet = Nothing
> End With
>
> If BoatNames = "" Then Exit Function
>
> BoatIDs = Trim(BoatIDs)
> BoatIDs = Left(BoatIDs, Len(BoatIDs) - 1)
> BoatNames = Trim(BoatNames)
> BoatNames = Left(BoatNames, Len(BoatNames) - 1)
> BoatClasses = Trim(BoatClasses)
> BoatClasses = Left(BoatClasses, Len(BoatClasses) - 1)
>
> BoatDetail.BtID = BoatIDs
> BoatDetail.BtName = BoatNames
> BoatDetail.BtClass = BoatClasses
> Stop
> End Function

My guess is that
Dim BoatDetail As BoatDetails
is redundant and that

BoatDetail.BtID = BoatIDs
BoatDetail.BtName = BoatNames
BoatDetail.BtClass = BoatClasses

Might be:

CombineBoatDetails.BtID = BoatIDs
CombineBoatDetails.BtName = BoatNames
CombineBoatDetails.BtClass = BoatClasses

but I never use types except where required in an API function. If I were
doing this I would use GetRows to return a variant array from the
function, but I suppose the type thing has the advantage of named
members.

Re: Help with Type Declaration please

Spot on Lyle, now works fine in the Immediate Window, but ....

I am trying to call the function from a query using
BoatClass: CombineBoatDetails(MemberID).BtClass
but on typing the line in I get an error "The expression you entered has an
invalid . (dot) or ! operator or invalid parentheseses"
"You may have entered an invalid identifyer ot typed parentheses following
the Null constant."
The error is highlighted at the dot.

Any ideas please?

Thanks,

Phil

"lyle fairfield" wrote in message
news:Xns9A76AB047F6456666646261@216.221.81.119...
> "Phil Stanton" wrote in
> news:m9udnU7FMrgFGWvanZ2dnUVZ8q6onZ2d@plusnet:
>
>> I have not played with 'Type's before, but want to use the same code
>> to combine Boat IDs, Boat Names and Class of boat where one person has
>> more than 1 boat
>>
>> The code is below, and I type into the Immediate Window
>>
>> ?combineboatdetails(582).Btclass (582 is the MemberID who has 2
>> boats)
>>
>> The procedure runs through correctly, and on the 3 lines before the
>> stop command I see the correct results :-
>>
>> BoatDetail.BtID "165, 269"
>> BoatDetail.BtName "Joanne, Phantom"
>> BoatDetail.BtClass "National 12, Dinghy"
>>
>> The problem is nothing is appearing in the immediate window. What Am I
>> doing wrong>
>>
>> Thanks
>>
>> Phil
>>
>> Public Type BoatDetails
>> BtID As String
>> BtName As String
>> BtClass As String
>> End Type
>>
>> Function CombineBoatDetails(MemID As Long) As BoatDetails
>>
>> 'Called from QAppendMemArchive. Combines boat names
>>
>> Dim MyDb As Database
>> Dim BoatSet As Recordset
>> Dim SQLStg As String
>> Dim BoatIDs As String, BoatNames As String, BoatClasses As String
>> Dim BoatDetail As BoatDetails
>>
>> SQLStg = "SELECT jnMemSpaceBoat.MemberID, Boat.BoatID,
>> Boat.BoatName,
>> BoatClass.BoatClass "
>> SQLStg = SQLStg & "FROM (BoatClass INNER JOIN Boat ON
>> BoatClass.BoatClassID = Boat.BoatClassID) "
>> SQLStg = SQLStg & "INNER JOIN jnMemSpaceBoat ON Boat.BoatID =
>> jnMemSpaceBoat.BoatID "
>> SQLStg = SQLStg & "WHERE jnMemSpaceBoat.MemberID = " & MemID
>> SQLStg = SQLStg & " ORDER BY Boat.BoatName;"
>>
>> Set MyDb = CurrentDb()
>> Set BoatSet = MyDb.OpenRecordset(SQLStg)
>>
>> With BoatSet
>> Do Until .EOF
>> BoatIDs = BoatIDs & CStr(!BoatID) & ", "
>> BoatNames = BoatNames & !BoatName & ", "
>> BoatClasses = BoatClasses & !BoatClass & ", "
>> .MoveNext
>> Loop
>> .Close
>> Set BoatSet = Nothing
>> End With
>>
>> If BoatNames = "" Then Exit Function
>>
>> BoatIDs = Trim(BoatIDs)
>> BoatIDs = Left(BoatIDs, Len(BoatIDs) - 1)
>> BoatNames = Trim(BoatNames)
>> BoatNames = Left(BoatNames, Len(BoatNames) - 1)
>> BoatClasses = Trim(BoatClasses)
>> BoatClasses = Left(BoatClasses, Len(BoatClasses) - 1)
>>
>> BoatDetail.BtID = BoatIDs
>> BoatDetail.BtName = BoatNames
>> BoatDetail.BtClass = BoatClasses
>> Stop
>> End Function
>
> My guess is that
> Dim BoatDetail As BoatDetails
> is redundant and that
>
> BoatDetail.BtID = BoatIDs
> BoatDetail.BtName = BoatNames
> BoatDetail.BtClass = BoatClasses
>
> Might be:
>
> CombineBoatDetails.BtID = BoatIDs
> CombineBoatDetails.BtName = BoatNames
> CombineBoatDetails.BtClass = BoatClasses
>
> but I never use types except where required in an API function. If I were
> doing this I would use GetRows to return a variant array from the
> function, but I suppose the type thing has the advantage of named
> members.
>

Re: Help with Type Declaration please

"Phil Stanton" wrote in message

> The code is below, and I type into the Immediate Window
>
> ?combineboatdetails(582).Btclass (582 is the MemberID who has 2
> boats)

Hum, everything looks ok, but you have a space missing in th above after the
"?"

.....you need to go:

? combineboatdetails(582).Btclass

or

debug.Print combineboatdetails(582).Btclass


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Re: Help with Type Declaration please

> I am trying to call the function from a query using
> BoatClass: CombineBoatDetails(MemberID).BtClass
> but on typing the line in I get an error "The expression you entered has
> an invalid . (dot) or ! operator or invalid parentheseses"
> "You may have entered an invalid identifyer ot typed parentheses following
> the Null constant."
> The error is highlighted at the dot.
>
> Any ideas please?


It looks like you can't pull a user defined type into a query exprecssion.
You have to declar a public functon in standard code mode:

eg:

Public Function GBoatClass (id As Long) As Variant

gBid = CombineBoatDetails(id).Btclass

End Function

Then, in query builder go:

BoatClass: GBoatClass([MemberID])


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Re: Help with Type Declaration please

Hi Albert, The debug works fine you don't need a space after a ? works with
or without a space (in AK2000 anyway)

The problem is in trying to call the function from a query

Access won't let me type in "BoatClass:
CombineBoatDetails(MemberID).BtClass" (No inverted commas) it gives the
error about the invalid dot.

Nasty feeling you are right about not being able to use a user defined type
in a query. Looks as though I will have to call a similar routine 3 times to
get the 3 combined bits of information


Thanks,

Phil


"Albert D. Kallal" wrote in message
news:ktxJj.155256$pM4.127463@pd7urf1no...
> "Phil Stanton" wrote in message
>
>> The code is below, and I type into the Immediate Window
>>
>> ?combineboatdetails(582).Btclass (582 is the MemberID who has 2
>> boats)
>
> Hum, everything looks ok, but you have a space missing in th above after
> the "?"
>
> ....you need to go:
>
> ? combineboatdetails(582).Btclass
>
> or
>
> debug.Print combineboatdetails(582).Btclass
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
>
>

Re: Help with Type Declaration please

Phil Stanton wrote:
> I have not played with 'Type's before, but want to use the same code to
> combine Boat IDs, Boat Names and Class of boat where one person has more
> than 1 boat
>
> The code is below, and I type into the Immediate Window
>
> ?combineboatdetails(582).Btclass (582 is the MemberID who has 2
> boats)
>
> The procedure runs through correctly, and on the 3 lines before the stop
> command I see the correct results :-
>
> BoatDetail.BtID "165, 269"
> BoatDetail.BtName "Joanne, Phantom"
> BoatDetail.BtClass "National 12, Dinghy"
>
> The problem is nothing is appearing in the immediate window. What Am I doing
> wrong>
>
> Thanks
>
> Phil
>
> Public Type BoatDetails
> BtID As String
> BtName As String
> BtClass As String
> End Type
>
> Function CombineBoatDetails(MemID As Long) As BoatDetails
>
> 'Called from QAppendMemArchive. Combines boat names
>
> Dim MyDb As Database
> Dim BoatSet As Recordset
> Dim SQLStg As String
> Dim BoatIDs As String, BoatNames As String, BoatClasses As String
> Dim BoatDetail As BoatDetails
>
> SQLStg = "SELECT jnMemSpaceBoat.MemberID, Boat.BoatID, Boat.BoatName,
> BoatClass.BoatClass "
> SQLStg = SQLStg & "FROM (BoatClass INNER JOIN Boat ON
> BoatClass.BoatClassID = Boat.BoatClassID) "
> SQLStg = SQLStg & "INNER JOIN jnMemSpaceBoat ON Boat.BoatID =
> jnMemSpaceBoat.BoatID "
> SQLStg = SQLStg & "WHERE jnMemSpaceBoat.MemberID = " & MemID
> SQLStg = SQLStg & " ORDER BY Boat.BoatName;"
>
> Set MyDb = CurrentDb()
> Set BoatSet = MyDb.OpenRecordset(SQLStg)
>
> With BoatSet
> Do Until .EOF
> BoatIDs = BoatIDs & CStr(!BoatID) & ", "
> BoatNames = BoatNames & !BoatName & ", "
> BoatClasses = BoatClasses & !BoatClass & ", "
> .MoveNext
> Loop
> .Close
> Set BoatSet = Nothing
> End With
>
> If BoatNames = "" Then Exit Function
>
> BoatIDs = Trim(BoatIDs)
> BoatIDs = Left(BoatIDs, Len(BoatIDs) - 1)
> BoatNames = Trim(BoatNames)
> BoatNames = Left(BoatNames, Len(BoatNames) - 1)
> BoatClasses = Trim(BoatClasses)
> BoatClasses = Left(BoatClasses, Len(BoatClasses) - 1)
>
> BoatDetail.BtID = BoatIDs
> BoatDetail.BtName = BoatNames
> BoatDetail.BtClass = BoatClasses
> Stop
> End Function

I don't see where you are assigning the return value of the function.
Either CombineBoatDetails = BoatDetails or as Lyle suggests cut out the
middle man a just assign the values to CombineBoatDetails.

Re: Help with Type Declaration please

No problem with the code now. Have adopted Lyle's solution

Problem is getting a query to let me type in
BoatClass: CombineBoatDetails(MemberID).BtClass

gives an error "The expression you entered has an invalid . (dot) or !
operator or invalid parentheseses"
Theory is that you can't use a 'user defined type' in a query.
Thanks

Phil

"rkc" wrote in message
news:47f7631e$0$1089$4c368faf@roadrunner.com...
> Phil Stanton wrote:
>> I have not played with 'Type's before, but want to use the same code to
>> combine Boat IDs, Boat Names and Class of boat where one person has more
>> than 1 boat
>>
>> The code is below, and I type into the Immediate Window
>>
>> ?combineboatdetails(582).Btclass (582 is the MemberID who has 2
>> boats)
>>
>> The procedure runs through correctly, and on the 3 lines before the stop
>> command I see the correct results :-
>>
>> BoatDetail.BtID "165, 269"
>> BoatDetail.BtName "Joanne, Phantom"
>> BoatDetail.BtClass "National 12, Dinghy"
>>
>> The problem is nothing is appearing in the immediate window. What Am I
>> doing wrong>
>>
>> Thanks
>>
>> Phil
>>
>> Public Type BoatDetails
>> BtID As String
>> BtName As String
>> BtClass As String
>> End Type
>>
>> Function CombineBoatDetails(MemID As Long) As BoatDetails
>>
>> 'Called from QAppendMemArchive. Combines boat names
>>
>> Dim MyDb As Database
>> Dim BoatSet As Recordset
>> Dim SQLStg As String
>> Dim BoatIDs As String, BoatNames As String, BoatClasses As String
>> Dim BoatDetail As BoatDetails
>>
>> SQLStg = "SELECT jnMemSpaceBoat.MemberID, Boat.BoatID, Boat.BoatName,
>> BoatClass.BoatClass "
>> SQLStg = SQLStg & "FROM (BoatClass INNER JOIN Boat ON
>> BoatClass.BoatClassID = Boat.BoatClassID) "
>> SQLStg = SQLStg & "INNER JOIN jnMemSpaceBoat ON Boat.BoatID =
>> jnMemSpaceBoat.BoatID "
>> SQLStg = SQLStg & "WHERE jnMemSpaceBoat.MemberID = " & MemID
>> SQLStg = SQLStg & " ORDER BY Boat.BoatName;"
>>
>> Set MyDb = CurrentDb()
>> Set BoatSet = MyDb.OpenRecordset(SQLStg)
>>
>> With BoatSet
>> Do Until .EOF
>> BoatIDs = BoatIDs & CStr(!BoatID) & ", "
>> BoatNames = BoatNames & !BoatName & ", "
>> BoatClasses = BoatClasses & !BoatClass & ", "
>> .MoveNext
>> Loop
>> .Close
>> Set BoatSet = Nothing
>> End With
>>
>> If BoatNames = "" Then Exit Function
>>
>> BoatIDs = Trim(BoatIDs)
>> BoatIDs = Left(BoatIDs, Len(BoatIDs) - 1)
>> BoatNames = Trim(BoatNames)
>> BoatNames = Left(BoatNames, Len(BoatNames) - 1)
>> BoatClasses = Trim(BoatClasses)
>> BoatClasses = Left(BoatClasses, Len(BoatClasses) - 1)
>>
>> BoatDetail.BtID = BoatIDs
>> BoatDetail.BtName = BoatNames
>> BoatDetail.BtClass = BoatClasses
>> Stop
>> End Function
>
> I don't see where you are assigning the return value of the function.
> Either CombineBoatDetails = BoatDetails or as Lyle suggests cut out the
> middle man a just assign the values to CombineBoatDetails.
>
>

Re: Help with Type Declaration please

Phil Stanton wrote:
> No problem with the code now. Have adopted Lyle's solution
>
> Problem is getting a query to let me type in
> BoatClass: CombineBoatDetails(MemberID).BtClass
>
> gives an error "The expression you entered has an invalid . (dot) or !
> operator or invalid parentheseses"
> Theory is that you can't use a 'user defined type' in a query.
> Thanks
>

I would not think that Jet would recognize a UDT.

I think you have already been given the solution.

Use a function that returns a variable of a type Jet can
handle. You can certainly build that function using your UDT.
You just can't return the UDT or reference a member of a UDT
in the query.