Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Query SQL to VB SQL (2000)

    Edited by HansV because <!t>[B]<!/t> is interpreted as "bold on" by the Lounge software.

    Hi All,

    I have built a query which in SQL view looks like this:

    UPDATE DET SET DET.A = "29"
    WHERE (((DET.[img]/forums/images/smilies/cool.gif[/img]='0603') AND ((DET.A)<>'X') AND ((Val([DET]![A]))>30));

    The query looks for all records in DET where B='0603' and A (stored as text) is both <> 'X' and >30.

    What I would like to do is have this operation performed from VBA, using the DoCmd.RunSQL command. Unfortunately I cannot seem to get the correct syntax.

    This is what I am currently working with:
    DoCmd.RunSQL "UPDATE DET in 'filelocation'" _
    & "SET DET.A = '29'" _
    & "WHERE (<!t>[B]<!/t>='0603')" _
    & "and ([A]<>'X'" _
    & "and (Val([DET]![A])>30))"

    But this errors out.

    Can anyone point me in the right direction???

    Thanks in Advance.

    Rob

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

    Re: Convert Query SQL to VB SQL (2000)

    You need spaces before WHERE and AND:
    <code>
    DoCmd.RunSQL "UPDATE DET in 'filelocation'" _
    & "SET DET.A = '29'" _
    & " WHERE (<!t>[B]<!/t>='0603')" _
    & " and ([A]<>'X'" _
    & " and (Val([DET]![A])>30))"
    </code>
    Otherwise, the parts run together.

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Query SQL to VB SQL (2000)

    Thanks Hans!

    Works Perfect!!!

    Rob.

Posting Permissions

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