Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Function (Access2000)

    I think this may be a simple solution but I just can't seem to make it work. <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

    I have a two worksheets, one called DATA and the other REPORT. In the DATA sheet I have a "date" in cell A2, "May 31, 2002". I would like to display this date in my REPORT worksheet BUT I want to add text in front of it. For example, Monthly Report of Sales as of MAY 31, 2002.

    How do I do this. Thank you very much in advance. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Function (Access2000)

    In cell A2 of the REPORT worksheet, put the formula

    =DATA!A2

    Next, with this cell selected, select Format/Cells...

    In the Number format tab, select Custom and enter the following string into the Type box:

    "Monthly Report of Sales as of "mmmm d, yyyy

    Click OK.

    An alternative is to use the following formula and no formatting:

    ="Monthly Report of Sales as of "&TEXT(DATA!A2,"mmmm d, yyyy")

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Function (Access2000)

    The easy way is to use two cells, putting the text in one and the linked value in the other, and then format the text as right-justified and the value as left-justified. When printed or displayed it will look "pretty close" to a single caption for a report.

    The alternative that you were actually asking for is to concatenate two text strings, one with the phrase you want ("Sales Report as of...") and the other with the text value corresponding to the date recorded in the other file. This uses the TEXT function to convert a value to a text string, in the required format. Try the formula below:

    ="Monthly Report of Sales, as at "&TEXT([Book2]Sheet1!$B$3,"dd mmmm, yyyy").

    The on-line help (or the formula builder) can give you some insight into how the TEXT function works, or post back here for more discussion.

  4. #4
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Function (Access2000)

    Thanks a million!!!!! it worked perfectly using the function
    ="Monthly Report of Sales as of "&TEXT(DATA!A2,"mmmm d, yyyy")

    YOUR THE BEST
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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