Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Unanswered: Day of the week query/vba, whatever

    I'm trying to get my database to do the following:

    If today is Monday, then show me the last three records from my table, or if today is Tues, Wed, Thurs, Friday, then show me only the last record. Any suggestions?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two approaches

    first is based on calculating directly in the SQL (note: untested)
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM daTable AS t
     WHERE ( SELECT COUNT(*)
               FROM daTable
              WHERE some_datetimecolumn > t.some_datetimecolumn )
         < IIF(WEEKDAY(t.some_datetimecolumn)=2,3,
           IIF(WEEKDAY(t.some_datetimecolumn) IN (3,4,5,6),1,0))
    second approach is simply to return TOP 3 and ignore the ones you don't want

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    • How do you determine the last record of the query?
    • How are you displaying the results?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Dec 2003
    Posts
    138
    Let me make myself a little more clear:

    I have a table that stores values entered daily.

    On Monday, I want to see the value entered on Friday, Saturday, and Sunday.

    If it is Tues, Wed, Thurs, or Friday, I only want to see the value entered from the day before.

    Does this make sense?

    Table name is: Table1

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dendalee View Post
    Let me make myself a little more clear:
    actually you were clear the first time

    did you try my query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2003
    Posts
    138
    Trying it now.

  7. #7
    Join Date
    Dec 2003
    Posts
    138
    Ok, it's not working correctly. I get error messages all over the place. Any other suggestions?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dendalee View Post
    Ok, it's not working correctly. I get error messages all over the place. Any other suggestions?
    please show your exact query, and tell us the error messages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2003
    Posts
    138
    I'm sure I have this completely wrong, but here you go:

    SELECT Table1.Day, Table1.CarsonHandPrevious, Table1.[Cars on Hand Closing]
    FROM Table1;
    Select Table1.CarsonHandPrevious FROM Table1 AS table WHERE ( SELECT COUNT(*) FROM Table1 WHERE Table1.[Day] > Today() )
    < IF(WEEKDAY(Today())=2,3, IIF(WEEKDAY(Today) IN (3,4,5,6),1,0))

  10. #10
    Join Date
    Dec 2003
    Posts
    138
    I forgot to put in my Today field. Sorry for the confusion.
    Let's try this again:

    SELECT Table1.Day, Table1.CarsonHandPrevious, Table1.[Cars on Hand Closing], Date() AS Today
    FROM Table1;

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try like this -
    Code:
    Select Table1.CarsonHandPrevious 
      FROM Table1 AS table 
     WHERE ( SELECT COUNT(*) 
               FROM Table1 
              WHERE Table1.[Day] > table.[Day] ) 
         < IIF(WEEKDAY(table.[Day])=2,3, 
           IIF(WEEKDAY(table.[Day]) IN (3,4,5,6),1,0))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2003
    Posts
    138
    Ok, I got it to work past a few error messages, but it doesn't show me the records. I entered some false data as a sample. It should have produced yesterday's value, but it didn't. Any further suggestions? Instead, it is completely blank.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to be totally honest, i think you should go with the second approach i mentioned

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It might make your life a little easier if you had a 'DateEntered' field in your table which defaults to the date the record is entered (using Now() or Date()). Then you could write your query based on criteria against this data.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Dec 2003
    Posts
    138
    That is what my Today field is - it is Date()

Posting Permissions

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