Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Convert number field to day of week!

    Greetings,

    I addressed this in the past but wasn't really able to work this out... Now it's back!

    For this exercise, there are two fields that I would like to work with:

    [UPP_CHECK_DATE] - Format: Number - This value contained in this field is actually a date, formatted as YYYYMMDD, but it was imported as a NUMBER field.

    [TYPE] - Format: Text - blank field


    Here is what I would like to do:

    I would like to create a query or VB code that will populate the TYPE field with one of three values, based on the day of week represented by the "Date" contained in the UPP_CHECK_DATE field.

    More specifically,
    I want the TYPE field to be populated with "CASH" if the day of week happens to be a Monday.
    I want the TYPE field to be populated with "ADJ" if the day of week happens to be a Friday.
    Otherwise, I want the TYPE field to be populated with "UPP".

    I have seen examples of where DateAsText is used, but this is a number field and I can't convert it for practical reasons.

    I appreciate any and all help offered!

    Thanks,

    Mitch

  2. #2
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42

    Lightbulb Here with simple code for idea.

    Dim UPP_CHECK_DATE As Long
    Dim Type_ As String
    Dim myWeekDay As Integer

    UPP_CHECK_DATE = 38027 ' 38027is numeric value of 10-Feb-2004, Tuesday.
    myWeekDay = Weekday(UPP_CHECK_DATE)

    Select Case myWeekDay
    Case 1 ' for Sunday
    Type_ = "UPP"
    Case 2 ' for Monday
    Type_ = "CASH"
    Case 3 ' for Tuesday
    Type_ = "UPP"
    Case 4 ' for Wednesday
    Type_ = "UPP"
    Case 5 ' for Thursday
    Type_ = "UPP"
    Case 6 ' for Friday
    Type_ = "ADJ"
    Case 7 ' for Saturday
    Type_ = "UPP"
    End Select
    Cyber : The easiest place to make huge mistake.

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    For the first rule in normalisation states: Do not store calculatable values!

    To convert your YYYYMMDD number to a "real date" use:
    Dateserial(left([YourField],4),mid([yourfield],5,2),right([YourFiled],2))

    Then dunk that into a function like the one mmlatt posted.

    Regards

Posting Permissions

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