Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003

    Unanswered: Basic? Access problem. (Am I Expecting too much?)

    Doing acces in college. Trying to create a database for booking holiday cottages.

    I've got a table called calendar, which contains day, date, and week number.

    What i want to do on my booking form, is, when I type the required date into a date/time field, I want the text field below to automatically show the week number.

    I've set up a query, which gets the date from the form, and gives the result correctly, but I can't figure out how to get that result into the text field.

    Please help.

    PS I'm new to all this, so please try and put it in laymans terms.

  2. #2
    Join Date
    Nov 2003
    Place this line (with the necessary proper field names) into the BeforeUpdate event of your Form's Data/Time entry field:

    Me.myWeekNumberField = DatePart("ww", CDate(Me.myDataTimeField))

    Hope this helps

  3. #3
    Join Date
    Oct 2003
    Provided Answers: 1
    You can copy and past this function in a new Module, then use it to get the weekNum of any date you send to it.

    Function weekNum(anyDate)
    &nbs; ' Compute the weeknum of any given date
    Dim newYear As Variant
    Dim Mon1Week As Variant
    Dim MonAktWeek As Variant
    Dim interval As Integer

    newYear = DateSerial(Year(anyDate), 1, 1)

    'Monday of the first week in year
    Select Case WeekDay(newYear, vbSunday)
    Case 1 ' Sunday
    Mon1Week = DateAdd("d", 1, newYear)

    Case 2 ' Monday
    Mon1Week = newYear

    Case 3 ' Tuseday
    Mon1Week = DateAdd("d", -1, newYear)

    Case 4 ' Wendsday
    Mon1Week = DateAdd("d", -2, newYear)

    Case 5 ' Thursday
    Mon1Week = DateAdd("d", -3, newYear)

    Case 6 ' Friday
    Mon1Week = DateAdd("d", 3, newYear)

    Case 7 ' Saturday
    Mon1Week = DateAdd("d", 2, newYear)
    End Select

    MonAktWeek = DateAdd("d", 1 - WeekDay(anyDate, vbMonday), anyDate)
    interval = MonAktWeek - Mon1Week
    weekNum = (interval / 7) + 1
    End Function

    Then in the BeforeUpdate event of your Form's Date field: put

    txt_weekNum= WeekNum(Me.myDateField)

  4. #4
    Join Date
    Dec 2003
    Thanx Guys that did it.

Posting Permissions

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