Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    Doubt about query

    friends,

    I'm having a little trouble to perform a query.

    I have a table named "tbl_Date" with the following fields "DATE_1" and "Date_2" (the fields are date type).

    How do I know the records where the dates of the field "Date_2" are 15 working days more useful that the dates of the field "DATE_1"

    Thank you.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    9,959
    Thanks
    126
    Thanked 1,100 Times in 1,012 Posts
    Hi Fabio,

    What you mean with "more useful"? Probably knowing this may help defining what you want, but I would be looking to use the DateAdd function: http://www.techonthenet.com/access/f...te/dateadd.php

    This function would allow you to add 15 week days to the initial date and make a comparison, probably allowing you to know what you need to know.

    HTH

  4. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-12-07)

  5. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Fabio,

    Just to add to Rui suggestion if a value is calculated, which appears to be the case with your Date_2, by convention it should not be stored in a table but created when needed in a query. This keeps the tables simpler and insures against data errors where someone changes Date_1 and not Date_2 or just changes Date_2.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-12-07)

  7. #4
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui and RetiredGeek, thanks for the help.

    I tried these three expressions using the function ADDDATE, but all it shows a syntax error message:

    Expr1: DateAdd ("d", "15", #DATE_1#)
    Expr1: DateAdd ('d', 15, [DATE_1])
    Expr1: DateAdd ("d", 15, [DATE_1])

    Please, where am I going wrong?


    Rui meant that I'm trying to get only those records where the value is
    in the field "Data_2" is greater than "Data_1".

    example:

    DAta_01 Data_2
    07/12/2012 02/01/2013

    In this case the date field "Data_2" is 15 days (useful - workdays
    ) larger than the date field "Data_1"

  8. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Fabio,

    The correct format for use in a query is:
    ColTitle: DateAdd("d",15,[FieldName])
    DateAdd.JPG
    Expression used for the graphic above: FollowUp: DateAdd("d",15,[DeceasedDte])

    FYI: If you are after only working days you should see this MS Article.
    Last edited by RetiredGeek; 2012-12-07 at 12:48.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-12-07)

  10. #6
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    See the attached image. I put the word exactly as you showed me. Sorry, the error message is in Portuguese:

    The expression you entered contains invalid syntax. You omitted an operand or operator, entered an invalid character or comma, or inserted without text enclose it in quotes.
    image1.jpg

  11. #7
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

  12. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Fabio,

    Is the data type of the Date_1 field Date/Time? Or Text?
    DateTimeField.JPG
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-12-07)

  14. #9
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Date_1 and Date_2 are Date/Time!image3.jpg

  15. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    9,959
    Thanks
    126
    Thanked 1,100 Times in 1,012 Posts
    Fabio,

    Can you go into SQL View and then copy all the SQL and paste it here?

  16. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-12-08)

  17. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Fabio,

    Sorry, but I'm at a loss. Everyting works on my end and I can't see anything wrong with what you are doing.

    Here's the SQL:
    Code:
    SELECT Hospital.PatientNo, Hospital.LName, Hospital.FName, Hospital.DeceasedDte, DateAdd("d",15,[DeceasedDte]) AS FollowUp
    FROM Hospital;
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  18. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-12-08)

  19. #12
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui and RetiredGeek,image5.png

    I found the reason: I was using comma (,) to separate function parameters
    must use the semicolon (;).

    Simply withdraw the (;) and only put (,) does not work. (See the picture)

    See, I'm using the wizard in design view, and not directly in making
    SQL.

    Maybe it's because my access is in Portuguese, not sure.

    But the important thing is that now worked perfectly, many thanks for help
    you both.

  20. #13
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    I'm trying to show the sign of semicolons, but is getting a face (happy image)

  21. #14
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    9,959
    Thanks
    126
    Thanked 1,100 Times in 1,012 Posts
    Yes, the forum code does have that behavior. You can go around it (and I just did, but if you edit the post again, the changes to avoid it are removed).

    Anyway, I am glad you solved your problem.

  22. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-12-08)

  23. #15
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, thanks.

Page 1 of 2 12 LastLast

Posting Permissions

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