MS Access 97 Report detail line truncated when outputting to DOS text file

Memo fields automatically truncate spaces at end.

You will have to create a custom built function to output the data in the format you want.

Use the Open Statement method to create a text file
Use a Recordset to loop through your Memo detail line data from your table.
Use the Print #1 statement to write each line to the text file eg  

    strOutput = strMemoline & Space(maxrecordlength - Len(strMemoline))
    Print #1, strOutput

obviously once you have come to the end of the table, close the file with the Close Statement

Thank you mccredb for replying so quickly, much appreciated.

I don't think I made my question very clear. It is not just truncating the spaces at the end of the Memo field, it is also truncating data as well as spaces.

The "Open Statement" method looks as though it might work for my project and I will attempt to use this today.

If you or anyone else can think of any other way I would be most grateful.

Many Thanks

Memo field are difficult to deal with because they can be HUGE.  Users love them, developers (all the ones I've worked with) hate them.  In addition, if not managed correctly, they are a prime target for database corruption.

Try buffering the content of the memo field into a Variant datatype variable.  From there you can manipulate it further.  Remember that memo fields can have CrLfs in them.  

If you want to flush out CrLfs you can:
  MemoVar = Replace(MemoVar, vbcrlf,"  ")

If you want to limit the output to the last XX characters you can
  Right(MemoVar,255)

You just have to be careful not to change the original content as part of your routine.

Thanks jadedata.

Using a Memo field wasn't my first choice. The first thing I tried was using two 255 character Text fields which I then combined using a Query that the Report ran off.

I had the same problem of truncation..!!

Thanks for taking the time to post a response.

Hello mccredb.

Unfortunately I can't use your suggested method as I require my users to send me the reports as an attachment in an email and having a flat file on their PC doesn't make it easy to send in automatically using MS Access.

I still require a solution to the truncation problem or a method of sending a file as an attachment with the "SentTo" action.

Thanks once again for your helpful suggestions so far.

 

Interesting point jadedata, unfortunately the Replace function was introduced in A2000 & doesnot exist in A97

As jadedata suggests the truncation problem may have been new line characters.  However if you were using a Report to format your data and were exporting it as RTF format, that is a pretty good way of not getting what you want.  Access Reports are useless when it comes to generating precisely formated files.

Search EE for "Public Function Replace(" for a Acc97 version written by one of our finer Experts.  It will serve to replace the missing function in Acc97.  Just add it to your code "toolbox"

The IO Read function can be finicky about Commas AND CrLfs, and guess what users love to put into memo fields???  Pushing the whole memo field into a var allows you to "tweek" it a little before you push it into your text file.

dale,  I have had the same dilema myself.

It is possible to get Access to attach a file to a mail message but you will have to use CDO

link to the Access web site which shows how
http://www.mvps.org/access/modules/mdl0019.htm

It is a bit advanced and maybe going to a complexity level further than you want to go with this.  Unfortunately for me I couldn't use it due PC environment restrictions.

In the end I just displayed an empty email with the SendObject function, with body text saying 'file XYZ saved to ABC. please attach to this email and send'

As an alternative you may want to talk to your mainframe support people to see if there is a location your Access program can save the file to, from which they can then ConnectDirect/JSO/NDM or FTP the file directly onto your mainframe.

Hello mccredb.

he he he. You are spot on with the clues! Thanks once again mccredb.

I found this web site 3 hours ago and couldn't get the code to work because I couldn't find the Reference to
"Microsoft CDO 1.21" under Tools/References.

I also got a piece of code from a colleague who was surfing that I have managed to get to work, and yes it looks as though I will have to go down this complicated route. It's a horrible solution to a fairly simple problem but I can't think of any other ways around this baby.

To get the code to work I had to go to Tools/References and tick the "Microsoft Outlook 8.0 Object Library" box. This includes the objects such as "Outlook.Application" and "Outlook.MailItem" used in the code.

Seems to work fine.

The code is:

Private Sub Command0_Click()
'Arvin Meyer 03/12/1999
'Updated 7/21/2001
On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
    .To = "info@datastrat.com"
    .Subject = "Look at this sample attachment"
    .body = "The body doesn't matter, just the attachment"
    .Attachments.Add "C:\Test.htm"
    '.attachments.Add "c:\Path\to\the\next\file.txt"
    .Send
    '.ReadReceiptRequested
End With

Exit_Here:
    Set objOutlook = Nothing
    Exit Sub
   
Error_Handler:
    MsgBox Err & ": " & Err.Description
    Resume Exit_Here

End Sub


The "ConnectDirect/JSO/NDM or FTP" is a nice idea but what I didn't tell you because I didn't want to confuse matters was that the Access database is for our Brokers to send in new business and the only communication we have with them is by email.

Thank you mccredb for not giving up after first glance. You deserve the points when I get this working..!!

Dale

 

daletrotman:  I you feel you have a solution, and that solution was not derived from the possibles provided by Experts, you should feel comfortable asking a Mod in Community Support to refund your points.  Experts would just ask you to detail that solution out here so that others can benefit from your findings.

OK?
Jack

You might as well Dale.  Its not as if Expert Points are worth anything anyway.  Used to be the case that if you gained enough points you could access the knowledge base (KPro) for free.

mc:  MSKB might have all the poop, but it isn't quite and interactive as EE, or as much fun!!

well, it's been a while but I thought I should close this one off.
thanks to all who contributed.

Dale


the code I used for a remote user to send in email data goes as follows...

Option Compare Database
Option Explicit
Dim referenceStatus As String

1) This first bit of code looks to see if the Broker has the reference to the Microsoft Outlook Library. If he has I create a reference to it.

Function SendMain() As Boolean
 
    If fIsFileDIR("C:\Program Files\Microsoft Office\Office\msoutl8.olb") = True Then
        Select Case ReferenceFromFile("C:\Program Files\Microsoft Office\Office\msoutl8.olb")
            Case "Created"
                referenceStatus = "Created"
            Case "Already There"
                referenceStatus = "Already There"
            Case "Problem"
                referenceStatus = "Problem"
        End Select
    ElseIf fIsFileDIR("C:\Program Files\Microsoft Office\Office\msoutl9.olb") = True Then
        Select Case ReferenceFromFile("C:\Program Files\Microsoft Office\Office\msoutl9.olb")
            Case "Created"
                referenceStatus = "Created"
            Case "Already There"
                referenceStatus = "Already There"
            Case "Problem"
                referenceStatus = "Problem"
        End Select
    ElseIf fIsFileDIR("C:\Program Files\Microsoft Office\Office10\msoutl.olb") = True Then
        Select Case ReferenceFromFile("C:\Program Files\Microsoft Office\Office10\msoutl.olb")
            Case "Created"
                referenceStatus = "Created"
            Case "Already There"
                referenceStatus = "Already There"
            Case "Problem"
                referenceStatus = "Problem"
        End Select
    Else
         referenceStatus = "Problem"
    End If
           
    Select Case referenceStatus
        Case "Created"
            SendEmail
            RemoveReference
            SendMain = True
        Case "Already There"
            SendEmail
            SendMain = True
        Case "Problem"
            MsgBox "No References to outlook libraries are found. The email cannot be sent"
            SendMain = False
    End Select
   
    If referenceStatus = "Email Failure" Then
        SendMain = False
    End If
   
End Function

2) The fIsFileDIR function tests to see if the file exists...

Function fIsFileDIR(stPath As String, Optional lngType As Long) As Boolean
    On Error Resume Next
    fIsFileDIR = Len(Dir(stPath, lngType)) > 0
End Function

3) The reference to the Library is created like this...

Function ReferenceFromFile(strFileName As String) As String
    Dim ref As Reference
   
    On Error GoTo Error_ReferenceFromFile
    ' Create new reference.
    Set ref = References.AddFromFile(strFileName)
    ReferenceFromFile = "Created"

Exit_ReferenceFromFile:
    Exit Function

Error_ReferenceFromFile:
    'Is the reference already there?
    If Err = 32813 Then
        ReferenceFromFile = "Already There"
    Else
        MsgBox Err & ": " & Err.Description
        ReferenceFromFile = "Problem"
    End If
    Resume Exit_ReferenceFromFile
End Function

4) And to remove the reference...

Function RemoveReference() As Boolean
    Dim ref As Reference

    On Error GoTo Error_RemoveReference
    ' Remove calendar control reference.
    Set ref = References!Outlook
    References.Remove ref
    RemoveReference = True

Exit_RemoveReference:
    Exit Function

Error_RemoveReference:
    MsgBox Err & ": " & Err.Description
    RemoveReference = False
    Resume Exit_RemoveReference
End Function

5) The function to create the file and email it...

Function SendEmail()
    Dim ol As New Outlook.Application
    Dim ns As Outlook.NameSpace
    Dim newMail As Outlook.MailItem
    Dim errObj As Long
    Dim errMsg As String

    On Error GoTo Error_Handler

    'Return a reference to the MAPI layer.
    Set ns = ol.GetNamespace("MAPI")

    'Create the temp attachment.
    TransferText

    'Create a new mail message item.
    Set newMail = ol.CreateItem(olMailItem)
    With newMail
        'Add the subject of the mail message.
        .Subject = "Incoming Transmissions from  " & [Reports]![qryTransmission]![Scheme Code]
        'Create some body text.
        .Body = "Version 2.00 (10/9/2003)" & vbCrLf

        'Add a recipient and test to make sure that the
        'address is valid using the Resolve method.
        With .Recipients.Add("person@place.co.uk")
            .Type = olTo
            If Not .Resolve Then
                MsgBox "Unable to resolve address.", vbInformation
                Exit Function
            End If
        End With

        'Attach a file.
        With .Attachments.Add(CurrentDBDir() & "temp.txt")
            .DisplayName = [Reports]![qryTransmission]![email heading]
        End With

        'Send the mail message.
        .Send
    End With

Exit_Here:
    'Release memory.
    Set ol = Nothing
    Set ns = Nothing
    Set newMail = Nothing
    Exit Function
   
Error_Handler:
    errObj = Err - vbObjectError  ' Strip out the OLE automation error.
    referenceStatus = "Email Failure"
        Select Case errObj
            Case 275                    ' User cancelled sending of message.
                errMsg = MsgBox("The Email was not sent.")
                Resume Exit_Here
            Case 285                    'User cancelled logon.
                errMsg = MsgBox("Error " & errObj & " was returned. The Email was not sent. " & Err.Description)
                Resume Exit_Here
            Case Else
                errMsg = MsgBox("Error " & errObj & " was returned. The Email was not sent." & Err.Description)
                Resume Exit_Here
            End Select
           
End Function

6) The transfer text function referenced above is as follows...

Private Sub TransferText()
    DoCmd.TransferText acExportFixed, "Standard Report Spec", "qryTransmission", CurrentDBDir() & "temp.txt"
End Sub

7) It creates "temp.txt" in the same folder as the database. The location is in CurrentDBDir() which follows...

Function CurrentDBDir() As String
    Dim strDBPath As String
    Dim strDBFile As String

    strDBPath = CurrentDb.Name
    strDBFile = Dir(strDBPath)
    CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
   
End Function



and that's it. A pretty complicated solution to a simple problem. If it's any use to anyone I'm pleased.

Thanks mccredb.

regards
Dale