[AccessD] adhbCalendar returns INCORRECT value?! Extra info SOLVED

Hi Sander

> THANK YOU!!!!!
> When copy-pasted the code and ran the code I got a very big smile on my ugly face!

Very good!

> WHY in G's name does this work?

1. Even if you format a textbox as date it still contains nothing more
than a string or Null. Access tries at its best to turn that string
into a date/time value but sometimes it gets confused if you include
the value of the textbox in code. CDate() forces a conversion to a
date/time value as early as possible.

2. Date/time values don't exist in SQL code other than by a string
representation. If you include a date/time value in an SQL string in
code, Access tries to convert this value to a string but it uses your
local settings to create that string; this means that for most
developers outside the US, the string will be formatted as dd/mm/yyyy
or similar.
However, the format must be in US format including the crazy time
format with AM/PM if a time value is present. Format() can do that
right away if you know how and that's Lembit's trick; to Format(), the
slash "/" doesn't mean a slash but the date separator which, as to
your local settings, may be replaced with a hyphen or a dot. Adding
the backslash tells Format() to read and return the slash as a slash.

If you are writing a lot of SQL code, this function may come in handy
which also takes care of your hash marks "#":

<code>

Public Function StrDateSQL(ByVal dat As Date) As String

' Formats full string of date/time in US format for SQL.
' Overrides local (non US) settings for date/time separators.
' Example output:
'
'   #08/16/1998 04:03:36 PM#
'
' 1999-10-21. Cactus Data ApS, CPH.

  StrDateSQL = Format(dat, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#")

End Function

</code>

/gustav


> I would check two things.

> 1. The input looks like coming from a textbox. If a Debug.Print shows
> wrongly translated dates, try:

> Call CalculateDays(CDate(txtStartDatum), CDate(txtEindDatum), arrDates())

> 2. Date expression in SQL must be US formatted according to the Lembit
> method:

> strWhere =
> "WHERE a.date = #" & Format(dtmCurrDate, "mm\/dd\/yyyy") & "# "