Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine text based on a condition.

    The data for the table comes from an OCDB database out of our control.
    for a field named DaysofWeek it list all of the days based on the days that were selected.
    for example. If the input was Monday, Tuesday, Wednesdat, Thursday, Friday then the result in a query is Monday, Tuesday, Wednesda, Thursday, Friday .
    Would like code to just combine the dates if this is true, then it would read Monday - Friday

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    PA,

    If I interpret your question correctly here's a query that will accomplish the task.
    Code:
    DteRng: Left([CombDate],InStr([CombDate],",")-1) & " - " & Right([CombDate],Len([CombDate])-InStrRev([CombDate],","))
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    But if the result in a query said Monday, Wednesday, Friday, Sunday .
    What would you want in that case? Monday-Sunday? but that does not sound right to me.

    Or do you want to do something much complicated?
    Regards
    John



  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    I agree with you. I tried to answer the question as posed and I just assumed that all answers would be contigious ranges. But of course we all know what happens when you assume!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Feb 2012
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you know there would be a twist and you guys were right. They only want to combine if it is M,T,W,TH,F, ONLY for that one. also, it would appear that the field schdaysofweek could come from multiple tables. her was my attempt.

    combinedate: IIf(([dbo_vCourseSchedule.schDaysOfWeek]="Monday" and "Tuesday" and "Wednesday" and "Thursday" and "Friday"), (Left([dbo_vCourseSchedule.schDaysOfWeek],InStr([dbo_vCourseSchedule.schDaysOfWeek],",")-1) & " - " & Right([dbo_vCourseSchedule.schDaysOfWeek],Len([dbo_vCourseSchedule.schDaysOfWeek])-InStrRev([dbo_vCourseSchedule.schDaysOfWeek],","))), [dbo_vCourseSchedule.schDaysOfWeek])

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    PA,

    If the data is formatted as stated in your original post then you want the following:
    Code:
    combinedate: IIf([dbo_vCourseSchedule.schDaysOfWeek]="Monday, Tuesday, Wednesday, Thursday, Friday", "Monday-Friday", [dbo_vCourseSchedule.schDaysOfWeek])
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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