Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to force date format

Posted on 2009-06-29
11
Medium Priority
?
1,824 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 24737202
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
ID: 24737339
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
ID: 24737365
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mato01
ID: 24737497
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
ID: 24737604
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
 

Author Comment

by:mato01
ID: 24737751
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
ID: 24737771
Under
     'Change the Font
add this line:
objSht.Range("E7:Q155").NumberFormat = "mm d"
0
 
LVL 39

Accepted Solution

by:
thenelson earned 200 total points
ID: 24737854
<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
ID: 24737861
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
ID: 31597970
Worked.  Thanks
0
 
LVL 39

Expert Comment

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

Happy Computing!

Nelson
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 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