Date Problem

[Jonny]

Hello,

I have a DB, which when I enter a date into the DateTo field, a box is
populated with a Cut Off Date (this cut off date is held in a table
against each week). For example, the table would be :

WeekEnding - CutOff
25/06/04 - 10/05/04
02/07/04 - 08/06/04
and so on....

Therefore, I have used a Case Statement and a lookup (as per the code
below)

Private Sub TxtDateTo_LostF ocus()
Select Case Format(TxtDateT o, "ddd")
Case "Mon"
TxtCutOffLookup = DateAdd("d", 6, TxtDateTo)
Case "Tue"
TxtCutOffLookup = DateAdd("d", 5, TxtDateTo)
Case "Wed"
TxtCutOffLookup = DateAdd("d", 4, TxtDateTo)
Case "Thu"
TxtCutOffLookup = DateAdd("d", 3, TxtDateTo)
Case "Fri"
TxtCutOffLookup = DateAdd("d", 2, TxtDateTo)
Case "Sat"
TxtCutOffLookup = DateAdd("d", 1, TxtDateTo)
Case "Sun"
TxtCutOffLookup = TxtDateTo
End Select
'On Error Resume Next
Dim DateOne
DateOne = DLookup("[CutOffDate]", "TblCutOffDate" , "[WeekEnding] =
#" & TxtCutOffLookup & "#")
LblCutOff2.Capt ion = Format(DateOne, "dd mmmm yyyy")
End Sub

However, when I enter a date of say 11/07/04, the cut off date is
appearing as sometime in October (Basically because I assume it's
treating it as a US Date Format.

All of my settings are in UK, so why is this happening, and how can I
get round it ?

Thanks in advance.

J.

[Allen Browne]

Re: Date Problem

If TxtDateTo is unbound, Access does not know it is supposed to be a date.
To fix that, set the Format property of the text box to Short Date or
similar.

Two other cases where Access can misunderstand your UK date format are
discussed in article:
International Date Formats in Access
at:

http://allenbrowne.com/ser-36.html

--
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.

"Jonny" <jonnymenthol@y ahoo.com> wrote in message
news:14f4e726.0 406110434.261f9 d5e@posting.goo gle.com...[color=blue]
>
> I have a DB, which when I enter a date into the DateTo field, a box is
> populated with a Cut Off Date (this cut off date is held in a table
> against each week). For example, the table would be :
>
> WeekEnding - CutOff
> 25/06/04 - 10/05/04
> 02/07/04 - 08/06/04
> and so on....
>
> Therefore, I have used a Case Statement and a lookup (as per the code
> below)
>
> Private Sub TxtDateTo_LostF ocus()
> Select Case Format(TxtDateT o, "ddd")
> Case "Mon"
> TxtCutOffLookup = DateAdd("d", 6, TxtDateTo)
> Case "Tue"
> TxtCutOffLookup = DateAdd("d", 5, TxtDateTo)
> Case "Wed"
> TxtCutOffLookup = DateAdd("d", 4, TxtDateTo)
> Case "Thu"
> TxtCutOffLookup = DateAdd("d", 3, TxtDateTo)
> Case "Fri"
> TxtCutOffLookup = DateAdd("d", 2, TxtDateTo)
> Case "Sat"
> TxtCutOffLookup = DateAdd("d", 1, TxtDateTo)
> Case "Sun"
> TxtCutOffLookup = TxtDateTo
> End Select
> 'On Error Resume Next
> Dim DateOne
> DateOne = DLookup("[CutOffDate]", "TblCutOffDate" , "[WeekEnding] =
> #" & TxtCutOffLookup & "#")
> LblCutOff2.Capt ion = Format(DateOne, "dd mmmm yyyy")
> End Sub
>
> However, when I enter a date of say 11/07/04, the cut off date is
> appearing as sometime in October (Basically because I assume it's
> treating it as a US Date Format.
>
> All of my settings are in UK, so why is this happening, and how can I
> get round it ?
>
> Thanks in advance.
>
> J.[/color]

[Bob Quintal]

Re: Date Problem

jonnymenthol@ya hoo.com (Jonny) wrote in
news:14f4e726.0 406110434.261f9 d5e@posting.goo gle.com:
[color=blue]
> Hello,
>
> I have a DB, which when I enter a date into the DateTo field,
> a box is populated with a Cut Off Date (this cut off date is
> held in a table against each week). For example, the table
> would be :
>
> WeekEnding - CutOff
> 25/06/04 - 10/05/04
> 02/07/04 - 08/06/04
> and so on....
>
> Therefore, I have used a Case Statement and a lookup (as per
> the code below)
>
> Private Sub TxtDateTo_LostF ocus()
> Select Case Format(TxtDateT o, "ddd")
> Case "Mon"
> TxtCutOffLookup = DateAdd("d", 6, TxtDateTo)
> Case "Tue"
> TxtCutOffLookup = DateAdd("d", 5, TxtDateTo)
> Case "Wed"
> TxtCutOffLookup = DateAdd("d", 4, TxtDateTo)
> Case "Thu"
> TxtCutOffLookup = DateAdd("d", 3, TxtDateTo)
> Case "Fri"
> TxtCutOffLookup = DateAdd("d", 2, TxtDateTo)
> Case "Sat"
> TxtCutOffLookup = DateAdd("d", 1, TxtDateTo)
> Case "Sun"
> TxtCutOffLookup = TxtDateTo
> End Select
> 'On Error Resume Next
> Dim DateOne
> DateOne = DLookup("[CutOffDate]", "TblCutOffDate" ,
> "[WeekEnding] =
> #" & TxtCutOffLookup & "#")
> LblCutOff2.Capt ion = Format(DateOne, "dd mmmm yyyy")
> End Sub
>
> However, when I enter a date of say 11/07/04, the cut off date
> is appearing as sometime in October (Basically because I
> assume it's treating it as a US Date Format.
>
> All of my settings are in UK, so why is this happening, and
> how can I get round it ?
>
> Thanks in advance.
>
> J.[/color]

Allen has given you the solution to the problem, but your code
could be much simpler,

To calculate a weekending date, examine the possibilities in the
weekday function,

txtcuttofflooku p = dateadd("d",7-weekday(txtdate to,2),txtdateto )

replaces the whole SELECT CASE structure.

Bob Quintal