Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    2

    Unanswered: where clause date format issue

    I'm new to dbforums so if I something is not according to rules, please notify in your respond.

    In my form I want to count the number of records from a table (subform) linked to the form by 3 fields.
    The code below works except for the 3rd line; [shift_start] which has a date/time format (dd-mm-yy hh:nn:ss AM). What have I done wrong ?

    str = "(([ID-number]=""" & Me.ID_number & """)"
    str = str & " AND (Line=""2""))"
    str = str & " AND (shift_start= #" + Me.shift_start + "#)" 'here's an error!

    i = DCount("[controle_ID]", "tbl_line_con_det_sub2", str)

    Any help is appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Access only handles Date/Time value in the US format (mm/dd/yyyy) or iso format (yyyy-mm-dd). Try:
    Code:
    str = str & " AND (shift_start= #" & Format(Me.shift_start, "yyyy-mm-dd") & "#)" 'here's an error!
    Also pay attention to the fact that the string concatenation operator is the ampersand (&), not the plus sign (+), although the last one works most of the time, there are situations where its use will yieds results you do not expect.
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    2

    solved!

    Quote Originally Posted by Sinndho View Post
    Access only handles Date/Time value in the US format (mm/dd/yyyy) or iso format (yyyy-mm-dd). Try:
    Code:
    str = str & " AND (shift_start= #" & Format(Me.shift_start, "yyyy-mm-dd") & "#)" 'here's an error!
    Also pay attention to the fact that the string concatenation operator is the ampersand (&), not the plus sign (+), although the last one works most of the time, there are situations where its use will yieds results you do not expect.
    This works all right. Thank you.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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