Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statement (A2k)

    I need to insert into a table certain text strings, date = Now and a check box to false

    I thought the following would be suffice:

    INSERT INTO WIPREMINDER ( [ADMINOPERATORS="DT"], [ESTIMATENo="0"], [DATE=now], [WHO FROM="Workshop"], [NOTES="Images To Import"], [READ=False] )

    It doesn't work.

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

    Re: SQL Statement (A2k)

    You shouldn't use square brackets like this:

    [ADMINOPERATORS="DT"]

    but like this:

    [ADMINOPERATORS]="DT"

    Also, in an SQL statement, you must put parentheses after a function even if there are no arguments, for example Now(). Note: Now() is the date and time (9 October 2003 10:34 AM), while Date() is just the date (9 October 2003). So, replace

    [DATE=now]

    by

    [DATE]=Now()

    or by

    [DATE]=Date()

    depending on what you need.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    So simple.

    This is part of a very old database which is also very embarrasing, ie Table names, spaces etc etc.
    I knew about the function Parenthesis but wasn't sure how to implement it into the square brackets.

    Now() is the function I need for an accurate Date & Time, although the field and it's values will never be used for criteria in future grouping etc.

    Thanks again Hans

    Dave

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    Dim strSQL As String
    strSQL="INSERT INTO WIPREMINDER ( ADMINOPERATORS="DT", [ESTIMATENo]="0", [DATE]=now(), _
    [WHO FROM]="Workshop", [NOTES]="Images To Import", [read ]=False )"
    DoCmd.RunSQL

    I'm getting invalid end of statement with ="DT"
    Should I enclose the strings with: & Chr(34) &

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

    Re: SQL Statement (A2k)

    Yes, either that, or replace the double quotes within the SQL string with double double quotes.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL Statement (A2k)

    A single quote should do it.
    eg. 'DT'

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    Still no go !!

    Private Sub Command255_Click()
    Dim strSQL As String
    strSQL = "INSERT INTO WIPREMINDER ( ADMINOPERATORS='DT', [ESTIMATENo]='0', [DATE]=now(),
    [WHO FROM]='Workshop', [NOTES]='Images To Import', [read ]=False )"
    DoCmd.RunSQL strSQL
    End Sub

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    Thats two of us not thinking clearly <img src=/S/doh.gif border=0 alt=doh width=15 height=15> .

    I only put the space at the end for the post because [read ] becomes <img src=/S/read.gif border=0 alt=read width=19 height=33> (smiley)

    I'll try your suggestions, yes you were right EstimateNo is Numeric !!

    Have a good day

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

    Re: SQL Statement (A2k)

    I wasn't thinking clearly. You are mixing up the syntax for update and append queries.

    UPDATE TableName SET Field1 = Value1, Field2 = Value2

    INSERT INTO TableName (Field1, Field2) VALUES (Value1, Value2)

    If you want to insert a record, try

    strSQL = "INSERT INTO WIPREMINDER " & _
    "( [ADMINOPERATORS], [ESTIMATENo], [DATE], [WHO FROM], [NOTES], <!t>[read]<!/t> ) " & _
    "VALUES ( 'DT', '0', now(), 'Workshop', 'Images To Import', False )"

    Note that I replaced [read ] by <!t>[read]<!/t> - a field name can't end in a space.

    (Dumb question: is EstimateNo really a text field? The value 0 seems to suggest that it is a number.)

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

    Re: SQL Statement (A2k)

    Thanks for pointing it out. I edited my reply to erase the evidence.
    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    Its a pleasure.
    I'm sure the smilies catch us all out from <img src=/S/time.gif border=0 alt=time width=23 height=39> to time.
    But I bet you <img src=/S/new.gif border=0 alt=new width=35 height=15> that anyway !!

    BTW

    SQL is ok now, thanks

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    Hans
    I was just thinking about this SQL we did earlier:

    strSQL = "INSERT INTO WIPREMINDER " & _
    "( [ADMINOPERATORS], [ESTIMATENo], [DATE], [WHO FROM], [NOTES], [read ] ) " & _
    "VALUES ( 'DT', '0', now(), 'Workshop', 'Images To Import', False )"

    I cn us this for another purpose, I'll explain.
    I set up a table ages ago as an internal mail system.
    The users "AdminOperators" each have a query with the criteria set to their initial.

    KW = Karen Willett
    DT = Diane Townsend etc etc

    This works really well and is so simple.
    The method of the SQL as earlier, was to save the legs of the estimator.
    Coupled with the Imagetransfer "Card Reader" the workshop have an old digital camera to take pics of further damage which Insurers want all the time.
    Whenthe code is run, it also sends DT a message sayng Images are ready for download.
    I then turned the Source and Destination paths around for DT's machine so she can import onto a folder on her desktop.

    This is very efficient and she doesn't have to leave her desk every five minutes.

    Any way, This internal mail system could do with a tweak for reception

    Using an strWhere:
    strWhere = Forms!EstimateNo................... (I have the correct syntax at work) but how to insert the strWhere into the strSQL

    strSQL = "INSERT INTO WIPREMINDER " & _
    "( [ADMINOPERATORS], [ESTIMATENo], [DATE], [WHO FROM], [NOTES], [read ] ) " & _
    "VALUES ( 'DT', '0', now(), 'Workshop', 'Images To Import', False )"

    I think previously we put the strWhere at the end of the statement but I've tried that today without avail.
    The EstimateNo etc comes from a subform datasheet view.

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

    Re: SQL Statement (A2k)

    I'm not sure that you need a Where-condition here. But if you want to get one or more of the values to be inserted from a form, you can do so by concatenation. It could could look like the following "air code" - you need to substitute the correct names:

    strSQL = "INSERT INTO WIPREMINDER " & _
    "( [ADMINOPERATORS], [ESTIMATENo], [DATE], [WHO FROM], [NOTES], [read ] ) " & _
    "VALUES ( 'DT', " & Forms!frmEstimates!EstimateNo & ", now(), 'Workshop', 'Images To Import', False )"

    If you want to retrieve more values from the form, you would need to add more & ... & pairs. Make sure that you handle the single and double quotes correctly.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2k)

    I'm sure that will be suffice.
    Thanks again, I can finish my Glass of Rose now.

Posting Permissions

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