Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Update Query for Fridays!

    Hello again, I searched but could not find anything to help, so here you go:

    I need to create an update query that only updates records that have a date that falls on a FRIDAY!

    The date format for this field is (yyyymmdd)

    The starting date would be the first friday of 2000 (20000105). This query would need to update to today's date...

    I know that I'll need to do something like:

    set startdate=20000105
    next_update_date=startdate+7

    then in my criteria i'd put the value i want in the appropriate field... Unless y'all know a VB approach to use...

    Thanks in advance!

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    What Data Type is this field? Is it a Date/Time or Text? The way you have it written, it looks like Text.

    TD

  3. #3
    Join Date
    Nov 2004
    Posts
    10
    try
    ...
    update table
    set field = value
    where weekday(datefield,1) = 5


    note that weekday takes two parameters,
    a date,
    and an integer to indicate the start of the week.
    1 = sunday (default)
    2 = monday...etc

    weekday(datefield) would have been equivalent in this case.

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    That does not work if the date field is text, which is why I posted the question earlier. If the date field is text, the weekday function returns an error. If it is a Date/Time data type, it is an easy query. If it is text, try this.

    SELECT TextDate.DateAsText, Format(DateSerial(Left([DateAsText],4),Mid([DateAsText],5,2),Mid([DateAsText],7,2)),"dddd") AS Weekday2
    FROM TextDate
    WHERE (((Format(DateSerial(Left([DateAsText],4),Mid([DateAsText],5,2),Mid([DateAsText],7,2)),"dddd"))="Friday"));

    If you want to shortcut it, you can leave out the Format Function and change the Where statement to =5 instead of ="Friday", but since you said you were looking for Friday, I thought you might want to see it.

    TD

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Thumbs up uhmmm.

    Whoa... that... actually... worked...

    What the heck was that?! I mean, that was cool as hell. I keep looking at it and looking at it and i still can't figure out why it worked!

    wow. cool. thanks, TD! that was cool.

    PS, could you tell me more about the "dddd" bit? I don't understand how that applies to the mid() parts... thx
    Last edited by jimmyswinger; 12-02-04 at 16:42.

  6. #6
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Hey there,

    I just tried this again on another file, but this time I got a "Data Type mismatch" error, AFTER it appeared to present the results of the query...

    I view the query, a table shows up with valid data, and right when it gets to the end of looking through all 250,000 records, it pops up with the error and then all the visible fields change to "#Name?".

    Any idea why this would happen? Here is the code:

    SELECT collections.upp_check_date, Format(DateSerial(Left([upp_check_date],4),Mid([upp_check_date],5,2),Mid([upp_check_date],7,2)),"dddd") AS Weekday2
    FROM collections
    WHERE (((Format(DateSerial(Left([upp_check_date],4),Mid([upp_check_date],5,2),Mid([upp_check_date],7,2)),"dddd"))="Friday"));

    The upp_check_date field is in text format.

    Thanks

  7. #7
    Join Date
    Sep 2003
    Posts
    41

    not sure

    not sure if this is it but i belive you code has to be on the same line or the first line has to end in ,

    you have
    ([upp_check_date],7,2)),"dddd") AS Weekday2

    starting on a new line which i believe needs to be on the same line or you have to finish your first line with a ,

    this caused me a problem with a code i did a long time ago and someone here explained it to me i just dont remember if you end with semicolon or comma i would just move that code back up to the end of that line.

  8. #8
    Join Date
    Sep 2006
    Posts
    1

    Talking

    I needed help on this very same situation, my solution was:
    For the criteria:
    BETWEEN (DATE()-WEEKDAY(2,1)) AND DATE ()
    OR
    Between Date()-Weekday(Date(),2)+1 And Date()-Weekday(Date(),2)+7

    Note .. normally the beginning of a week starts on Sunday and ends on Saturday ... But the expression above uses Monday to Sunday as I had needed it ...

Posting Permissions

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