Results 1 to 3 of 3
  1. #1
    niki09 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2014
    Posts
    10

    US format Dates posted to a table from SQL when using visual basic

    My table has a Date field format Short Date. Record is added to the table without a date.
    SQL is run to update the null date records with a prompt in the query of [Enter the date].
    I am in Australia, and the date format is dd/mm/yyyy.

    When using VB to develop the sql using Input Box to offer a default date, this works, but the date loaded against the record is in US format of mm/dd/yyyy.

    Example, it is 6th December.
    I enter 6/12/2014 with the SQL prompt query. Access posts it as 6/12/2014 which in long date is Dec 6 (correct)

    Using VB, Access offers 6/12/2014, but Access posts it as 12/6/2014, which in long date is June 6 (incorrect).

    The question is really using VB, Access turns the around to US format.

    Dim Message, Title, Default, MyDate, sql
    Message = "Enter the date (yesterday's is given)"


    Title = "Date" '
    Default = Format(Date - 1, "dd/mm/yyyy")
    MyDate = InputBox(Message, Title, Default, 100, 100)


    sql = "UPDATE Results SET Results.MeetingDate = #" & MyDate & "#, Results.Course = [enter name of race course] WHERE (((Results.MeetingDate) Is Null) AND ((Results.Course) Is Null));"
    Debug.Print sql
    DoCmd.RunSQL sql

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review http://allenbrowne.com/ser-36.html

    Allen Browne is Australian so he knows what he's talking about on this topic.
    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
    niki09 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2014
    Posts
    10
    Sir, thank you for this. This has been a puzzle for me for years. I first used Access when it was version 2.0 (earliest version I can recall).
    I have other questions, and know where to come for help.
    Thank you again.

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

Similar Threads

  1. Visual Basic
    By Darth21 in forum Access
    Replies: 4
    Last Post: 08-11-2014, 06:18 AM
  2. Visual Basic Code for If Else
    By Baldeagle in forum Programming
    Replies: 1
    Last Post: 09-14-2012, 08:46 AM
  3. Visual Basic
    By kwooten in forum Queries
    Replies: 2
    Last Post: 01-25-2012, 08:22 AM
  4. Replies: 1
    Last Post: 01-21-2011, 11:32 AM
  5. Can't see visual basic code
    By Michele Keck in forum Forms
    Replies: 5
    Last Post: 09-29-2010, 11:31 AM

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