Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    what date is it? (Acces XP)

    I have a sql query of :
    select sum(Fpduration) from sickhist where staffid = 424 and startdate >= dateadd("yyyy",-1,startdate) and startdate < #10/7/2003#
    when i paste this into SQL view of a query it runs and retuns invalid data (ie not what i would expect).

    When i view the same query in design view the criteria for the query has changed the date value from #10/7/2003# to #7/10/2003#. This seem to be where the incorrect data retrieval happens.

    Can anyone please explain what is happening and how i can avoid it please? I am trying to set the SQL from VB so this change in format is transparent, i just get incorrect results in the form.

    thanks for any assistance you can offer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: what date is it? (Acces XP)

    The query design window uses the date format set in the Regional Settings (or Regional and Language Options) control panel. SQL always uses US format m/d/yyyy. So if your date setting is d/m/yyyy, October the 7th will be displayed as #7/10/2003# in design view, but as #10/7/2003# in SQL view. To put it another way, in design view you must enter dates according to your personal system settings, and in SQL view, you must enter dates the US way.

    If you assemble an SQL string in VBA code, you must ensure that dates are converted to US format. For example, if you want to refer to a date in a text box txtDate on a form:

    Dim strSQL As String
    strSQL = "SELECT * FROM MyTable WHERE MyDate < #" & Format(Me.txtDate, "m/d/yyyy") & "#"

    I hope this clarifies things; if not, don't hesitate to post back with more questions.

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: what date is it? (Acces XP)

    Many thanks that's cleared things up.

Posting Permissions

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