Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2455
  • Last Modified:

Word mail merge showing 12:00:00AM

I have a spreadsheet that is used as the data file for a word mail merge.  This is done in vba.   Everything merges over just fine, except 1 column that keeps send over 12:00:00AM even though the spreadsheet data looks something like "Monthy Pet Fee - $20.00"

I have 2 columns like this that are updated by a recordset do while, and the merge field names are nonRent1, nonRent2, nonRent3.   nonRent1 comes out great, nonRent2 looks like the same data in nonRent1 but prints as 12:00:00am on the word document.  I have deleted the column and added it, I have changed the column format to text, and I don't know why I cant get this to work.   I have the code that creates the data and fills the fields, and then also the merge code to show you.  Hope someone can help.

thanks
 
    ' create a new file based on the merge document
    Set doc = wrdApp.documents.Add(sPathFileTemplate)
    
    ' Hide Word (actually just make sure it doesn't show)
    wrdApp.Visible = True

'    ' Connect the datasource to the merge document
   doc.MailMerge.OpenDataSource Name:=ActiveWorkbook.FullName, LinkToSource:=True, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM `MergeRecords$`"
  
    ' Execute the merge
    With doc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
'        With .DataSource
'            .FirstRecord = wdDefaultFirstRecord
'            .LastRecord = wdDefaultLastRecord
'        End With
        .Execute Pause:=False
    End With
'
'    Dim docloop As Document
'
'
'
'    For Each docloop In wrdApp.documents
'        myName = docloop

Open in new window



 rs.Open strSQL, con, adOpenStatic
      c = 37
    
        If rs.RecordCount > 0 Then
              Do Until rs.EOF
               Dim myItem As String
               myItem = CStr(rs(2)) & Space(1) & "-" & Space(1) & CStr(FormatCurrency(rs("amtofcharge")))
                Workbooks.Item(2).Sheets("MergeRecords").Cells(myline, c) = myItem
                c = c + 1
                rs.MoveNext
            Loop
        Else
            Workbooks.Item(2).Sheets("MergeRecords").Cells(myline, c) = "Not Applicable"
        End If
    

Open in new window

0
mgmhicks
Asked:
mgmhicks
  • 2
1 Solution
 
GrahamSkanRetiredCommented:
It's not clear what value is added by using VBA.

I think that we need to see the Mail Merge main document and a sample of the datasource.
0
 
mgmhicksAuthor Commented:
thank you for the response.  As it turned out I added code to put a least a space in the field, rather than leave it blank and that worked.  Apparently with the field/column blank it looked at the column as something else, but when I started putting in = space(1), in the column then it started working for all the columns.  Something to keep in mind for the future.
0
 
GrahamSkanRetiredCommented:
Excel columns don't have a data type as such, but the cells do have NumberFomat property which MaiMerge  would take as a clue.
0
 
Helen FeddemaCommented:
12:00:00 AM is usually what you get for a blank Date field.  Sometimes you get the whole date, usually 12/30/1899, which can be disconcerting in a document.  It seems that Excel is somehow processing this cell as a Date value.  There are often data type conversion problems between Access and Excel, especially with dates.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now