How to force date format

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")
mato01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thenelsonCommented:
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
GRayLCommented:
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
DBDevlCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mato01Author Commented:
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
GRayLCommented:
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
mato01Author Commented:
Columns F thru P should be a string (date)  as you can type either a date or text in the form.
0
thenelsonCommented:
Under
     'Change the Font
add this line:
objSht.Range("E7:Q155").NumberFormat = "mm d"
0
thenelsonCommented:
<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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
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
mato01Author Commented:
Worked.  Thanks
0
thenelsonCommented:
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.