Query Export - How, now that query has changed 1

[thefourthwall]

I export 2 fields of data from an existing query. The query is

SELECT tblDevice.EID, tblDevice.Cell_Number
FROM tblDevice
WHERE tblDevice.EID IS NOT NULL AND tblDevice.EID NOT LIKE '[A-Z]*';

This data is exported to a text file by

  'Begin variable declarations for Export Cell Numbers & EID's
  
  Dim strFilePath As String 'For the file path of your exported file - where you want to save it.
  Dim strFileName As String 'For the actual file name of your exported workbook.
  Dim strQuery As String 'Your Query Name
  
    strFilePath = "G:\Share\IS\WSU\Administrative\Cell_Phone_Database\Exported\"
    strFileName = "EIDs_and_CellNumbers" & ".txt"
    strQuery = "qryEportEID_Phone_Number"
    
    DoCmd.TransferText acExportDelim, "QryEportEID_Phone_Number Export Specification", _
    strQuery, strFilePath & strFileName, False

UpdateData_Exit:
    Exit Function

UpdateData_Err:
    MsgBox Error$
    Resume UpdateData_Exit

End Function

Now management wants additional data exported, so I changed the query to

SELECT tblDevice.EID, Mid([cell_number],1,3) & "-" & Mid([cell_number],4,3) & "-" & Mid([cell_number],7,4) AS Expr1, EMPLOYEE.AD_USER_NAME
FROM tblDevice INNER JOIN EMPLOYEE ON tblDevice.EID = EMPLOYEE.EMPLOYEE_ID
WHERE (((tblDevice.EID) Is Not Null And (tblDevice.EID) Not Like '[A-Z]*'));

I've only exported existing fields; what's the best way to export "Expr1"?

 

[oharab]

You need to change "QryEportEID_Phone_Number Export Specification" to include your extra field, or change your code to

DoCmd.TransferText acExportDelim, "", _
    strQuery, strFilePath & strFileName, False

so that all fields are exported in a default format.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[thefourthwall]

Exactly the right idea Ben! I renamed the query with today's date appended and went through the export process to create a new specifications file and modified the vba code accordingly. Is there another way of getting at an existing specification file to edit it, or is exporting the query to create a new specification file the best way?

Thank you, for your quick response! The new process works beautifully.

 

[oharab]

Glad it worked. Thanks for the star.
AFAIK the only way to change an existing specification is to reimport a file.

Cheers

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.