renaming query field (1 Viewer)

[Eljefegeneo]

I have a query field that is currently called PO. I want to rename it P.O.# because that is the name of the field in Quickbooks to which it will be transferred. I can rename it PO# but when I try to rename it P.O.#, the error message says this is not a valid name. The pertinent part of the SQL statement is:

  tblTempAccounting.PO AS [PO#]

Apparently the "." is what is causing the problem. I know that I can put P.O.# in the caption property, but when I use theSentTQ2Excel module (slightly modified), It reverts back to PO#.

  Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String)
  ' strTQName is the name of the table or query you want to send to Excel
  ' strSheetName is the name of the sheet you want to name it to
      
      Dim rst As DAO.Recordset
      Dim ApXL As Object
      Dim xlWBk As Object
      Dim xlWSh As Object
      Dim fld As DAO.Field
      Const xlCenter As Long = -4108
      Const xlBottom As Long = -4107
      On Error GoTo Err_Handler
      Set rst = CurrentDb.OpenRecordset(strTQName)
      Set ApXL = CreateObject("Excel.Application")
      Set xlWBk = ApXL.Workbooks.Add
      ApXL.Visible = True
          
      Set xlWSh = xlWBk.Worksheets("Sheet1")
      If Len(strSheetName) > 0 Then
          xlWSh.Name = Left(strSheetName, 34)
      End If
   
      xlWSh.Activate
      xlWSh.Range("A1").Select
   
      For Each fld In rst.Fields
          ApXL.ActiveCell = fld.Name
          ApXL.ActiveCell.Offset(0, 1).Select
      Next
      rst.MoveFirst
      xlWSh.Range("A2").CopyFromRecordset rst
      xlWSh.Range("1:1").Select
      ' This is included to show some of what you can do about formatting.  You can comment out or delete
      ' any of this that you don't want to use in your own export.
      With ApXL.Selection.Font
          .Name = "Calibri"
          .Size = 11
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
      End With
      'ApXL.Selection.Font.Bold = True
      'ApXL.Selection.Font.Bold = False
      With ApXL.Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .MergeCells = False
      End With
      ' selects all of the cells
      ApXL.ActiveSheet.Cells.Select
      ' does the "autofit" for all columns
      ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
      ' selects the first cell to unselect all cells
      xlWSh.Range("A1").Select
      rst.Close
      Set rst = Nothing
      Dim myPath, myFile As String
      If (Len(Dir("P:\"))) Then
      myPath = "P:\Gene\"
      Else
      myPath = "C:\Users\Gene\Documents\AccessDBWordDocs\"
      End If
      'myPath = "C:\Users\Gene\Documents\AccessDBWordDocs\"
      myFile = Format(Date, "MMDYYYY") & " " & "Billing"
      xlWBk.SaveAs myPath & myFile
      Exit Function
  Err_Handler:
      DoCmd.SetWarnings True
      MsgBox Err.Description, vbExclamation, Err.Number
      Exit Function
  End Function

Is there any way to change the field name to P.O.#?

 

[pbaldy]

In the loop that sets the field names in the header row, you can test for RO# and use your desired text instead, else the actual name.

 

[Eljefegeneo]

Thank you for your almost instant reply. But my level of expertise stopped at "In the". I think I have some vague understanding of what you are saying, but have no idea of how to go about it.

Do you mean in this loop:?

[FONT=&quot]For Each fld In rst.Fields[/FONT]
[FONT=&quot] ApXL.ActiveCell = fld.Name[/FONT]
[FONT=&quot] ApXL.ActiveCell.Offset(0, 1).Select[/FONT]
[FONT=&quot] Next[/FONT]


And if so, would it be something like

If fld.name = PO then 
fld.name = P.O.

Would this be in the loop code or after it?

 

[pbaldy]

Close. Something like

For Each fld In rst.Fields
  If fld.name = "PO" Then 
    ApXL.ActiveCell = "DesiredText"
  Else
    ApXL.ActiveCell = fld.Name
  End If
  ApXL.ActiveCell.Offset(0, 1).Select
Next

 

[Eljefegeneo]

Thank you . Another lesson learned. It did exactly what I wanted.

 

[pbaldy]

Happy to help!