Results 1 to 6 of 6
  1. #1
    slothnet is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    3

    multiple dcount update query

    mysql = "UPDATE table1 SET TLs=DCount(*,""shipper='"" & shipper & ""' and filename='"" & filename & ""'"")"

    *this is supposed to count occurances of duplicate field shipper & filename in table1 table.

    I get


    run-time error '3075':

    Syntax error (missing operator) in query expression
    'DCount(*,"shipper='"&shipper & " and filename='"& filename & "".

    I couldn't see the code in message box to well but the line above should be more or less accurate.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    try:
    mysql = "UPDATE table1 SET TLs=" & cstr(DCount(*,"shipper='" & shipper & ' and filename='" & filename & "'"))

    Adjust the 's and "s if there is syntax error.

    The point is: you can not put vba function in SQL syntax, you need to make up a string using the result of vba function. As this example, Dcount return a number, we need to convert the number to a string then add to the end of the SQL string, let's say dcount return 100:
    mysql = "UPDATE table1 SET TLs=" & cstr(100)

    then we get mysql contents "UPDATE table1 SET TLs=100"

  3. #3
    slothnet is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    3
    I get a plain syntax error without no specifics

  4. #4
    slothnet is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    3
    I even tried this,
    mysql = "UPDATE table1 SET TLs=DCount(*,'shipper=' & CStr(shipper) & ' and filename=' & CStr(filename))"

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you still don't understand what I said, but try this:

    mysql = "UPDATE table1 SET TLs=" & cstr(DCount("*","shipper='" & shipper & ' and filename='" & filename & "'"))

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by weekend00 View Post
    you still don't understand what I said, but try this:

    mysql = "UPDATE table1 SET TLs=" & cstr(DCount("*","shipper='" & shipper & ' and filename='" & filename & "'"))
    weekend,

    this may result in a syntax error too. concatenated strings use this syntax:
    Code:
    tls = '" & string & "'"
    using CSTR on a number I believe yields the same result as using a regular string.

    Slothnet,

    are you familiar with the syntax conventions of domain functions? if not, try googling it. some of it is different between versions, but I believe the general rule in code ONLY is:

    Dfunction("field", "table", "criteria")


    an example of concatenating a string and assigning it in a query might be something like:

    MYSQL = "UPDATE table1 SET " & _

    "TLs = '" & string variable & "'"


    what you posted initially is very complex and maybe impossible because the required quote marks that are common syntax requirements for concatenation have to be nested, which is frustrating to figure out (at the least). I would try to simplify this query so you only need one concat operation in the entire query string.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. INSERT INTO and UPDATE to multiple tables
    By lupis in forum Import/Export Data
    Replies: 6
    Last Post: 05-19-2010, 05:21 AM
  2. Update query to summarize multiple values
    By ser01 in forum Queries
    Replies: 3
    Last Post: 05-15-2010, 09:38 AM
  3. Replies: 1
    Last Post: 02-03-2010, 09:17 PM
  4. Multiple UPDATE in single query
    By nishezaman in forum Access
    Replies: 0
    Last Post: 12-13-2009, 01:40 AM
  5. Update Multiple tables from one form
    By KenK in forum Forms
    Replies: 0
    Last Post: 10-30-2009, 08:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums