0

How do I add a code to this that would make the format stick exactly like it is in Access into excel?

I tried following this link but it is not working :/ Exporting to excel loses the date format

This is my VBA.

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click

Dim stDocName As String

stDocName = "q_Reg_Disp_Rpt_by_CMS_Reg_Due_Date"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "q_Reg_Disp_Rpt_by_CMS_Reg_Due_Date", "Excel Workbook (*.xlsx)", , True

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub
Erik A
  • 31,639
  • 12
  • 42
  • 67
Holly Cathcart
  • 93
  • 1
  • 1
  • 10
  • Write a query based on the query you want export and apply the format you want using the Format function. – Don Jewett Jul 30 '15 at 20:03
  • This actually works perfect however I have one field that I used the format function but the parameter (or criteria) won't work :/ So should it be formatted as well or ? I mean if I put in 1/1/2015 and 1/31/2015 as for Between [Enter Start Date] And [Enter End Date], it won't filter to the date range....just pulled everything....make sense? – Holly Cathcart Jul 30 '15 at 20:26
  • post your query in your question please (or best, a truncated version of it with the relevant fields) – Don Jewett Jul 30 '15 at 20:36
  • (truncated) SELECT m.mREF, Format([Opened],"mm/dd/yyyy") AS [Recvd Date], Format([CreatedDate],"General Date") AS [Created Date], Format([RegDue],"mm/dd/yyyy") AS [Regulatory Due Date], Format([RegCompleteDate],"mm/dd/yyyy") AS [Regulatory Complete Date], Format([Completed],"mm/dd/yyyy") AS [Completed Date] FROM ((((((((((tblMain AS m WHERE (((Format([RegDue],"mm/dd/yyyy")) Between [Enter Start Date] And [Enter End Date])); – Holly Cathcart Jul 30 '15 at 20:45
  • remove the format from the where clause: ([RegDue]) Between [Enter Start Date] And [Enter End Date]. To do this in the editor, add the field a second time, but uncheck the show box. Move your criteria to this unformatted field. – Don Jewett Jul 30 '15 at 21:34
  • Works!!!!! Thank YOU, Dan Jewett!!!! How do I check your answer as "THE" Answer here??!! – Holly Cathcart Jul 30 '15 at 21:42

2 Answers2

0

Please refer to following link which may help you in solving your problem.

@Mdlueck suggested approach in access programmers forum is reproduced below. I also feel It may work for your problem

The way to keep formatting is to:

  1. 1) Create a empty spreadsheet with all of the formatting in place

2).In Access VBA, make a copy of that template file to the target filename (I use the MSO SaveAs dialog box to accept a target filename), and make a copy of the template to that filename

3). Then use this suggestion to transfer the data to the spreadsheet:

[How to drive Excel with VBA (Access) in order to transfer values into spreadsheet.]http://www.access-programmers.co.uk/...04#post1190025

skkakkar
  • 2,772
  • 2
  • 17
  • 30
0

Use the Format Function, which returns a string:

SELECT KeyField, Format([DateField],"mm/dd/yyyy") AS [DateString]

Note any criteria should use the original date field and not the formatted string

WHERE [DateField] >= #1/1/2015#

References

Don Jewett
  • 1,867
  • 14
  • 27