Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unanswered: More Weird Dates

    Hi, Im using DCOUNT to find the number of records in a qry....
    this is the LINE:
    ChkClash = DCount("[IdTurno]", "qry_Turnos", "[HoraInicio] = #" & HoraInicial & "# AND [Fecha] = #" & DateNumber & "# AND [IdMedico] =" & MD)
    the strange thing is that it works only with dates >= 15 of each month.
    It means that 15/10/2002 will return records but 13/10/2002 will not.
    Any Ideas?
    I cant find the diff in the logic of dates
    Thanks in advance

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Try the format command, before placing the dates into the dlookup. For example:

    MyDate=Format(date-15,"Medium Date")

    Dlookup("[SomeDate]","TblMyTable","SomeDate=" & MyDate)

    I am not sure you need the # sign in Dlookup or not, you can modify the statement as required.

  3. #3
    Join Date
    Feb 2002
    Posts
    403
    Try the format command, before placing the dates into the dlookup. For example:

    MyDate=Format(date-15,"Medium Date")

    Dlookup("[SomeDate]","TblMyTable","SomeDate=" & MyDate)

    I am not sure you need the # sign in Dlookup or not, you can modify the statement as required.

    However, for the statement you are running you could consider SQL instead. Make a query and then copy the SQL statement from the query to your code.

  4. #4
    Join Date
    Jul 2002
    Posts
    3

    ReChecked

    Ive rechecked the problem and the issue is with dates lower or equal 12 so I guess the sql in the DCOUNT confuses month with days..
    Any Idea ?

  5. #5
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    The Jet DB engine's default date format is mm/dd/yyyy only if that doesn't apply it will try if other formats could be applicable. So if you have 01/02/2003 it will interprete that as the 2nd of January 2003. 13/02/2003 will however be interpreted as the 13th of February 2003 (as there's no month after December ).

    You should avoid handling dates in string format. The format function obeys regional settings and the output will differ from locale settings.
    Use the date data type within vba and when passing to queries (or the domain functions) convert it to double first. Jet internally handles dates as double format (where 1 is one day). There's also a nasty part with double though, you need to watch out for the decimal point character if it's ',' or anything and not the '.' in your locale as Jet only accepts the '.'. The CStr function obeys regional settings as well, so use the Str function instread.

    Your dlookup would look something like this:

    dim MyDate as Date
    MyDate=date
    Dlookup("[SomeDate]","TblMyTable","SomeDate=" & Str(CDbl(MyDate)))

  6. #6
    Join Date
    Jul 2002
    Posts
    3

    thanks

    Thanks for your kindly help.
    I ve started playin breakin dates with DAY MONTH YEAR so my qry strings are huge...........
    so.. thanks again

Posting Permissions

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