Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Access VBA SQL Query Error

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

0
ca1358
Asked:
ca1358
  • 3
  • 3
1 Solution
 
Corey ScheichDeveloperCommented:
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

0
 
ca1358Author 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;'.
0
 
Corey ScheichDeveloperCommented:
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ca1358Author 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

0
 
Corey ScheichDeveloperCommented:
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

0
 
ca1358Author Commented:
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now