Search code examples
sqlms-accessvbams-access-2003

What is the default date format for access input?

I am not really experienced with ms-access db. I have the following sql-code that ask for input from the user: 

SELECT c.the_date, E.[iD #] AS ID, E.[first name] AS fname
      FROM [date-table] AS c, employeetbl AS E
WHERE c.the_date between [enter a beginning date] and [Enter an end date];
  • What format should the input date from the user be? (mm-dd-yyyy or dd-mm-yyyy)
  • Is mm-dd-yyyy format for date type in ms access by default?
  • Can I also determine the default format? or can I somehow change this using either access vba or sql.

  • I also want to keep looping until the user inputs the right date format. Is this possible in vba?

Any help will be appreciated.

Solution

  • you can use Format to ensure the date is entered in the correct format to the table: i.e Format (Date, "yyyy-mm-dd").
    However, ensuring the user enters the correct format may be a little tricky, since '2015-03-09' may be March 9th or September 3rd. For the user to enter a date that can't be ambiguous I guess the best format is something like dd-mmm-yyyy (ie. 09-mar-2015 or 03-sep-2015).