• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

query works in Access, but not through script

This query:

INSERT INTO custPoints (cust_num, month, year, points)
SELECT customers.cust_num, 4, 2005, SUM(amount) * (SELECT pointValue FROM pointValue)
FROM sales INNER JOIN customers ON sales.cust_num=customers.cust_num WHERE saleDate >= #5/1/2004# AND saleDate <= #5/31/2004# GROUP BY customers.cust_num

works when I type it directly into my access DB, but not when I run it through my script.  How can this be?!?!?!  It gives me the generic error of "Syntax error in INSERT INTO statement"

Yeah yeah, I know I know, I should be using a real db for this....I know that now... :(
0
ARACK04
Asked:
ARACK04
  • 4
  • 3
  • 2
1 Solution
 
ayha1999Commented:
Hi,

try this

dim date1 as string = "5/1/2004"
dim date2 as string = "5/31/2004"

INSERT INTO custPoints (cust_num, month, year, points)
SELECT customers.cust_num, 4, 2005, SUM(amount) * (SELECT pointValue FROM pointValue)
FROM sales INNER JOIN customers ON sales.cust_num=customers.cust_num WHERE saleDate >= "# & date1  & #" AND saleDate <= "# & date2 & #" GROUP BY customers.cust_num

ayha

0
 
b1xml2Commented:
when you run queries thru Access either with or without the use of macros, a lot of hidden operations occur. The SQL syntax that Access can use is greatly limited when compared to say Microsoft SQL Server or Oracle.

0
 
ayha1999Commented:
Hi again,

If not working please post the code you are executing for query?

ayha
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
b1xml2Commented:
ayha,

have you EVEN read what he is talking....do you not realise that some SQL Statements that when executed in Access works do not work with the OleDb Provider.

0
 
ARACK04Author Commented:
b1xml2, what do you recommend?  Is there any way to see if this operation is supported?  Wow, this is frustrating.
0
 
ayha1999Commented:
Hi b1xml2,

That's why I asked him to post his code for query, to see how he is executing.

ayha
0
 
ARACK04Author Commented:
I do a series of string conatenations, and the string I posted is EXACTLY what is produced.  I know this because I did a Response.Write.  The problem is that OldDb will not execute it.
0
 
ARACK04Author Commented:
Ok, it looks like it doesn't like me putting in literal values into my SELECT statement.  Do you guys know any other way to do this?

ie

SELECT field1, 23, 3
FROM ..
...
0
 
ARACK04Author Commented:
I just took the numbers out, and did an update afterward...whatever works.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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