View original (French)

{Access} The date and the day of the week

Burgerdash -  
burgerdash Posted messages 47 Status Member -
Hello,
I have previously reached out for your help in this thread (https://forums.commentcamarche.net/forum/affich-16037923-access-tri-de-doublons). Having made some progress, I am now facing new issues, and I thought it would be better to create a new thread as the problem is different. Should I have posted as a reply?

In my Access 2003 database, I have a field called "Date" where I enter a date in the format dd/mm/yyyy, and it then appears as "DayName dd Month yyyy". By DayName, I mean Monday, Tuesday, Wednesday... Perfect, I actually need to know how many clients came on Mondays, how many on Tuesdays, how many on Wednesdays... Unfortunately, the name of the day displays on the screen but is not usable by the database! Indeed, I can only sort my data in a query in a simply chronological order, but I cannot group them by day of the week. I'm not sure if I'm being clear. Just to be safe, here's a good example. Here’s what I input in my table:

Clients | Purchase Date
Client 1 | 07/01/10
Client 2 | 08/01/10
Client 3 | 14/01/10
Client 4 | 16/01/10
Client 5 | 16/01/10

Here's what displays when my cursor leaves the "Purchase Date" field:

Clients | Purchase Date
Client 1 | Saturday 7 January 2010
Client 2 | Sunday 8 January 2010
Client 3 | Saturday 14 January 2010
Client 4 | Monday 16 January 2010
Client 5 | Monday 16 January 2010

Here’s the type of information I would like to obtain:

Monday: 2 clients
Tuesday: 0 clients
Wednesday: 0 clients
Thursday: 0 clients
Friday: 0 clients
Saturday: 2 clients
Sunday: 1 client
(in a nice graph of course, but I know how to do that afterwards)

Here's what I currently manage to obtain:
January: 5 clients
OR
07/01/10: 1 client
08/01/10: 1 client
14/01/10: 1 client
16/01/10: 2 clients

I tried an old trick of copying a "day of the week" field next to the date, where I simply copy the name of the day as displayed next to it. But:
1. it's tedious to use
2. I feel like I'm using a hammer to crack a Kinder egg (there's certainly a function to avoid this hassle... Right?)
3. my data ends up being sorted alphabetically by day (Sunday, Thursday, Monday...), and that's ugly.

... Can someone help me? :x
Configuration: Windows XP Firefox 3.5.7

32 answers

  • 1
  • 2
  1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
     
    Hi,

    look at the datepart function, it allows you to extract what you want from a date...

    --

    See you later Blux           
     "The idiots dare everything. It's even how we recognize them"
    0
  2. moiced59 Posted messages 1161 Status Member 60
     
    Hello

    1° You did well to open a new post for each issue

    So for your problem, do you want the total number of people who came on a Monday for a month?
    Or
    all the people who came on a specific Monday?
    0
    1. Burgerdash
       
      blux, how do I "look at the datepart function"?

      moiced59, actually I want to have the total of all the people who came on a Monday since the opening of my database, the same for Tuesday, Wednesday...

      Jean-Jacques, I'll try that right away :) Thanks for your help.
      0
  3. Jean_Jacques Posted messages 1045 Registration date   Status Member Last intervention   112
     
    Hello Burgerdash,

    Here is the syntax to obtain the literal value of <Champ_date>:

    Literal day: Format([<Champ_date>];"\ dddd")

    - To be entered in the 'field' of the graphical interface.
    - This, combined with grouping, will allow you to achieve the desired result.

    Best regards
    --
    Science only discovers what has existed since forever.
    REEVES Hubert.
    0
    1. burgerdash Posted messages 47 Status Member 4
       
      [Sorry for the message above, wrong manipulation... :x]

      Blux, how do I "look at the datepart function"?

      moiced59, actually I want to have the sum of all the people who came on a Monday since the opening of my database, same for Tuesday, Wednesday...

      Jean-Jacques, I will try that right away :) Thank you for your help.
      0
  4. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
     
    The datepart function is available in the help...

    --

    A+ Blux           
     "Stupid people dare anything. It's even how you recognize them"
    0
  6. burgerdash Posted messages 47 Status Member 4
     
    Blux, I created a query containing the field "Date" with the Grouping operation and the criterion "DatePart(sem, date, vbMonday)", and a field "Number of invoices created" with the Count operation. On one hand, I think I need to replace "date" with something, but I don't understand what, and on the other hand, Access gives me the following error message preventing me from saving the query: "The entered expression contains a function whose number of arguments is not correct."

    Jean_Jacques, should I 1. create a new query in design view
    2. write this formula in the box at the top left
    3. replace <champ_date> with the name of the field containing the invoice creation dates
    4. choose the "Grouping" operation?
    Because if I do that, Access tells me that I'm making a syntax error...</champ_date>
    0
    1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
       
      You need to replace the datepart parameters with those you want, that is, the date you want it to be applied to and the type of data you want to extract (day, month, week...).

      --

      A+ Blux 
       "Idiots dare to do anything. It's even how we recognize them."
      0
    2. Jean_Jacques Posted messages 1045 Registration date   Status Member Last intervention   112
       
      burgerdash,

      Indeed, you need to replace <Champ_date> with the name of the field that contains the 'date' information.
      I thought this convention was well known ... Sorry.

      Note: You must copy the entire line below! This applies regardless of the mode, whether creating or modifying..
      Literal day: Format([<Champ_date>];"\ dddd")

      A+
      --
      Science only finds what has existed since time immemorial.
      REEVES Hubert.
      0
  7. moiced59 Posted messages 1161 Status Member 60
     
    The best would be for you to create a new field with the name of the day, e.g. day, and then do:

    select count(client)
    from ...
    group by day
    0
    1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
       
      It's not recommended in a relational database...

      --

      A+ Blux 
       "Fools will try anything. That's how you recognize them"
      0
  8. moiced59 Posted messages 1161 Status Member 60
     
    Baah even with relationships, it won't be a problem, I find it simple to apply and it will always be useful.
    0
    1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
       
      If you have a database with 2 million rows, you will add 2 million days!
      What a waste, when it can be recalculated when needed...

      --

      See you, Blux           
       "The idiots will try anything. That's how we recognize them"
      0
  9. burgerdash Posted messages 47 Status Member 4
     
    Blux, I'm sorry, but I don't understand your explanations. Apparently, in my case, in "DatePart(interval, date [, firstdayofweek] [, firstweekofyear])", I need to replace "interval" with "sem" to request a day of the week, and "firstdayofweek" with "vbMonday" because I want Monday to be the first day of the week. I don't care about the first week of the year, and that criterion is optional. Then, here's what the help says about "date":
    "Argument date required. Variant (Date) to evaluate."
    Does that seem clear to you? I suspect Microsoft wrote its documentation in a way that only those who already know how to use the program understand it ^^
    Concretely, do I need to replace "date" with "TableName_FieldName" to process?

    Ced, that's what I tried at first, but it required copying the name of the day when it's displayed in the adjacent field! It's silly; I'm sure Access can do that by itself!
    0
  10. moiced59 Posted messages 1161 Status Member 60
     
    I don't see what the datepart will be useful for you.
    0
    1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
       
      Me neither, I’ve been mixed up from the start... :-)

      It’s the right format to use, as Jean-Jacques said, with one nuance: since we are in SQL, it needs to be the English version:

      SELECT format(champ_date,"dddd") FROM ...

      for today’s date, it returns Saturday.

      Sorry for leading you astray with the error :-(

      --

      A+ Blux 
       "Losers dare everything. That's how you recognize them"
      0
      1. Jean_Jacques Posted messages 1045 Registration date   Status Member Last intervention   112 > blux Posted messages 2067 Registration date   Status Moderator Last intervention  
         
        For your information,

        Here is the copy/paste (adapted) of my SQL test.
        It works correctly under my Access 2000 version.

        SELECT Month([<champ_date>]) AS N°, Format([<champ_date>],"\ dddd") AS [Literal Day]
        FROM <table> GROUP BY Month([<champ_date>]), Format([<champ_date>],"\ dddd");

        See you later
        --
        Science only finds what has existed since forever.
        REEVES Hubert.
        0
  11. burgerdash Posted messages 47 Status Member 4
     
    Ced, it's based on blux's advice that I'm trying to use it... The aim is to obtain data sorted by week period in a query without having to modify the data in the table (since I need the complete date for other queries, and I want to avoid the redundancy of information like

    Date | Day
    Saturday, January 7, 2010 | Saturday

    (which is annoying for the person who has to enter the information daily, and takes up space on the server)).
    0
  12. burgerdash Posted messages 47 Status Member 4
     
    Ah okay XD Thanks Ced for insisting, and thank you blux for persevering.

    So, should I create a column Date | Invoice | Grouping | SELECT format(Date,"dddd") FROM ... in my query?
    Access reaction: "This expression contains an erroneous subquery. Place the subquery in parentheses."
    Am I supposed to replace the dddd or ... with something?
    0
    1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
       
      No, you simply put format(date_you_want, "dddd") as a column.

      What I gave was just a simple example...

      --

      See you, Blux        
       "Stupid people dare to do anything. That's how you recognize them"
      0
  13. moiced59 Posted messages 1161 Status Member 60
     
    re

    I don't understand anything anymore with the field format. You're going to get it on that day, okay, but apparently, he already has it?
    0
    1. blux Posted messages 2067 Registration date   Status Moderator Last intervention   3 456
       
      According to what I understand, he only has one complete date, so as many pivots for a group by...

      --

      A+ Blux           
       "Idiots, they dare everything. That's even how we recognize them"
      0
  14. moiced59 Posted messages 1161 Status Member 60
     
    ah ok I thought that date was in the format Saturday, January 9, 2010
    0
  15. moiced59 Posted messages 1161 Status Member 60
     
    However, I tested it and Access SQL does not support the Format.
    0
  16. moiced59 Posted messages 1161 Status Member 60
     
    re

    G nothing to say, it was just a missing reference, that's why it wasn't working, sorry!!!
    0
  17. burgerdash Posted messages 47 Status Member 4
     
    https://www.imagup.com

    On the left, my test data in the Invoice table (there are other fields but I have hidden them to get to the point); on the right, what I would like to obtain in a query, and then perhaps, in a perfect world, to create a nice graph from it.
    The field highlighted in orange is the one I am currently editing. This is how I enter the data, and this is how it displays. Okay?

    The file was created in 2003, but I'm on 2007 for the weekend.
    0
  18. moiced59 Posted messages 1161 Status Member 60
     
    SELECT Count(lundi.nom) AS total, Format(date,'dddd') AS Expr1
    FROM lundi
    GROUP BY Format(date,'dddd')
    ORDER BY FIELD(Format(date,'dddd'), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')
    0
  19. burgerdash Posted messages 47 Status Member 4
     
    Ced, I don't know how to use SQL. What do I write where, in the graphical query creation tool?

    Thank you for the efforts you are making to help me, I hope to be able to return the favor to you soon.
    0
  20. moiced59 Posted messages 1161 Status Member 60
     
    so your date field was already correctly formatted as Saturday, January 9, 2009
    0
  21. burgerdash Posted messages 47 Status Member 4
     
    Yes, but as it stands, I don't know how to use this "Saturday" that is displayed in a query. That's the whole crux of the problem, if you'll allow me.
    0
  • 1
  • 2