Results 1 to 7 of 7
  1. #1
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39

    Date formatting in VBA - MS Access linked table to SQL Server Table

    We have an old MS Access database that we moved to SQL Server but we’re still using the Access forms for the front end until we can get a web front end built. I’m having a hard time trying to get the dates formatted correctly when being sent to the SQL database via the linked tables.

    Date shows up like this


    • Date: 12/30/1899
    • Year: 0


    I bolded the code below where I'm trying to get the date part completed, Any help is appreciated and thanks in advance.

    Code:
        TerminatedYear = DatePart("yyyy", [Date]) 
        FormattedDate = Year(Date)
       
        Dim conn As ADODB.Connection
        Dim strSQL As String
        Dim LDate As String
       
            Set conn = CurrentProject.Connection
           
            strSQL = "INSERT INTO termDate_Tracking(FacilityID,terminationDate,TermYear) VALUES(" & _
                FacilityID & ", " & FormattedDate & ", " & TerminatedYear & ")"
           
            conn.Execute strSQL
               
            conn.Close
            Set conn = Nothing
     
            Me.Requery

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the finished SQL look like? What are the data types of the 3 fields?

    BaldyWeb-Immediate window
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    12/30/1899 is a zer0 date. The start of all dates, there's nothing there.
    If you want to FORMAT an existing date, use: FORMAT([datefld],"mm/dd/yyyy")
    set it for any format you want.

  4. #4
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    FacilityID is an int
    FormattedDate is just a date field for the current date, Date(). field name: terminationDate(dateime2(7), null)
    TerminatedYear is just a text field which gets the current year from Date(). termYear(varchar(255), null)

    I think I'm looking at this wrong since SQL Server does m/d/y : minutes correct?

    At this point I would just like to get the formatted date and terminated year in the system the ID already works fine.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wouldn't bother storing the TermYear, since it can always be derived from the full date. You're peeling out just the year to put in a full date field, which doesn't seem reasonable. Does this work?

    strSQL = "INSERT INTO termDate_Tracking(FacilityID,terminationDate) VALUES(" & _
    FacilityID & ", #" & Date() & "#)"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    Thank you very much that worked perfectly!

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 12-30-2015, 10:33 PM
  2. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  3. Replies: 1
    Last Post: 04-23-2012, 03:41 PM
  4. Replies: 0
    Last Post: 03-08-2012, 03:12 AM
  5. Replies: 3
    Last Post: 01-03-2012, 12:28 PM

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