Format Query Field As Memo

J

JLJones13

I have a query that derives a list of Recipients for a specified e-mail.
The Recipients are in a table and are linked through a GUID. The field
Recipient is data type Memo and the field RecipientDisplayName is text. If
I use both of these fields (as in the code below), my field won't go beyond
255 characters. If I only use the memo field Recipient the query field
returned is memo, also.



I have tried: CStr(), Trim(), Left() with the query line
RecptList(![MailboxMsgDetailsSentGUID]) and none of them make a
difference.



How can I make the query field Recipients a Memo field?



Thank you for your help!



Here's the SQL and the Function RecptList.....



SELECT S.MailboxMsgDetailsSentGUID, S.MailboxDN, S.MailboxEmail,
S.MessageID, S.Date, S.Subject, S.SizeKBytes,
RecptList(![MailboxMsgDetailsSentGUID]) AS Recipients

FROM dbo_T_MailboxMsgDetailsSent AS S;





Function RecptList(lMsgGUID As String) As String



Dim db As Database

Dim rs As Recordset

Dim sSQL As String

Dim sReturn As String



sSQL = "SELECT R.Recipient, R.RecipientDisplayName" & _

" FROM dbo_T_MailboxMsgDetailsSentRecips AS R" & _

" WHERE R.MailboxMsgDetailsSentGUID = " & lMsgGUID



Set db = CurrentDb()

Set rs = db.OpenRecordset(sSQL)



If rs.BOF And rs.EOF Then

sReturn = ""

Else

rs.MoveFirst

Do While Not rs.EOF

'if the display name is there, use it..

If Not IsNull(rs![RecipientDisplayName]) Then

sReturn = sReturn & rs![RecipientDisplayName] & ";" 'this
field is type TEXT

Else

'if not, extract the e-mail address and append it to the list.

sReturn = sReturn & FrmtMailName(rs![Recipient]) & ";" 'this
field is type MEMO

End If



rs.MoveNext

Loop

End If



rs.Close

db.Close



RecptList = sReturn



End Function
 
G

Gary Walter

Hi JL,

Isn't a *String* by definition 0-255 chars?

What happens if you make the following
two changes to your function
(swap "string" with "variant")?

Function RecptList(lMsgGUID As String) As Variant
Dim db As Database
Dim rs As Recordset
Dim sSQL As String
Dim sReturn As Variant

Plus, I don't understand the "bang"
instead of a period in

RecptList(.[MailboxMsgDetailsSentGUID]) AS Recipients


Good luck,

Gary Walter
 
R

Rick Brandt

Gary Walter said:
Hi JL,

Isn't a *String* by definition 0-255 chars?
Click to expand...

No. From Access 97 Help File

**************************************
There are two kinds of strings: variable-length and fixed-length strings.

· A variable-length string can contain up to approximately 2 billion (2^31)
characters.
· A fixed-length string can contain 1 to approximately 64K (2^16) characters.
 
G

Gary Walter

OK JL...let's try this again.

One possibility is that a critical
number of the *initial records* return
a string less than 255 so it "casts"
Recipients as "TEXT"

One way to test this would be to right
pad all strings returned by your function
with SPACE chars so all returned strings
will be greater than 255.

There are several ways to this. One way might
be to use this function.

'=============================================
'Right-Padding Function from Q210573
'The following function will right pad a string with a specified
'character. It accepts a base string which is to be right padded with
'characters, a character to be used as the pad character, and a
'length which specifies the total length of the padded result.
'To test this function,
'type the following line in the Immediate window,
'and then press ENTER:
'Print Rpad("123B", "0", 6)
'===============================================

Function Rpad(MyValue As String, _
MyPadCharacter As String, _
MyPaddedLength As Integer)
Rpad = MyValue & String(MyPaddedLength - Len(MyValue), _
MyPadCharacter)
End Function
'==============================================

and at end of your function, change last line to:

If Len(Trim(sReturn & ""))<256 Then
RecptList = Rpad(sReturn, " ", 256)
Else
RecptList = sReturn
End If

Obviously I have been wrong before,
and I could be wrong again.

Good luck,

Gary Walter

JLJones13 said:
I have a query that derives a list of Recipients for a specified e-mail.
The Recipients are in a table and are linked through a GUID. The field
Recipient is data type Memo and the field RecipientDisplayName is text. If
I use both of these fields (as in the code below), my field won't go beyond
255 characters. If I only use the memo field Recipient the query field
returned is memo, also.



I have tried: CStr(), Trim(), Left() with the query line
RecptList(![MailboxMsgDetailsSentGUID]) and none of them make a
difference.



How can I make the query field Recipients a Memo field?



Thank you for your help!



Here's the SQL and the Function RecptList.....



SELECT S.MailboxMsgDetailsSentGUID, S.MailboxDN, S.MailboxEmail,
S.MessageID, S.Date, S.Subject, S.SizeKBytes,
RecptList(![MailboxMsgDetailsSentGUID]) AS Recipients

FROM dbo_T_MailboxMsgDetailsSent AS S;





Function RecptList(lMsgGUID As String) As String



Dim db As Database

Dim rs As Recordset

Dim sSQL As String

Dim sReturn As String



sSQL = "SELECT R.Recipient, R.RecipientDisplayName" & _

" FROM dbo_T_MailboxMsgDetailsSentRecips AS R" & _

" WHERE R.MailboxMsgDetailsSentGUID = " & lMsgGUID



Set db = CurrentDb()

Set rs = db.OpenRecordset(sSQL)



If rs.BOF And rs.EOF Then

sReturn = ""

Else

rs.MoveFirst

Do While Not rs.EOF

'if the display name is there, use it..

If Not IsNull(rs![RecipientDisplayName]) Then

sReturn = sReturn & rs![RecipientDisplayName] & ";" 'this
field is type TEXT

Else

'if not, extract the e-mail address and append it to the list.

sReturn = sReturn & FrmtMailName(rs![Recipient]) & ";" 'this
field is type MEMO

End If



rs.MoveNext

Loop

End If



rs.Close

db.Close



RecptList = sReturn



End Function
Click to expand...
 
J

JLJones13

Thank you for replying to my question.

You are correct that the first few items returned by the function are less
than 255 chars. So, I tried padding the string returned by the function,
but the query still truncates the field. For example:
Doe, Jane Ms. <256 spaces> (returned by my function)
Doe, Jane Ms.<242 spaces> (in my query)

Just for good measure, I tried changing my return data type to Variant and I
changed the bang to a dot in my query line:
RecptList(.[MailboxMsgDetailsSentGUID]) AS Recipients

And it still insists on giving me 255 chars.

An additional note, and maybe this is just because it's Monday and nothing
seems to want to work on a Monday, when I used ONLY the Memo field in my
function, it would return a Memo field to the query. It was only when I
mixed text with the Memo that I was getting my data truncated. When I
returned to work today, though, my data was truncated whether or not I used
the Memo field.....

Thanks again for your ideas!


Gary Walter said:
OK JL...let's try this again.

One possibility is that a critical
number of the *initial records* return
a string less than 255 so it "casts"
Recipients as "TEXT"

One way to test this would be to right
pad all strings returned by your function
with SPACE chars so all returned strings
will be greater than 255.

There are several ways to this. One way might
be to use this function.

'=============================================
'Right-Padding Function from Q210573
'The following function will right pad a string with a specified
'character. It accepts a base string which is to be right padded with
'characters, a character to be used as the pad character, and a
'length which specifies the total length of the padded result.
'To test this function,
'type the following line in the Immediate window,
'and then press ENTER:
'Print Rpad("123B", "0", 6)
'===============================================

Function Rpad(MyValue As String, _
MyPadCharacter As String, _
MyPaddedLength As Integer)
Rpad = MyValue & String(MyPaddedLength - Len(MyValue), _
MyPadCharacter)
End Function
'==============================================

and at end of your function, change last line to:

If Len(Trim(sReturn & ""))<256 Then
RecptList = Rpad(sReturn, " ", 256)
Else
RecptList = sReturn
End If

Obviously I have been wrong before,
and I could be wrong again.

Good luck,

Gary Walter

JLJones13 said:
I have a query that derives a list of Recipients for a specified e-mail.
The Recipients are in a table and are linked through a GUID. The field
Recipient is data type Memo and the field RecipientDisplayName is text. If
I use both of these fields (as in the code below), my field won't go beyond
255 characters. If I only use the memo field Recipient the query field
returned is memo, also.



I have tried: CStr(), Trim(), Left() with the query line
RecptList(![MailboxMsgDetailsSentGUID]) and none of them make a
difference.



How can I make the query field Recipients a Memo field?



Thank you for your help!



Here's the SQL and the Function RecptList.....



SELECT S.MailboxMsgDetailsSentGUID, S.MailboxDN, S.MailboxEmail,
S.MessageID, S.Date, S.Subject, S.SizeKBytes,
RecptList(![MailboxMsgDetailsSentGUID]) AS Recipients

FROM dbo_T_MailboxMsgDetailsSent AS S;





Function RecptList(lMsgGUID As String) As String



Dim db As Database

Dim rs As Recordset

Dim sSQL As String

Dim sReturn As String



sSQL = "SELECT R.Recipient, R.RecipientDisplayName" & _

" FROM dbo_T_MailboxMsgDetailsSentRecips AS R" & _

" WHERE R.MailboxMsgDetailsSentGUID = " & lMsgGUID



Set db = CurrentDb()

Set rs = db.OpenRecordset(sSQL)



If rs.BOF And rs.EOF Then

sReturn = ""

Else

rs.MoveFirst

Do While Not rs.EOF

'if the display name is there, use it..

If Not IsNull(rs![RecipientDisplayName]) Then

sReturn = sReturn & rs![RecipientDisplayName] & ";" 'this
field is type TEXT

Else

'if not, extract the e-mail address and append it to the list.

sReturn = sReturn & FrmtMailName(rs![Recipient]) & ";" 'this
field is type MEMO

End If



rs.MoveNext

Loop

End If



rs.Close

db.Close



RecptList = sReturn



End Function
Click to expand...
Click to expand...
 
J

JLJones13

Whoops! It must be my brain that doesn't work on a Monday (go figure,
huh!). I was looking at the query in datasheet view and each time I copied
the contents out to check the number of chars, I highlighted the data
instead of selecting the whole cell. The padding worked :eek:)

Tks for your help!


JLJones13 said:
Thank you for replying to my question.

You are correct that the first few items returned by the function are less
than 255 chars. So, I tried padding the string returned by the function,
but the query still truncates the field. For example:
Doe, Jane Ms. <256 spaces> (returned by my function)
Doe, Jane Ms.<242 spaces> (in my query)

Just for good measure, I tried changing my return data type to Variant and I
changed the bang to a dot in my query line:
RecptList(.[MailboxMsgDetailsSentGUID]) AS Recipients

And it still insists on giving me 255 chars.

An additional note, and maybe this is just because it's Monday and nothing
seems to want to work on a Monday, when I used ONLY the Memo field in my
function, it would return a Memo field to the query. It was only when I
mixed text with the Memo that I was getting my data truncated. When I
returned to work today, though, my data was truncated whether or not I used
the Memo field.....

Thanks again for your ideas!


Gary Walter said:
OK JL...let's try this again.

One possibility is that a critical
number of the *initial records* return
a string less than 255 so it "casts"
Recipients as "TEXT"

One way to test this would be to right
pad all strings returned by your function
with SPACE chars so all returned strings
will be greater than 255.

There are several ways to this. One way might
be to use this function.

'=============================================
'Right-Padding Function from Q210573
'The following function will right pad a string with a specified
'character. It accepts a base string which is to be right padded with
'characters, a character to be used as the pad character, and a
'length which specifies the total length of the padded result.
'To test this function,
'type the following line in the Immediate window,
'and then press ENTER:
'Print Rpad("123B", "0", 6)
'===============================================

Function Rpad(MyValue As String, _
MyPadCharacter As String, _
MyPaddedLength As Integer)
Rpad = MyValue & String(MyPaddedLength - Len(MyValue), _
MyPadCharacter)
End Function
'==============================================

and at end of your function, change last line to:

If Len(Trim(sReturn & ""))<256 Then
RecptList = Rpad(sReturn, " ", 256)
Else
RecptList = sReturn
End If

Obviously I have been wrong before,
and I could be wrong again.

Good luck,

Gary Walter
Click to expand...
text.
If
I use both of these fields (as in the code below), my field won't go beyond
255 characters. If I only use the memo field Recipient the query field
returned is memo, also.



I have tried: CStr(), Trim(), Left() with the query line
RecptList(![MailboxMsgDetailsSentGUID]) and none of them make a
difference.



How can I make the query field Recipients a Memo field?



Thank you for your help!



Here's the SQL and the Function RecptList.....



SELECT S.MailboxMsgDetailsSentGUID, S.MailboxDN, S.MailboxEmail,
S.MessageID, S.Date, S.Subject, S.SizeKBytes,
RecptList(![MailboxMsgDetailsSentGUID]) AS Recipients

FROM dbo_T_MailboxMsgDetailsSent AS S;





Function RecptList(lMsgGUID As String) As String



Dim db As Database

Dim rs As Recordset

Dim sSQL As String

Dim sReturn As String



sSQL = "SELECT R.Recipient, R.RecipientDisplayName" & _

" FROM dbo_T_MailboxMsgDetailsSentRecips AS R" & _

" WHERE R.MailboxMsgDetailsSentGUID = " & lMsgGUID



Set db = CurrentDb()

Set rs = db.OpenRecordset(sSQL)



If rs.BOF And rs.EOF Then

sReturn = ""

Else

rs.MoveFirst

Do While Not rs.EOF

'if the display name is there, use it..

If Not IsNull(rs![RecipientDisplayName]) Then

sReturn = sReturn & rs![RecipientDisplayName] & ";" 'this
field is type TEXT

Else

'if not, extract the e-mail address and append it to the list.

sReturn = sReturn & FrmtMailName(rs![Recipient]) & ";" 'this
field is type MEMO

End If



rs.MoveNext

Loop

End If



rs.Close

db.Close



RecptList = sReturn



End Function
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting Query in UTF-8 format with VBA 0
Garbage in a string variable loaded from a field 2
can queries output be formatted as memo fields? 5
Using calculated query field in Recordset 5
Changing format of memo field to text 0

Top