Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2007 VBA update Access 2007 date field

Posted on 2012-03-21
2
Medium Priority
?
518 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
[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
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

715 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