Date Problem

[jonnymenthol]

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_LostFocus()
Select Case Format(TxtDateTo, "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.Caption = 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.

 

[RichO]

I assume you are doing this from a form. Did you try setting the format for the date control to dd/mm/yy?

You can also use this expression in place of the lengthy Select Case:

TxtCutOffLookup = DateAdd("d", (8 - DatePart("w", TxtDateTo)) Mod 7, TxtDateTo)

It will give you the same results with just a single line of code.

 

[jonnymenthol]

Thanks...

Thanks very much for that, I'll give it a go.

J.