We help IT Professionals succeed at work.

Access VBA SQL Query Error

ca1358
ca1358 asked
on
Medium Priority
173 Views
Last Modified: 2012-05-06
I am getting an error "3085"
Undefined function "Table1.RecDtWhere' in expression.

Any help would greatly be appreciated.
Dim SQL33
SQL33 = "INSERT INTO ETT_CountForForToday ( CountOfCommit, Buyer, RecDt )"
SQL33 = SQL33 & " SELECT Count(Table1.Commit) AS CountOfCommit, Table1.Buyer, Table1.RecDt "
SQL33 = SQL33 & "FROM Table1 "
SQL33 = SQL33 & "GROUP BY Table1.Buyer, Table1.RecDt"
SQL33 = SQL33 & "Where (((Table1.Buyer) = 'Max') And ((Table1.RecDt) = Date))"
SQL33 = SQL33 & "ORDER BY Table1.Buyer;"
CurrentDb.Execute SQL33, dbFailOnError

Open in new window

Comment
Watch Question

Corey ScheichDeveloper

Commented:
You need an extra space


Dim SQL33
SQL33 = "INSERT INTO ETT_CountForForToday ( CountOfCommit, Buyer, RecDt )"
SQL33 = SQL33 & " SELECT Count(Table1.Commit) AS CountOfCommit, Table1.Buyer, Table1.RecDt "
SQL33 = SQL33 & "FROM Table1 "
SQL33 = SQL33 & "GROUP BY Table1.Buyer, Table1.RecDt " '<---added space here
SQL33 = SQL33 & "Where (((Table1.Buyer) = 'Max') And ((Table1.RecDt) = Date))"
SQL33 = SQL33 & "ORDER BY Table1.Buyer;"
CurrentDb.Execute SQL33, dbFailOnError

Open in new window

Author

Commented:
Now I am gett error "3075"

Syntax error (missing operator) In query expression 'Table1.RecDt
Where(((Table1.Buyer) = 'Max') AND ((Table1.RecDt = Date))ORDER  BY Table1.Buyer;'.
Corey ScheichDeveloper

Commented:
Perhaps there were too many Parentheses
Dim SQL33
SQL33 = "INSERT INTO ETT_CountForForToday ( CountOfCommit, Buyer, RecDt )"
SQL33 = SQL33 & " SELECT Count(Table1.Commit) AS CountOfCommit, Table1.Buyer, Table1.RecDt "
SQL33 = SQL33 & "FROM Table1 "
SQL33 = SQL33 & "GROUP BY Table1.Buyer, Table1.RecDt " here
SQL33 = SQL33 & "Where (Table1.Buyer = 'Max') And (Table1.RecDt = Date)"'<--eliminated extra ()
SQL33 = SQL33 & "ORDER BY Table1.Buyer;"
CurrentDb.Execute SQL33, dbFailOnError

Open in new window

Author

Commented:
Changed some of the Parentheses

Now Error states
Syntax error (missing operator) In query expression 'Table1.RecDt
Where(Table1.Buyer) = 'Max') AND (Table1.RecDt = Date)'.

Dim SQL33
SQL33 = "INSERT INTO ETT_CountForForToday ( CountOfCommit, Buyer, RecDt )"
SQL33 = SQL33 & " SELECT Count(Table1.Commit) AS CountOfCommit, Table1.Buyer, Table1.RecDt "
SQL33 = SQL33 & "FROM Table1 "
SQL33 = SQL33 & "GROUP BY Table1.Buyer, Table1.RecDt " '<---added space here
SQL33 = SQL33 & "Where (Table1.Buyer = 'MAX') And (Table1.RecDt = Date)"
SQL33 = SQL33 & "ORDER BY Table1.Buyer;"
CurrentDb.Execute SQL33, dbFailOnError

Open in new window

Developer
Commented:
Move the Where clause above the group by clause
Dim SQL33
SQL33 = "INSERT INTO ETT_CountForForToday ( CountOfCommit, Buyer, RecDt )"
SQL33 = SQL33 & " SELECT Count(Table1.Commit) AS CountOfCommit, Table1.Buyer, Table1.RecDt "
SQL33 = SQL33 & "FROM Table1 "
SQL33 = SQL33 & "Where (Table1.Buyer = 'MAX') And (Table1.RecDt = Date) "
SQL33 = SQL33 & "GROUP BY Table1.Buyer, Table1.RecDt "
SQL33 = SQL33 & "ORDER BY Table1.Buyer;"
CurrentDb.Execute SQL33, dbFailOnError

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.