I challenge ANYONE to work this one out...

[Nathon Jones]

Hi Joe,

So a database put together in the UK and hosted on a UK server is, whether
we like it or not, going to have dates interpretted as US format?

That's the bit I don't understand.

It begs the question....why does Access even bother letting people enter UK
date formats when it is just going to ignore them anyway!?

I love Microsoft.
Nath.


"Joe Makowiec" <webmaster@munged.brunswicklibrary.invalid> wrote in message
news:Xns9607AA333B6F1makowiecatnycapdotrE@216.104. 212.96...

> On Thu 24 Feb 2005 02:51:13p, Julian Roberts wrote in
> macromedia.dreamweaver.appdev:
>

>> Access will always interpret 01/04/2005 as 4th Jan. Format your dates
>> to the ISO standard of YYYY-MM-DD
>>
>> [url]http://www.ceilidhculture.co.uk/eventdates.asp?eventdate=2005-04-01[/url]

>
> Julian's got it. Dates which are ambiguous[1] (1 Apr - 3 Apr, 5 Apr - 12
> Apr) don't work; but if I try this URI:
>
> [url]http://www.ceilidhculture.co.uk/eventdates.asp?eventdate=2005-4-3[/url]
>
> I get events for 3 Apr with no problem.
>
> Try this:
> <%
> myYear = Year(rsEvDates.Fields.Item("eventdate").Value)
> myMonth = Month(rsEvDates.Fields.Item("eventdate").Value)
> myDay = Day(rsEvDates.Fields.Item("eventdate").Value)
> myDate = myYear & "-" & myMonth & "-" & myDay
> %>
> <a href="eventdates.asp?eventdate=<%= myDate %>">
>
> There's gotta be an easier way, but this way you have control over it.
>
> [1] Remember where Microsoft database products come from:
>
> 1 Apr leftpond: 4/1/2005 rightpond: 1/4/2005
> 2 Apr leftpond: 4/2/2005 rightpond: 2/4/2005
> 3 Apr leftpond: 4/3/2005 rightpond: 3/4/2005
> 4 Apr leftpond: 4/4/2005 rightpond: 4/4/2005
> 5 Apr leftpond: 4/5/2005 rightpond: 5/4/2005
> ...
> 13 Apr leftpond: 4/13/2005 rightpond: 13/4/2005*
> * Even the dumbest of databases should be able to figure out either one.

[Joe Makowiec]

On 25 Feb 2005 in macromedia.dreamweaver.appdev, Nathon Jones wrote:

> March works because the dates are:
> 19th - 30th. In the database, these are interpreted as 19/03/2005,
> 20/03/2005...
>
> They CAN'T be interpretted any other way (by any other country!),
> because there isn't a "month 19", "month 20". So the database
> says..."ok, these MUST be March".

Exactly.

> My other dates:
> 1st April - 17th April also have problems initially, until I get
> beyond the "12th April".

Actually... 4 April works, as it's unambiguous.

--
Joe Makowiec
[url]http://makowiec.net/[/url]
Email: [url]http://makowiec.net/email.php[/url]

[CMBergin]

It's a server setting - blame your host.

"Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
news:cvmv1b$rhl$1@forums.macromedia.com...

> Hi CM,
>
> What I should have perhaps mentioned is that this:
> <a
>

href="eventdates.asp?eventdate=<%=(rsEvDates.Field s.Item("eventdate").Value)

> %>">
>
> ...is within a repeat region.
>
> So I placed this within the repeat region also:
>
> <%
> DateField = rsEvDates.Fields.Item("eventdate").Value
> MyDate = Year(dateField) & "-" & Month(dateField) & "-" & Day(dateField)
> %>
>
> And it now appears to be working.
>
> Thank you so much for your help.
>
> I'm still annoyed that, despite being in the UK, and hosting the site in

the

> UK, VBScript/ASP is still interpretting UK dates as US.
> Why? Who knows!
>
> Nath.
>
>
> "CMBergin" <NoHarvestForYou@NoSpam.org> wrote in message
> news:cvlijb$t3b$1@forums.macromedia.com...

> > Sorry, I'm in and out.
> > Let's do it a little different:
> >
> > <%
> > DateField = rsEvDates.Fields.Item("eventdate").Value
> > MyDate = Year(dateField) & "-" & Month(dateField) & "-" & Day(dateField)
> > %>
> > <a
> >

href="eventdates.asp?eventdate=<%=MyDate%>"><%=DoD ateTime(DateField,1,2057)%

> >></a>

> >
> > "Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
> > news:cvlbhr$ikv$1@forums.macromedia.com...

> >> Hi CM,
> >>
> >> Can you help me out with that code? Here's what I've got:
> >>
> >> <a
> >>

> >

href="eventdates.asp?eventdate=<%=(rsEvDates.Field s.Item("eventdate").Value)

> > %>"><%=

> >> DoDateTime((rsEvDates.Fields.Item("eventdate").Val ue), 1, 2057) %></a>
> >>
> >> How does that translate into what you've shown me there?
> >>
> >> Thanks for sticking with me!
> >> Nath.
> >>
> >> "CMBergin" <NoHarvestForYou@NoSpam.org> wrote in message
> >> news:cvlap0$hf5$1@forums.macromedia.com...
> >> > Entering a new format in Access may not do any good.
> >> > Format it on the web page:
> >> > <%
> >> > DateField = recordset.Fields.Item("eventDate").Value
> >> > Response.Write(Year(dateField) & "-" & Month(dateField) & "-" &
> >> > Day(dateField))
> >> > %>
> >> >
> >> > "Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
> >> > news:cvla3h$gca$1@forums.macromedia.com...
> >> >> Hi CM,
> >> >>
> >> >> The problem is happening on all computers and I'm pretty sure it's
> >> >> also
> >> >> happening on yours and Nancy's no doubt.
> >> >>
> >> >> I just tried changing all of the event dates into YYYY-MM-DD and it
> >> >> has
> >> > made
> >> >> no difference.
> >> >>
> >> >> In fact, when I entered the new format, and tabbed into the next
> >> >> field,
> >> >> it
> >> >> just jumped back to DD/MM/YYYY format!!
> >> >>
> >> >> Help!!!!!!!
> >> >>
> >> >> Nath.
> >> >>
> >> >> "CMBergin" <NoHarvestForYou@NoSpam.org> wrote in message
> >> >> news:cvl9au$f6e$1@forums.macromedia.com...
> >> >> > Sounds like you're working with different date formats on

different

> >> >> > computers.
> >> >> > Submit your dates as YYYY-MM-DD and there won't be any confusion

on

> > any

> >> >> > computer. You can use the date formatting functions to display

them

> > as

> >> >> > you
> >> >> > wish on screen.
> >> >> >
> >> >> > "Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
> >> >> > news:cvl813$d4g$1@forums.macromedia.com...
> >> >> >> It's a date issue and it has got EVERYONE here stumped...
> >> >> >>
> >> >> >> Try this:
> >> >> >> [url]www.ceilidhculture.co.uk/events.asp[/url]
> >> >> >>
> >> >> >> Ok, so choose any date in March from the list. You will be shown

a

> >> > list
> >> >> > of
> >> >> >> events in March.
> >> >> >>
> >> >> >> Now choose 1st April. Nothing! (yet there ARE DEFINITELY events
> >> >> >> on
> >> > the
> >> >> > 1st
> >> >> >> April).
> >> >> >> The querystring in the address bar will be:
> >> >> >> [url]www.ceilidhculture.co.uk/eventdates.asp?eventdate=01/04/2005[/url]
> >> >> >>
> >> >> >> Now try changing this date to US format - 04/01/2005
> >> >> >>
> >> >> >> It works! But....the dates are listed as 01/04/2005!
> >> >> >>
> >> >> >> I HATE COMPUTERS!
> >> >> >>
> >> >> >> Nath.
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>

> >
> >

>
>

[CMBergin]

Access' formatting doesn't affect the underlying data. Database dates are
numbers of some sort that are turned into strings that you recognize for
your convenience. For the database to correctly interpret the strings you
pass back for dates, it has to know what you're doing. I'm not very
familiar with Access, but SQL Server (Microsoft's big database) has a
server-wide setting that tells it how to interpret dates. I would be
surprised if Access didn't have a database-wide setting for the same
somewhere. What it sounds like is that the formatting you apply in the
Access interface only affects that interface and not anything in the
database engine itself. I can't verify that, though.

Anyway, if you have problems, just keep using YYYY-MM-DD dates. All
databases will understand those and interpret them correctly, regardless of
regional settings. Or, if Access is smart enough, use abbreviations (Apr 2,
2005) because those are unambiguous. It would understand both "Apr 2" and
"2 Apr". There is some flexibility there. :)

"Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
news:cvmv5p$rmv$1@forums.macromedia.com...

> Hi Joe,
>
> So a database put together in the UK and hosted on a UK server is, whether
> we like it or not, going to have dates interpretted as US format?
>
> That's the bit I don't understand.
>
> It begs the question....why does Access even bother letting people enter

UK

> date formats when it is just going to ignore them anyway!?
>
> I love Microsoft.
> Nath.
>
>
> "Joe Makowiec" <webmaster@munged.brunswicklibrary.invalid> wrote in

message

> news:Xns9607AA333B6F1makowiecatnycapdotrE@216.104. 212.96...

> > On Thu 24 Feb 2005 02:51:13p, Julian Roberts wrote in
> > macromedia.dreamweaver.appdev:
> >

> >> Access will always interpret 01/04/2005 as 4th Jan. Format your dates
> >> to the ISO standard of YYYY-MM-DD
> >>
> >> [url]http://www.ceilidhculture.co.uk/eventdates.asp?eventdate=2005-04-01[/url]

> >
> > Julian's got it. Dates which are ambiguous[1] (1 Apr - 3 Apr, 5 Apr -

12

> > Apr) don't work; but if I try this URI:
> >
> > [url]http://www.ceilidhculture.co.uk/eventdates.asp?eventdate=2005-4-3[/url]
> >
> > I get events for 3 Apr with no problem.
> >
> > Try this:
> > <%
> > myYear = Year(rsEvDates.Fields.Item("eventdate").Value)
> > myMonth = Month(rsEvDates.Fields.Item("eventdate").Value)
> > myDay = Day(rsEvDates.Fields.Item("eventdate").Value)
> > myDate = myYear & "-" & myMonth & "-" & myDay
> > %>
> > <a href="eventdates.asp?eventdate=<%= myDate %>">
> >
> > There's gotta be an easier way, but this way you have control over it.
> >
> > [1] Remember where Microsoft database products come from:
> >
> > 1 Apr leftpond: 4/1/2005 rightpond: 1/4/2005
> > 2 Apr leftpond: 4/2/2005 rightpond: 2/4/2005
> > 3 Apr leftpond: 4/3/2005 rightpond: 3/4/2005
> > 4 Apr leftpond: 4/4/2005 rightpond: 4/4/2005
> > 5 Apr leftpond: 4/5/2005 rightpond: 5/4/2005
> > ...
> > 13 Apr leftpond: 4/13/2005 rightpond: 13/4/2005*
> > * Even the dumbest of databases should be able to figure out either one.

>
>

[Julian Roberts]

Access isn't that flexible. Consider the following code

<%
Session.LCID=2057
conn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=
c:\virtualroot\lawyers\db\7yhg4dfg3.mdb"
Set MM_Cmd = Server.CreateObject("ADODB.Command")
MM_Cmd.ActiveConnection = conn
MM_Cmd.CommandText = "insert into Orders (OrderDate) values (#01/04/2005#)"
MM_Cmd.Execute
Set MM_Cmd = Nothing
%>

Access will store that as 4th Jan, no matter what. But, we're singing from
the same hymn sheet, the problem is easily resolved by passing dates to
Access in the ISO format.

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[CMBergin]

I appreciate the info. It never hurts to have more information.


"Julian Roberts" <newsg@charon.co.uk> wrote in message
news:cvn9vg$eju$1@forums.macromedia.com...

> Access isn't that flexible. Consider the following code
>
> <%
> Session.LCID=2057
> conn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=
> c:\virtualroot\lawyers\db\7yhg4dfg3.mdb"
> Set MM_Cmd = Server.CreateObject("ADODB.Command")
> MM_Cmd.ActiveConnection = conn
> MM_Cmd.CommandText = "insert into Orders (OrderDate) values

(#01/04/2005#)"

> MM_Cmd.Execute
> Set MM_Cmd = Nothing
> %>
>
> Access will store that as 4th Jan, no matter what. But, we're singing from
> the same hymn sheet, the problem is easily resolved by passing dates to
> Access in the ISO format.
>
> --
> Jules
> [url]http://www.charon.co.uk/charoncart[/url]
> Charon Cart 3
> Shopping Cart Extension for Dreamweaver MX/MX 2004
>
>