Results 1 to 3 of 3

Thread: Date Problem

  1. #1
    Join Date
    Jun 2004

    Unanswered: Date Problem


    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.


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    sometimes A can convince you that it can handle nonUS date format - but it's lying!

    safest is to convert to US date format "internally" even tho you talk to your users in "normal" date format.

    this might help

    currently using SS 2008R2

  3. #3
    Join Date
    May 2004

    Try medium date

    I tend to always use medium date as the format for my tables
    ie. 12-feb-04

    This tends to fix any date issues for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts