Solved

Excel 2007 VBA update Access 2007 date field

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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