Results 1 to 5 of 5
  1. #1
    Ganymede is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    102

    Long Choose Function

    I need to write a choose function that selects the proper ending for the day of the month. For instance, January 3rd, 2012. In excel, I had a template document with a date that would automatically update thanks to the following formula:


    Code:
    =CHOOSE(Text(Today(), "d"), "st", "nd", "rd", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "st", "nd", "rd", "th", "th", "th", "th", "th", "th", "th", "st")
    When I try to replicate this in access, it says the formula is too complex. Since I can write shorter choose functions in access, I assume the expression is too complex because it is too long. Is there a clean way around this? I can think of a number of messy ways, but I feel like access should have a way around this.

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Why even use ordinals in a date?

    Apparently 3 ordinals too many for ControlSource. However, VBA has no problem with the full set. Suggest build a function and then call the function in the ControlSource.

    Are Text and Today custom functions? I used Day(Date) instead.

    Alternative is Switch or IIf function (both work for me), like:
    =IIf((Day(Date())>3 And Day(Date())<21) Or (Day(Date())>23 And Day(Date())<31),"th",IIf(Day(Date()) Like "*1","st",IIf(Day(Date()) Like "*2","nd",IIf(Day(Date()) Like "*3","rd",""))))
    =Switch((Day(Date())>3 And Day(Date())<21) Or (Day(Date())>23 And Day(Date())<31),"th",Day(Date()) Like "*1","st",Day(Date()) Like "*2","nd",Day(Date()) Like "*3","rd")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ganymede is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    102
    Quote Originally Posted by June7 View Post
    Why even use ordinals in a date?

    Apparently 3 ordinals too many for ControlSource. However, VBA has no problem with the full set. Suggest build a function and then call the function in the ControlSource.

    Are Text and Today custom functions? I used Day(Date) instead.

    Alternative is use Switch or IIf functions.
    The item I'm dating is an official court document. Ordinals are not required, but they are custom.

    Build a function? I guess I'm kind of new to access. Is that similar to a "named range" in excel? Or were you suggesting making a custom function in VBA?

    Text() and Today() are regular excel functions. Text is Text(range, format). Today() just returns the current date in whatever format you specify. In that equation, I used the text() function to format the date to "d".

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Text and Today won't work in Access or VBA. I edited my previous post and provided alternative to VBA custom function suggestion. If you read before those edits, might look at again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Ganymede is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    102
    Quote Originally Posted by June7 View Post
    Text and Today won't work in Access or VBA. I edited my previous post and provided alternative to VBA custom function suggestion. If you read before those edits, might look at again.
    Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Choose a record from a query?
    By Kevo in forum Forms
    Replies: 12
    Last Post: 07-15-2011, 09:47 PM
  2. Replies: 1
    Last Post: 07-24-2010, 07:46 AM
  3. Msg-box - choose teamnumber
    By carstenhdk in forum Reports
    Replies: 5
    Last Post: 05-31-2010, 01:32 PM
  4. Automatically choose which report?
    By Lockrin in forum Reports
    Replies: 2
    Last Post: 12-28-2009, 02:41 PM
  5. How do I choose multiple records for a report
    By admaldo in forum Reports
    Replies: 2
    Last Post: 03-03-2006, 06:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums