Apologies for posting a question that has been asked and answered many
times but its been causing me a lot of grief!

I'm using the following code to prepare a (UK) date from a form to be
inserted into a table:

If IsDate(dFindSite) Then
If DateValue(dFindSite) = dFindSite Then
FindSite = Format$(dFindSite, "\#dd\/mm\/yyyy\#")
End If
End If

I've used this many times having found the above code on the list
previously, however now i get the error "Type-declaration character
does not match declared data type"

Any ideas where i'm going wrong?
Thanks for any suggestions.
Paul

Paul, here's my experience from working with Access in a dmy country:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Internally, the value is a special kind of real number, where the integer
part represents the day, and the fraction the time (as a fraction of a day.)
The article explains how the presentation/entry in the interface is not the
same as the way a literal date should appear in a VBA statement or SQL
string. It's really important to get the difference.

I'm not clear what dFindSite and FindSite are in your code. If one of them
is an unbound text box, setting the Format *property* of the text box will
help Access understands the data type. If FindSite is a field, then forcing
the (date/time)? value in dFindSite to become a string (which is what
Format$() outputs), and then back to a date value again is unproductive,
serving only to increase the chance of a misinterpretation.

There is never a valid reason to use:
"\#dd\/mm\/yyyy\#"
when assigning a date value. In a SQL string or criteria, you always need:
"\#mm\/dd\/yyyy\#"
regardless of your local date format.

If you don't follow that discussion about data types, post a follow-up
indicating what dFindSite and FindSite are.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paul" <paulquinlan100 (AT) hotmail (DOT) com> wrote

Hi Allen thanks for the response.

Below is the full code ( with the mm\/dd corrected) which should clear
up a few things:

Dim sFindSite As Variant
Dim dFindSite As String, dCCM As Variant


dFindSite = Me.txtDateSubmittedForReview

If IsDate(dFindSite) Then
If DateValue(dFindSite) = dFindSite Then
FindSite = Format$(dFindSite, "\#mm\/dd\/yyyy\#")
End If
End If


so sFindSite is taking a user entered date from a text box. when the
error occurs "Compile Error: Type declaration character does not match
declared data type" it highlights Format$. the error occurs before
running any of the code in the proceedure.

Dont suppose it could be a missing reference or something??

Thanks again
Paul

On Mar 26, 1:53*pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:

On Mar 26, 9:53*am, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:

I think any unambiguous string is fine. I've been using yyyy-mm-dd for
years. One of the nice things about unambiguous strings is that they
are unambiguous. Another is that civilized and educated cultures
understand them. The insistence on using illogical date string formats
is an annoying habit of the Anglo world and Microsoft's nonsense of
stating that mm/dd/yyyy is required is reprehensible. I wonder how
many millions of dollars this idiocy has cost businesses around the
wrold in the last twenty years or so.

IMO date strings are often sent to JET when they don't need to be. JET
within Access has no trouble with Date() or Now() or UDF() AS Date or
some date converted to double e.g. 39898.4439930556.

Of course, those who practice eye of newt, and toe of frog may not
agree.

Resolved, just used your function Allen and its doing the business.
Thanks again.
Paul


On Mar 26, 1:53*pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:

Fair enough, Lyle.

There are many good reasons for using the yyyy-mm-dd format you suggest.
It's logical. It's and ISO standard. And even if the column is misunderstood
as text, it still sorts correctly.

My preference for mm/dd/yyyy is that this is what JET itself generates when
you create a query, as it is the format VBA uses for literal dates (since
Access 95.) So it seems to me that it is best to teach people to recognise
and generate their literal dates in the format that JET and VBA use,
regardless of their regional settings.

There are times when you don't need to convert a date value into a string to
pass to JET. For a function with no paramter such as Date() or Now(), the
query optimizer will normally call it once only for the whole query, so the
efficency is no different than converting a # delimited string to a date
value (which also happens once for the whole query), so either is as good as
the other.

I'm less keen on passing a floating point value as a date. As you probably
know, date/time is not any kind of normal real value, as the fractional part
represents the time of day. This makes it a really weird data type for
negative numbers, and even JET does not handle it correctly all the time.
For example, if you ask it to pick the Min of two date/time values that are
on the same day and prior to December 30 1899, it picks the wrong one.
Therefore, treating it as if it were just a real number is not something I'm
comfortable doing.

Hopefully that rationale appears a little more logical than 'eye of newt'
stuff.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lyle fairfield" <lyle.fairfield (AT) gmail (DOT) com> wrote

On Mar 26, 9:53 am, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:

I think any unambiguous string is fine. I've been using yyyy-mm-dd for
years. One of the nice things about unambiguous strings is that they
are unambiguous. Another is that civilized and educated cultures
understand them. The insistence on using illogical date string formats
is an annoying habit of the Anglo world and Microsoft's nonsense of
stating that mm/dd/yyyy is required is reprehensible. I wonder how
many millions of dollars this idiocy has cost businesses around the
wrold in the last twenty years or so.

IMO date strings are often sent to JET when they don't need to be. JET
within Access has no trouble with Date() or Now() or UDF() AS Date or
some date converted to double e.g. 39898.4439930556.

Of course, those who practice eye of newt, and toe of frog may not
agree.

Hi Paul,

For building SQL-strings including a date, I always use a function
As_date (this_date).
This_date is a date in my local format, and in this way I never have
to bother about the needed format in SQL.

Example: "SELECT * FROM TblXXX WHERE Startdate = " & As_date(Date)



Function As_date(cur_datum As Variant) As String
' ISO-format: yyyy-mm-dd
If (IsNull(cur_datum)) Then
As_date = "Null"
Else
As_date = "#" & Format(cur_datum, "mm-dd-yyyy") & "#"
End If
End Function


HBInc.



On Mar 26, 12:30*pm, paul <paulquinlan... (AT) hotmail (DOT) com> wrote: