Solved

How to force date format

Posted on 2009-06-29
11
1,761 Views
Last Modified: 2012-05-11
I'm transfering data from Access to Excel and I am having trouble forcing the date to format as example: Oct-07.  I used the format in Excel, but it keeps converting to 10-07-2009 even through its correct in Access.  My thought is to use code to force it.  Need help with correct format.

'Force MM-D format
        objSht.Range("E7:Q155").Format ("mm d")
0
Comment
Question by:mato01
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
How are you transfering data from Access to Excel?

In Excel, try:
Range("E7:Q155").NumberFormat = "mm d"


About dates in Access:
Access stores all dates as doubles as the number of days since 12/30/1899 12:00:00 AM  (Format(0,"mm/dd/yyyy hh:nn:ss AM/PM") = 12/30/1899 12:00:00 AM).  For example 09/11/2005 10:56:40 AM is stored as 38606.4560185185 or 38606.4560185185 days since 12/30/1899 12:00:00 AM  (CDbl(#09/11/2005 10:56:40 AM#) = 38606.4560185185).  You can input and display the date/time in any format you choose.  

Time without a date will default to 12/30/1899 since the integer portion of the double is zero.

Also keep in mind that Access is an American product so VBA is sometimes expecting mm/dd/yyyy even when the regional setting is set for something else.  yyyy/mm/dd will always work for VBA input.

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
even though you may have a date in Access formatted as mmm-yy,  underlying the 'appearance' is the whole date
10-07-2009.

In Excel below I have =Today() in the first column, I formatted column B as mmm-yy, and column C as yyyy-mm-dd

2009-06-29      Jun-09      2009-06-29

While you might think that the column B date is stricltly Jun-09, by copying it to column C we see that it is actually the complete date for today.
0
 
LVL 5

Expert Comment

by:DBDevl
Comment Utility
In Excel highlight the cell(s) you want to format and right click.  Pick Format Cells.  On the Number tab in the Category pick Custom(at the bottom). Over to the right in the Type: box type in mmm-dd and click OK.
0
 

Author Comment

by:mato01
Comment Utility
Here is the code I'm using to transfer the data from my form to the excel.  THe date format is correct in the form Oct-07; however, when it transfers to Excel it changes to 10-07-2009.  I've formatted the template Excel file to Oct-07, but whenever I transfer it changes back to 10-07-2009.  This is why I thought I could code it to make it work. Any thoughts.
'--- note the use of the Option Explicit in the above line. forces all variables to be declared
 

Private Sub Command146_Click()
 

    Dim objXL As Excel.Application

    Dim objWkb As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim iRow As Integer

    Dim iRowS As Integer

    Dim rst As Recordset

    Dim db As Database

    

    DoCmd.Echo False
 

    '--- assumes use of DAO references.

    Set db = CurrentDb()

    

    Set rst = db.OpenRecordset("SELECT * FROM tblCodeAllocation ORDER BY Division;")  '--- open recordset to all data in table CustData

    

    If rst.EOF And rst.BOF Then   '--- if no data, stop

        MsgBox "No data to display"

        rst.Close

        Set rst = Nothing

        Exit Sub

    End If

    

    Set objXL = New Excel.Application  '--- start a new instance of Excel

    

    

    objXL.Visible = True

    Set objWkb = objXL.Workbooks.Open("C:\users\mato01\documents\CustList.xls")   '--- open workbook --- change to match where XLS template is

    Set objSht = objWkb.Worksheets(1)   '--- first tab in workbook

    DoCmd.Echo False

    iRow = 6          '--- first row to print data will be 3+1 (as rows 1,2,3 have column headers and page headers)

    iRowS = iRow + 1  '--- keep track of first row with data so we can do totals

    rst.MoveFirst

    

    

    Do While Not rst.EOF

        iRow = iRow + 1

       

        objSht.Range("A" & iRow) = rst!GMNA_NAIPC

        objSht.Range("B" & iRow) = rst!View

        objSht.Range("C" & iRow) = rst!C_T

        objSht.Range("D" & iRow) = rst!BookMgr

        objSht.Range("E" & iRow) = rst!Division

        objSht.Range("F" & iRow) = rst!Brand

        objSht.Range("G" & iRow) = rst!Buildout

        objSht.Range("H" & iRow) = rst!LastWkProd

        objSht.Range("I" & iRow) = rst!CutOffSEOPT

        objSht.Range("J" & iRow) = rst!CutOffSEOPT

        objSht.Range("K" & iRow) = rst!NAIPCCutoff

        objSht.Range("L" & iRow) = rst!DomesticCutOff

        objSht.Range("M" & iRow) = rst!QuickOrder

        objSht.Range("N" & iRow) = rst!OrderEntry

        objSht.Range("O" & iRow) = rst!StartUp

        objSht.Range("P" & iRow) = rst!OrderEstimate

        objSht.Range("Q" & iRow) = rst!AssemblyCenter

        

          

             

                

        rst.MoveNext

    Loop

    

    rst.Close

    

     'Change the Font

        objSht.Range("E7:Q155").Font.Bold = False

        objSht.Range("E7:Q155").Font.Name = "Arial"

        objSht.Range("E7:Q155").Font.Size = 8

              

         

    

   ' objSht.Range("A" & iRow + 1) = "TOTAL SALES"

   ' objSht.Range("C" & iRow + 1) = "=sum(C" & iRowS & ":C" & iRow & ")"

    

    '================================

    '--- finally, insert the date/time

   objSht.Range("E" & iRow + 4).Select

   objSht.Range("E" & iRow + 4) = Format(Now(), "mmm d, yyyy  hh:nn")

   

   
 

    Set objSht = Nothing

    Set objWkb = Nothing

    Set objXL = Nothing

    Set db = Nothing

    Set rst = Nothing

    

DoCmd.Echo True
 

MsgBox "Transfer Complete"
 

End Sub

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Do you want to wind up with a string or a bona fide date?  Any time you format a date in Excel, the underlying numeric value does not change, only the 'appearance' as I said in my previous post.  Are you going to tell us which field is the culprit?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:mato01
Comment Utility
Columns F thru P should be a string (date)  as you can type either a date or text in the form.
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
Under
     'Change the Font
add this line:
objSht.Range("E7:Q155").NumberFormat = "mm d"
0
 
LVL 39

Accepted Solution

by:
thenelson earned 50 total points
Comment Utility
<Columns F thru P should be a string (date)  as you can type either a date or text in the form.>

You can change the lines to (row F for example):
objSht.Range("F" & iRow) = Format (rst!Brand, "mm d")
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
If you want to see  mmm-dd then change the last line of nelson's solution above to

objSht.Range("E7:Q155").NumberFormat = "mmm-dd"
0
 

Author Closing Comment

by:mato01
Comment Utility
Worked.  Thanks
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now