Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: MS Access - Date Condition Not working

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Access - Date Condition Not working

    Hey Guys,

    I have some SQL code that updates a Schedule table, which is working;

    SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
    "tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
    "tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
    "tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
    "WHERE (((tblSchedule.EstNo) = " & tempEstNo & " )" & _
    "And (tblSchedule.SchDate) => " & DateFrom & ");"

    however on the bottom row "And (tblSchedule.SchDate) => " & DateFrom & ");"
    the condition doesnt seem to be working correctly; if I have dates in Oct,Nov and December

    and the DateForm value is mid November; the update will change all of the records; regardless of the date condition but I have set it to greater than equal to

    can anyone see what im doing wrong?

    thanks

  2. #2
    New Member
    Join Date
    Nov 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    DateFrom = Format(Forms!frmChangeEstName!EstDateFrom, "Short Date")

    which doesnt matter but yeah

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,984
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MS Access - Date Condition Not working

    You should view your SQL to confirm the actual SQL command text (which is also much easier to debug since thats the actual SQL command you are sending to the database for execution):


    Code:
    SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
    "tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
    "tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
    "tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
    "WHERE (((tblSchedule.EstNo) = " & tempEstNo & " )" & _
    "And (tblSchedule.SchDate) => " & DateFrom & ");"
    DEBUG.PRINT SQL_tblSchedule_Part2
    or (message boxes are not always as easy for viewing long text values but it seems some people don't know how to view debug.print messages in the immediate window):
    Code:
    SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
    "tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
    "tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
    "tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
    "WHERE (((tblSchedule.EstNo) = " & tempEstNo & " )" & _
    "And (tblSchedule.SchDate) => " & DateFrom & ");"
    MsgBox SQL_tblSchedule_Part2
    So, what is your actual sql command here? Most likely you are not correctly formatting the date for use in SQL.

    Note that this is not a valid sql operator as far as I know:
    Code:
    =>
    I assume you mean:
    Code:
    >=
    I guess it's possible that is the problem too and it has nothing to do with the date.
    Last edited by xenou; Nov 29th, 2017 at 09:57 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    Respectfully, "doesn't work" (same as "isn't working") makes everyone guess. Error numbers, messages can help a lot. If none, then elaborate on what's not happening that should be. I suspect you got a syntax error like "Missing Operator..."
    It's always greater than or less than first, equal sign second. Wrong (=> or =<). Right (>= or <=)
    Agree that the lack of date delimiters (#) is/will be a problem as well
    "And (tblSchedule.SchDate) >= #" & DateFrom & "#);"
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    Hey dude, this was the answer why;

    SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
    "tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
    "tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
    "tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
    "WHERE ((tblSchedule.EstNo) = " & tempEstNo & " )" & _
    "And (tblSchedule.SchDate >= #" & DateFrom & "#);"


    it was the hashes around the variable; thanks for trying /flicks wrist

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    it was the hashes around the variable
    I kinda doubt that was all. Put the >= back to what you had (=>) and I'll wager it doesn't work again.
    What the heck does this mean
    /flicks wrist
    Sounds like you're giving me the finger or something

  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    i love you Micron <3
    /kisses

  8. #8
    Board Regular
    Join Date
    May 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    You do not show where you are located, but as well as the hashes the date has to be mm/dd/yyyy format? for Access SQL

    No need for the flick wrist

    Quote Originally Posted by mattyhousecat View Post
    Hey dude, this was the answer why;

    SQL_tblSchedule_Part2 = "UPDATE tblSchedule SET " & _
    "tblSchedule.EstName = [forms]![frmChangeEstName]![newEstName]," & _
    "tblSchedule.EstState = [forms]![frmChangeEstName]![newState]," & _
    "tblSchedule.EstRegion = [forms]![frmChangeEstName]![newRegion] " & _
    "WHERE ((tblSchedule.EstNo) = " & tempEstNo & " )" & _
    "And (tblSchedule.SchDate >= #" & DateFrom & "#);"


    it was the hashes around the variable; thanks for trying /flicks wrist
    Last edited by welshgasman; Nov 30th, 2017 at 05:07 AM.
    Excel 2007

  9. #9
    New Member
    Join Date
    Nov 2017
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access - Date Condition Not working

    Sorry new member, im from the land down under; Australia

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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