Results 1 to 4 of 4
  1. #1
    mike182 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    2

    Access Formula

    In access I am trying to have it pull data in one of my queries that I am using to report headcount for the previous week. So for example, for this week I need it to show 11/26 no matter when I open the database this week (12/3-12/9) And then the following week show 12/3 even when I open the database (12/10-12/16) I think the formula's below will work in access but I cant get combos of them working in access. Any help would be appreciated.

    =TEXT((TODAY()-0)-WEEKDAY(TODAY()),"m/d/yy")+0
    =TEXT((TODAY()-7)-WEEKDAY(TODAY()),"m/d/yy")+0


  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access equivalent of the Excel Text function is Format.

    Access equivalent of the Excel Today() function is Date().

    so it translates to

    =Format(Date()-WeekDay(Date()),"m/d/yy")

    But not sure why you need a text representation of the date (format converts the date numerical value to text)- it won't sort in date order for example. Adding 0 will not convert it to a number

    As with excel, dates are a special form of number which can be formatted to whatever date format you require. You can use the format property of a field or control to show dates as you want without converting it to text

    today (3rd December) is 42707, tomorrow will be 42708 and yesterday was 42706

    and now (17:57) is 0.7483564815 which is the number of seconds so far this day divided by the number of seconds in the day (86400)

    if you use the access now() function, it returns the number 42707.7483564815

  3. #3
    mike182 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    2
    Quote Originally Posted by Ajax View Post
    Access equivalent of the Excel Text function is Format.

    Access equivalent of the Excel Today() function is Date().

    so it translates to

    =Format(Date()-WeekDay(Date()),"m/d/yy")

    But not sure why you need a text representation of the date (format converts the date numerical value to text)- it won't sort in date order for example. Adding 0 will not convert it to a number

    As with excel, dates are a special form of number which can be formatted to whatever date format you require. You can use the format property of a field or control to show dates as you want without converting it to text

    today (3rd December) is 42707, tomorrow will be 42708 and yesterday was 42706

    and now (17:57) is 0.7483564815 which is the number of seconds so far this day divided by the number of seconds in the day (86400)

    if you use the access now() function, it returns the number 42707.7483564815
    Awesome, that forumla got me the date, thank your for that. The reason I was thinking the 0 needed to be added was so I could make it a number so could I could find the difference between two numbers.
    Click image for larger version. 

Name:	hc 2.jpg 
Views:	12 
Size:	66.7 KB 
ID:	26619

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no - you just need

    Date()-WeekDay(Date())

    then if necessary, set the format property of the field or control

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

Similar Threads

  1. Access Min Formula Help
    By Jmaes1994 in forum Database Design
    Replies: 4
    Last Post: 11-04-2016, 11:40 PM
  2. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  3. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  4. Access formula
    By Zeebow in forum Programming
    Replies: 3
    Last Post: 10-03-2014, 02:07 PM
  5. Max formula in Access
    By mohsin74 in forum Programming
    Replies: 2
    Last Post: 12-26-2006, 07:21 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