Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Access Dates (Access 2000)

    Does Access have a problem with dates? I have a form into which dates must be entered and in order for the query to work, the dates must be entered in American format (mm-dd-yy), even though in reports and other places it's in Australian format (dd-mm-yy).

    Is this changeable? Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Access Dates (Access 2000)

    When you use Access interactively, you should be able to enter and edit dates according to the setting in the Regional Settings control panel - dd-mm-yy in your case.

    Both VBA and SQL expect literal dates to be in US date format mm/dd/yy, so if you plug a date from a text box on the form into an SQL statement or Where condition generated in code, you will have to convert the date. For example: you have a text box txtOrderDate on a form. You want to open a report rptOrders with only those records whose OrderDate correspond to the date entered on the form. The VBA instruction for this is:

    DoCmd.OpenForm ReportName:="rptOrders", View:=acViewPreview, _
    WhereCondition:="[OrderDate] = #" & Format(Me.txtOrderDate, "mm/dd/yy") & "#"

    The Format function ensures that the date is in US format; even though the user enters it in Oz format.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts