Results 1 to 4 of 4
  1. #1
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20

    Use VBA to update a date in table

    Hello,

    I'm using the following code to 1) import all csv tables that exist in a folder into table RPT0185, 2) ask user for month ending date (which doesn't exist in the individual csv tables), and 3)update the Month_Ending field in the table with the user input. It runs through, but when the user inputs 4/30/2020 (as an example) it shows up in the table as 12/30/1899. The Month_Ending field in the table is defined as data type Date/Time with format Short Date. I tried both the current and commented out command below with the same results (in red). Also, unrelated question...when should I use Public Sub vs Public Function?

    Public Sub importRPT0185()


    Dim FileName, FilePathName, Path, FileNameList() As String
    Dim FileCount As Integer
    Dim MonthEnding As Date
    Dim MonthEndingMsg As String


    DoCmd.SetWarnings False
    Path = "C:\Users\jamichaels\OneDrive - Highlights Family of Companies\Documents\Magazine JE\Current RPT0185A"
    FileName = Dir(Path & "")


    While FileName <> "" And Right(FileName, 3) = "csv"
    FileCount = FileCount + 1
    ReDim Preserve FileNameList(1 To FileCount)
    FileNameList(FileCount) = FileName
    FileName = Dir()
    Wend

    If FileCount > 0 Then
    For FileCount = 1 To UBound(FileNameList)
    FilePathName = Path & FileNameList(FileCount)
    DoCmd.TransferText transferType:=acImportDelim, TableName:="RPT0185", FileName:=FilePathName, hasfieldnames:=True
    Next
    End If

    MonthEndingMsg = "Enter month ending date."
    MonthEnding = InputBox(Prompt:=MonthEndingMsg, title:="Month Ending")
    'CurrentDb.Execute "UPDATE RPT0185 SET [Month_Ending] = " & MonthEnding
    DoCmd.RunSQL "UPDATE RPT0185 SET [Month_Ending] = " & MonthEnding



    DoCmd.SetWarnings True


    MsgBox "Done"


    End Sub

    Thanks!
    JM

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In future, please post lengthy code between CODE tags to retain indentation and readability.

    Inputs for date/time field must be delimited with # character.

    CurrentDb.Execute "UPDATE RPT0185 SET [Month_Ending] = #" & MonthEnding & "#"

    Problem with InputBox is cannot easily validate user input. Would be better to have input into a textbox.

    Use a Function when need to return a value to a calling procedure. Or if need to call procedure from a control or macro.



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    Dang, you're good! Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SetWarnings not needed with CurrentDb.Execute method.

    If you want to make user enter a date value and validate input and not allow them to cancel, consider:

    Code:
    Dim MonthEnding As Variant
    ...
    MonthEnding = "Month Ending"
    Do While MonthEnding = "Month Ending" Or Not IsDate(MonthEnding)
        MonthEnding = InputBox(MonthEndingMsg, "Month Ending")
    Loop
    Unfortunately, Cancel button cannot be removed.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-22-2019, 02:26 PM
  2. Update table based on closest match on date
    By dalahans in forum Queries
    Replies: 6
    Last Post: 06-07-2018, 07:50 AM
  3. Update table based on Max date link
    By janmack79 in forum Queries
    Replies: 2
    Last Post: 04-11-2017, 12:46 PM
  4. cannot update date in table
    By vojinb in forum Queries
    Replies: 7
    Last Post: 12-16-2011, 03:24 AM
  5. cannot update date in table
    By vojinb in forum Import/Export Data
    Replies: 6
    Last Post: 12-15-2011, 02:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums