VB6 App with hardcoded SQL to access a Access 2007 DB

5 pts.
Tags:
Access 2007
Excel 2007
VB 6
Hi Team,
I have a query that I run successfully in native SQL within Access 2007 but if I use this code within VB6 it seems as though it is not using the "From" & "To" dates. It does return values but it amounts to the complete database totals.
The extra code added to the production code was for the "pickerid in" statement with the array.
I pass the pickerid and also the from & to dates from excel. dd/mm/ccyy format.
The only real change is I added a array which defaults to a value of 999. I know I do not have these values in the access db so I will never get a row returned.
Process:-
I read the Excel spreadsheet to get those pickerids that have been flagged as to be reported on.. Max 70.
These Pickerids are then put into the Array. All non used array items have been set to 999.
The query (2nd select statement) should then pull all the data and hold as a temporary value for the 1st Select statement to SUM upon.
Your help is really appreciated.. And by all means call me stupid if its bleedingly obvious whats wrong.
I have also included the production code that works when running within Access using SQL.
Regards
Witsend
New Code
    SQL2 = "SELECT SUM(totalweight) AS TotalKilos, SUM(totalweight)/SUM(pickerhours) AS HourlyRate, sum(boxcount) AS Boxes " & _
           "FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight, count(*) AS BoxCount, avg(currentweight) as AvgBox " & _
           "FROM mergeddatabase " & _
           "GROUP BY pickerid,currentdate,pickerhours,pickername)  AS [%$##@_Alias] " & _
           "WHERE pickerid in (" & _
            WSPickerNo(0) & "," & WSPickerNo(1) & "," & WSPickerNo(2) & "," & WSPickerNo(3) & "," & WSPickerNo(4) & "," & WSPickerNo(5) & "," & WSPickerNo(6) & "," & WSPickerNo(7) & "," & WSPickerNo(8) & "," & WSPickerNo(9) & "," & _
            WSPickerNo(10) & "," & WSPickerNo(11) & "," & WSPickerNo(12) & "," & WSPickerNo(13) & "," & WSPickerNo(14) & "," & WSPickerNo(15) & "," & WSPickerNo(16) & "," & WSPickerNo(17) & "," & WSPickerNo(18) & "," & WSPickerNo(19) & "," & _
            WSPickerNo(20) & "," & WSPickerNo(21) & "," & WSPickerNo(22) & "," & WSPickerNo(23) & "," & WSPickerNo(24) & "," & WSPickerNo(25) & "," & WSPickerNo(26) & "," & WSPickerNo(27) & "," & WSPickerNo(28) & "," & WSPickerNo(29) & "," & _
            WSPickerNo(30) & "," & WSPickerNo(31) & "," & WSPickerNo(32) & "," & WSPickerNo(33) & "," & WSPickerNo(34) & "," & WSPickerNo(35) & "," & WSPickerNo(36) & "," & WSPickerNo(37) & "," & WSPickerNo(38) & "," & WSPickerNo(39) & "," & _
            WSPickerNo(40) & "," & WSPickerNo(41) & "," & WSPickerNo(42) & "," & WSPickerNo(43) & "," & WSPickerNo(44) & "," & WSPickerNo(45) & "," & WSPickerNo(46) & "," & WSPickerNo(47) & "," & WSPickerNo(48) & "," & WSPickerNo(49) & "," & _
            WSPickerNo(50) & "," & WSPickerNo(51) & "," & WSPickerNo(52) & "," & WSPickerNo(53) & "," & WSPickerNo(54) & "," & WSPickerNo(55) & "," & WSPickerNo(56) & "," & WSPickerNo(57) & "," & WSPickerNo(58) & "," & WSPickerNo(59) & "," & _
            WSPickerNo(60) & "," & WSPickerNo(61) & "," & WSPickerNo(62) & "," & WSPickerNo(63) & "," & WSPickerNo(64) & "," & WSPickerNo(65) & "," & WSPickerNo(66) & "," & WSPickerNo(67) & "," & WSPickerNo(68) & "," & WSPickerNo(69) & "," & _
            WSPickerNo(70) & " and ((currentdate) between " & WSStartDate & " and " & WSEndDate & "))"
Production code that works.. This doesnt have the pickerid's.
SELECT SUM(totalweight) AS TotalKilos, SUM(totalweight)/SUM(pickerhours) AS HourlyRate, sum(boxcount) AS Boxes
FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight, count(*) AS BoxCount, avg(currentweight) as AvgBox
FROM mergeddatabase
GROUP BY pickerid,currentdate,pickerhours,pickername)  AS [%$##@_Alias]
WHERE ((currentdate between [Enter first date:] AND [Enter last date:]));


Software/Hardware used:
Access 2007, VB6, Excel 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Are you sure it is the pickerid what is causing the problem?  I would try removing that condition (leaving the dates condition) to see what happens (and vice versa).

    I would also display or log to a file the final query string, just to review that it is in fact being constructed as expected.

    And, I would try putting # signs around the dates.

    If you try these options, please let us know the results.
    69,160 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following