Solved

Excel 2007 VBA update Access 2007 date field

Posted on 2012-03-21
2
502 Views
Last Modified: 2012-08-13
I have an access database table with a date field among about 50 other fields.  When the entire record is originally created, this particular date field may or may not be entered.  Subsequently, I will need to update this field with a date.  When I do this from Excel VBA, for example, trying to update with 3/13/2012, the field is populated with 12/29/1899 every time.  If I run the query from inside Access, the field populates correctly.  My VBA code is below:

Sub UpdateCDRBPCD()

    Dim DBConnection1 As New ADODB.Connection
    Dim rgInput As Range
    Dim rgInputCell As Range
    Dim rowCounter As Long
    Dim intCounter As Integer
    Dim intDealno As Integer
    Dim SQL1 As String
    Dim EnrollFTDT As String
    Dim FolderLoc As String
    
    FolderLoc = Sheets("Conversion").Range("F4")
        
    Sheets("BPCD").Select

    DBConnection1.Provider = "Microsoft Jet 4.0 OLE DB Provider"
    DBConnection1.Properties("Jet OLEDB:System database") = "" & FolderLoc & "\Security.mdw"
    DBConnection1.ConnectionString = "Data Source=" & FolderLoc & "\MMCD.mdb;User ID=user1;Password=password1;"
    DBConnection1.Open

    rowCounter = 5
    Do While Cells(rowCounter, 2) <> ""
        rowCounter = rowCounter + 1
    Loop
    
    Set rgInput = Range(Cells(5, 2), Cells(rowCounter - 1, 2))
    
        intCounter = 0

    For Each rgInputCell In rgInput
    
    If Sheets("BPCD").Range("AU5").Offset(intCounter, 0) = "No" Then
    
    Else
         
    EnrollFTDT = Format$(Sheets("BPCD").Range("AK5").Offset(intCounter, 0), "mm/dd/yyyy")
    intDealno = Sheets("BPCD").Range("H5").Offset(intCounter, 0)
            
    SQL1 = "UPDATE MMCD SET Enrollment_File_Transfer = #" & EnrollFTDT & "# and Update_UserID = 'updateuser' where [PPT #] = " & intDealno & ""
        
    DBConnection1.Execute SQL1
    
    End If
    
         intCounter = intCounter + 1
    
    Next rgInputCell
    
    DBConnection1.Close
    
End Sub

Open in new window

0
Comment
Question by:pmcd2012
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 37748051
In line 40 replace the word  'and' with a comma
0
 

Author Comment

by:pmcd2012
ID: 37748081
Ok, now I feel like a noob at this.  Thanks, works like a charm.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

820 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