Solved

Excel 2007 VBA update Access 2007 date field

Posted on 2012-03-21
2
489 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 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

14 Experts available now in Live!

Get 1:1 Help Now