Consulting

Results 1 to 6 of 6

Thread: DATES / DAYS / MONTHS / GRAPHS

  1. #1
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location

    DATES / DAYS / MONTHS / GRAPHS

    Hi
    I have just created my first database with help from thisforum (thank you) and now adding some charts and come across an issue I amunable to figure out!

    A received date is entered onto a form / table and then two otherfields automatically pull the DAY and MONTH the request received. Essentially using the original date field ascontrol and formatting them to DDD and MMMM, no issues here.
    However, when I try to use the DAY or MONTH to create chartsthey are just being seen as full dates e.g. today would be entered as 17 June2019, the next column would extract the day as Monday and the next the month asJune but the chart is just adding full dates.
    Is it possible to force the chart to use the DAY / MONTHvalues? Chart options in Access seemvery limited!

    Thank you
    Mykal


  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes the graphing function in Access is very poor and awkward to use.
    Have you tried changing the that your data looks, so that Access does not recognise it as a date?
    Something like combining the fields to read 17 of June, or 17-June etc.

  3. #3
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Hi.
    Apologies for late come back, was off work ill and just backin.
    I needed the date split into days (Mon, Tue etc) and months(Jan, Feb etc.) so we can see if there are trends for particular days / months,so combing wouldn’t work.
    I’d already set up an export to Excel function so will justdrop the data into a template when we need to audit. As you said, I think the graphs in access areprobably a waste of time but thank you for your response.
    Mykal

  4. #4
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Hi
    I have continued to play with the DATE / DAY / MONTH problemI was having and managed to almost get it to work (on 6 days out of 7)!
    The Date Field is a generic date and then the DAY is acalculated field pulled from the date (as is the month). Previously I just used the date field as theday field but just changed the format to DDDD which worked to look at butbehind the scenes it was still a date.
    I changed the calculated field to this WeekdayName((Weekday([Date])-1)) and it works Monday to Saturday butreturns an error for SUNDAY. I added theminus 1 because the weekday was 1 originally.
    Anyone know what I have done wrong here and why it is onlySunday (assuming it’s something to do with day 1 being a Sunday?)
    Mykal

  5. #5
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Got there, just after I gave up :-) Changed to this WeekdayName(Weekday([Date],2))

    Thank you

    Mykal

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Well done.
    I was about to write that if I remember correctly Sunday is either day 0 or day 1 so subracting one from it would produce an error.

Posting Permissions

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