Results 1 to 3 of 3

SQL to get or delete records on a week day

  1. #1
    anjemalo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    3

    SQL to get or delete records on a week day

    Hello:



    Can you tell me how to create a query that returns or delete all records that be a saturday, for example?

    I am using this query but I get an error:

    SQL="SELECT * From MyTabla WHERE fld_day<Date() AND UserId <= 200 AND WeekDay([fld_day,7]);"

    thank you very much

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,906
    Exactly what are you doing with this SELECT statement - trying to open a recordset?

    Where you have 7 in WeekDay function is argument to specify day you want to consider first day of week. The days are numbered from the first day. The default first day of week is Sunday which means Saturday is day 7. (and you have ] in wrong position)

    AND WeekDay([field_day]) = 7

    If you specify 7 as first day of week that means Saturday is day 1.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,355
    Quote Originally Posted by anjemalo View Post
    SQL="SELECT * From MyTabla WHERE fld_day<Date() AND UserId <= 200 AND WeekDay([fld_day,7]);"
    Be aware that "SQL" is a reserved word in Access and shouldn't be used as an object/variable name.

    Better would be "strSQL" or "sSQL" or ...
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Delete query to delete records that meet criteria
    By neill_long in forum Queries
    Replies: 1
    Last Post: 06-11-2018, 02:41 PM
  2. Replies: 9
    Last Post: 06-19-2015, 03:37 PM
  3. Replies: 3
    Last Post: 09-19-2013, 10:18 AM
  4. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  5. Replies: 3
    Last Post: 07-10-2011, 05:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums