Results 1 to 11 of 11

Thread: syntax (2000)

  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    syntax (2000)

    The first 4 lines of the following code work fine. Then I wanted to add another condition ( line 5 in blue). This line fails. Any help?

    strSQL = ""
    strSQL = strSQL & "UPDATE ALERT_RECORDS "
    strSQL = strSQL & "SET ALERT_RECORDS.ActualStopDate = Date() "
    strSQL = strSQL & "WHERE ResID = ' " & ID & " ' "
    <font color=blue>strSQL = strSQL & "AND ISNULL(ActualStopDate)"</font color=blue>
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: syntax (2000)

    Is it just a lack of a space before the AND?

    strSQL = strSQL & " AND ISNULL(ActualStopDate)"

    Put in a msgbox(strSQL) before the Docmd line and look at the sql to see if it looks OK.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    WHEN I run the following code, I get a "Data type Mismatch error"

    <font color=blue>Dim ID As String
    ID = Me.ResID
    DoCmd.SetWarnings False
    <font color=448800> ' Set the ActualStopDate in the ALERT_RECORDS table</font color=448800>
    strSQL = ""
    strSQL = "UPDATE ALERT_RECORDS SET ALERT_RECORDS.[ActualStopDate] = Date()WHERE [ResID] = " & ID & " AND isnull([ActualStopDate])"
    Debug.Print strSQL
    strSQL = "UPDATE ALERT_RECORDS SET ALERT_RECORDS.[ActualStopDate] = Date()WHERE [ResID] = " & ID & " AND [ActualStopDate] ="""" "
    Debug.Print strSQL
    DoCmd.RunSQL strSQL</font color=blue>

    And here's what I get in the Immediate window:

    <font color=blue>UPDATE ALERT_RECORDS SET ALERT_RECORDS.[ActualStopDate] = Date()WHERE [ResID] = 1 AND isnull([ActualStopDate])
    UPDATE ALERT_RECORDS SET ALERT_RECORDS.[ActualStopDate] = Date()WHERE [ResID] = 1 AND [ActualStopDate] ="" </font color=blue>


    I purposefully typed "isnull" in lower case thinking that since this is an SQL statement the isnull function would not be recognized....?? Also, wrote it " =""
    The data type mismatch makes me think there may be something to do with entering dates in the #.....# format ????

    I also took another approach: wrote an update qry
    ActualStopDate/ALERT_RECORDS/Date()/Is Null
    ResID/ALERT_RECORDS/ /[Form]![frmALERT_RECORDS]![ResID]

    When I ran this it asked me to manually enter the ResID parameter rather than picking it up from the form.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    PS

    ResID is indeed TEXT (not numeric)

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: syntax (2000)

    What I notice is that you have : Date()WHERE

    I think you need another space so that it reads Date() WHERE , otherwise it won't recognise the Date() function.
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    I just tried that on both versions - no luck
    Also tried putting a space in is null

    When I go the the Object Library in the VBE Is Null is recognized as a vba function. Can I use it in a SQL STATEMENT?

    Have you tried either of these lines of code? or a similar one? If so, can you post an example that has worked for you?
    thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: syntax (2000)

    Try this:
    <pre>strSQL = "UPDATE ALERT_RECORDS "
    strSQL = strSQL & "SET ActualStopDate = Date() "
    strSQL = strSQL & "WHERE ResID = '" & ID & "' "
    strSQL = strSQL & "AND ActualStopDate Is Null"
    </pre>

    Note that I removed the space after the first ' and before the second '.
    The 'official' way to test for a field begin blank in SQL is 'FieldName Is Null'.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: syntax (2000)

    I created an update query in the query grid, then pasted the sql into vba, and got this:

    strsql = "UPDATE ALERT_RECORDS SET ALERT_RECORDS.Actualstopdate = Date() WHERE (((ALERT_RECORDS.Actualstopdate) Is Null) And ((ALERT_RECORDS.ResID) = 1))"

    when I ran it , it worked OK

    I attach a sample file.
    Attached Files Attached Files
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: syntax (2000)

    This works also

    strsql = "UPDATE ALERT_RECORDS SET ALERT_RECORDS.Actualstopdate = Date() WHERE (((isNull(ALERT_RECORDS.Actualstopdate))) And ((ALERT_RECORDS.ResID) = 1))"
    Regards
    John



  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    Hans

    Thanks
    but the ActualStopDate still isn't being updated.

    Here's what the immediate window shows after running you code

    UPDATE ALERT_RECORDS SET ActualStopDate = Date() WHERE ResID = ' 10 ' AND ActualStopDate Is Null

    Note the 10 is the correct ResID for the particular resident that was in the textbox when the code was run. But, the single quotes around it seems like they should not be there.

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    OK I got it to work by removing both spaces between the single and dbl quotes in the line:

    WHERE ResID = <font color=blue>' " & ID & " ' </font color=blue> "

    THANKS BOTH OF YOU
    These quotes are quite confusing to me still. Hope to master them soon.

Posting Permissions

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