Query Expressions

[vicktown]

I have created an expression in a query using an IIF statement. But i am having a problem because the text is being truncated. The information coming in is set to Memo type but a character length is being set once it comes into the query expression. How can i get around this and stop the information from being cut off?

 

[BNPMike]

Can you post the SQL?

 

[vicktown]

The portion of the SQL that includes this expresssion is here:


IIf(IsNull([tblParagraph]![TEXT]),"The text for this paragraph has not yet been entered into the databse",[tblParagraph]![TEXT]) AS Expr3


tblParagrpah.TEXT is of Memo type but is being truncated.

 

[PHV]

Have you tried this ?
Nz(tblParagraph.TEXT, "The text for this paragraph has not yet been entered into the databse") AS Expr3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vicktown]

Yes i have tried this, thanks you for your efforts but i am still getting the same problem. It seems that these expressions are turnign a memo type into a 255 character text type.
Any more ideas?

 

[BNPMike]

See your point. If you do TST:[TEXT] you get a memo. If you do an IIF, you get a text. Can't immediately see an answer apart from do two passes into a memo field.

 

[BNPMike]

How could i use a TST:[TEXT] to attempt to solve this problem?

. You can't. I was just pointing out if you assign a value to another field it preserves the format but not if you introduce IIF.

My solution is to update/insert all the values WHERE TEXT Is Not Null then insert/update "The text for this paragraph has not yet been entered into the databse",for all records WHERE TEXT Is Null.

 

[lespaul]

can you use that TST:[TEXT] within a UNION query?

Leslie

 

[BNPMike]

That's just SELECT TEXT AS TST FROM SOMETABLE;

I can't find any definition of IIF. Access may be good but its documentation is disorganised, unprofessional and incomplete.

 

[TomCologne]

BNPMike is absolutely right. The 'Help' file in Acc2k and up hardly deserves the name, Acc97 was much better.

Try your IIf statement or query with a text field on a form.

I've had a similar issue with a db supposed to extract data from emails, based on certain keywords in the mail, in specified OL folders.
Mails are imported into a temp table, mail body into memo field [Contents].

Records appear to be cut off in any qry, too, but will be displayed completely in a form's text field.

In my case,

Tab = Chr(9)
LineFeed = Chr(10)
CarriageReturn = Chr(13)

are causing the problem, CarriageReturn being the worst.

Using an expression, or multiple expressions, like

FindCarriageRepl([Contents])

in a qry returns a compacted string that's searchable for keywords.

Some assembly required:

'[URL unfurl="true"]http://support.microsoft.com/default.aspx?scid=kb;en-us;210433&Product=acc2000[/URL]
'Code modified
'==================================================================
' The following functions will:
'   - Find the specified characters in a Text or Memo field.
'   - Call another function to replace them.
'==================================================================

Function FindCarriageRepl(WhichField As String) As String
   Dim intCounter As Integer
   Dim strText As String
   Dim intstart As Integer
   
   intstart = 1
   intCounter = 1
   strText = WhichField
   
   Do Until intCounter = 0
      ' Chr(13) is the Carriage character.
      ' Replace Chr(13) with the ANSI code for the character
      ' you are searching for.
      intCounter = InStr(intstart, strText, Chr(13))
      intstart = intCounter + 1
      If intCounter > 0 And Not IsNull(intCounter) Then
         strText = ReplaceCarriage(intCounter, strText)
         '*********************
         ''--Chr(167) § /Chr(13)
         '*********************
      End If
   Loop
   
   FindCarriageRepl = strText
End Function

Function FindLineFeedRepl(WhichField As String) As String
   Dim intCounter As Integer
   Dim strText As String
   Dim intstart As Integer
   
   intstart = 1
   intCounter = 1
   strText = WhichField
   
   Do Until intCounter = 0
      ' Chr(10) is the LineFeed character.
      ' Replace Chr(10) with the ANSI code for the character
      ' you are searching for.
      intCounter = InStr(intstart, strText, Chr(10))
      intstart = intCounter + 1
      If intCounter > 0 And Not IsNull(intCounter) Then
         strText = ReplaceLineFeeds(intCounter, strText)
         '*********************
         ''--Chr(216) Ø /Chr(10)
         '*********************
      End If
   Loop
   
   FindLineFeedRepl = strText
End Function

Function FindTabsRepl(WhichField As String) As String
   Dim intCounter As Integer
   Dim strText As String
   Dim intstart As Integer
   
   intstart = 1
   intCounter = 1
   strText = WhichField
   
   Do Until intCounter = 0
      ' Chr(9) is the Tab character.
      ' Replace Chr(9) with the ANSI code for the character
      ' you are searching for.
      intCounter = InStr(intstart, strText, Chr(9))
      intstart = intCounter + 1
      If intCounter > 0 And Not IsNull(intCounter) Then
         strText = ReplaceTabs(intCounter, strText)
         '*********************
         ''--Chr(191) ¿ /Chr(9)
         '*********************
      End If
   Loop
   
   FindTabsRepl = strText
End Function

'==================================================================
' The following functions are called from the functions above . They
' accept two arguments, intStart and strText. The functions replace specified characters
' with characters you choose and return the updated text.
'==================================================================
Function ReplaceCarriage(intstart As Integer, strText As String) As String
   ' Replace % with the character you want to substitute.
   '--Chr(191) ¿ /Chr(9)
   '--Chr(216) Ø /Chr(10)
   '--Chr(167) § /Chr(13)
   Mid(strText, intstart, 1) = Chr(32) 'Chr(167)
   ReplaceCarriage = strText
End Function

Function ReplaceLineFeeds(intstart As Integer, strText As String) As String
   ' Replace % with the character you want to substitute.
   '--Chr(191) ¿ /Chr(9)
   '--Chr(216) Ø /Chr(10)
   '--Chr(167) § /Chr(13)
   Mid(strText, intstart, 1) = Chr(32) 'Chr(216)
   ReplaceLineFeeds = strText
End Function

Function ReplaceTabs(intstart As Integer, strText As String) As String
   ' Replace % with the character you want to substitute.
   '--Chr(191) ¿ /Chr(9)
   '--Chr(216) Ø /Chr(10)
   '--Chr(167) § /Chr(13)
   Mid(strText, intstart, 1) = Chr(32) ' Chr(191)
   ReplaceTabs = strText
End Function


'==================================================================
' ' Function StripString()
'[URL unfurl="true"]http://support.microsoft.com/default.aspx?scid=kb;en-us;210227[/URL]
'Code modified
'------------------------------------------------------------------
' Returns a string minus a set of specified chars.
' Function only removes only (<> replace), may result in a long string !
'==================================================================
Function StripString(MyStr As Variant) As Variant
   On Error GoTo StripStringError

   Dim strChar As String, strHoldString As String
   Dim i As Integer

   ' Exit if the passed value is null.
   If IsNull(MyStr) Then Exit Function

   ' Exit if the passed value is not a string.
   If varType(MyStr) <> 8 Then Exit Function

   ' Check each value for invalid characters.
   For i = 1 To Len(MyStr)
      strChar = Mid$(MyStr, i, 1)
      Select Case strChar
      'Chr(9)= Tab
      'Chr(10)= LineFeed
      'Chr(13)= CarriageReturn
      'Chr(34)= "
      'Chr(60)= <
      'Chr(62)= >
         Case Chr$(13), Chr$(10), Chr$(9), Chr$(34), Chr$(60), Chr$(62)   'also accepts, eg: ".", "#", ",", "-"
            ' Do nothing
         Case Else
            strHoldString = strHoldString & strChar
      End Select
   Next i

   ' Pass back corrected string.
   StripString = strHoldString

StripStringEnd:
   Exit Function

StripStringError:
   MsgBox Error$
   Resume StripStringEnd
End Function

Maybe you can somehow use it to solve your problem,

TomCologne