sql error? 1

[ttuser4]

i have this sql in my vb6 program

sSQL = "SELECT [LUMBER].[INV] AS INV_NUMBER, Sum([LUMBER].[VOLUME]) AS _VOLUME, Sum([LUMBER].[TOTAL]) AS _AMOUNT, [LUMBER].[CUST] AS CUSTOMER, LUMBERD.MIL, LUMBERD.COND, LUMBERD.SPEC, LUMBERD.USD, LUMBERD.NOTE From LUMBERD Where (UCase(MonthName(Month(DATE1))) = '" & cmbMONTHS.Text & "') GROUP BY [LUMBER].[INV], [LUMBER].[CUST], LUMBERD.USD, LUMBERD.MIL, LUMBERD.COND, LUMBERD.SPEC, LUMBERD.NOTE;"
Set rstTarget = New ADODB.Recordset
rstTarget.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

i am not sure what is wrong with it - it gives me empty recordset from vb6 program.

when i debug.print ssql, copy it and run in ms access work ok:

SELECT [LUMBER].[INV] AS INV_NUMBER, Sum([LUMBER].[VOLUME]) AS _VOLUME, Sum([LUMBER].[TOTAL]) AS _AMOUNT, [LUMBER].[CUST] AS CUSTOMER, LUMBERD.MIL, LUMBERD.COND, LUMBERD.SPEC, LUMBERD.USD, LUMBERD.NOTE From LUMBERD Where (UCase(MonthName(Month(DATE1))) = 'AUGUST') GROUP BY [LUMBER].[INV], [LUMBER].[CUST], LUMBERD.USD, LUMBERD.MIL, LUMBERD.COND, LUMBERD.SPEC, LUMBERD.NOTE;

INV_NUMBER _VOLUME _AMOUNT CUSTOMER MIL COND SPEC USD NOTE
001 26156 $10,462.40 TENRYU CANADA CORPORATION ROUGH GREEN FIR No
002 7340 $6,761.20 DOUBLETREE FOREST PRODUCTS LTD. ROUGH GREEN WRC No
003 11392 $15,379.20 SAWARNE LUMBER CO. LTD. ROUGH GREEN WRC No
004 3504 $6,359.76 E.R. PROBYN EXPORT LTD. ROUGH GREEN WRC No

any idea, please?

 

[SkipVought]

hi,

when i debug.print ssql, copy it and run in ms access work ok:

So you KNOW that your SQL is not the problem, correct?

Did you try to STEP thru your code? Have you use the Watch Window to observe the values of variables and states of objects or use other debug techniques? If so, what have you observed, specifically?

I'd particularly look at your connection string. If you have done due diligence, then post your VB code, along with your observations, for asdditional help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ttuser4]

well, in the vb code i just changed one line with the sql (i updated an existing, working code). nothing else has been changed (connection, variables, etc).
instead of running 12 different sql queries in ms access based on selected month, i try to run just one updated query from vb code.
i am not getting any error, just wrong recordset. so i would say sql is the problem even it looks ok...

 

[SkipVought]

What does "wrong recordset" mean?

Please understand that you must provide clear, complete, cogent information that conveys all the information required for someone to understand your issue and provide a solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ttuser4]

there is no data - recordcount is null (not zero), fields.count is zero

 

[Golom]

Your SQL appears to be invalid. You are referencing two tables/queries [LUMBER] and LUMBERD but only LUMBERD is defined in your FROM clause. The SQL is probably raising an error that is being supressed by your error handling routine. You will get the results you are quoting (i.e. NULL count, no fields) if the SQL could not be run at all.

 

[ttuser4]

thanks, you are right - now it says 'undefined monthname function'.
is anything wrong with 'UCase(MonthName(Month(DATE1)))'?

http://www.techonthenet.com/access/functions/date/monthname.php

it doesn't give me error when run directly in ms access as query

 

[Golom]

When you are running in Access it is Access that interprets your SQL and Access can properly recognize most VBA constructs such as MonthName. However, in VB6, it is ADO that interprets the SQL and for the most part, ADO doesn't know about VBA routines.

You may want to try something like Month(Date1) = 8 in your query.

 

[SkipVought]

How about UCase(Format(Date1, "mmmm"))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ttuser4]

thank you for your help.
i had to do it in two steps:

Select Case cmbMONTHS.Text
Case "JANUARY"
myMo = 1
Case "FEBRUARY"
myMo = 2
Case "MARCH"
myMo = 3
Case "APRIL"
myMo = 4
Case "MAY"
myMo = 5
Case "JUNE"
myMo = 6
Case "JULY"
myMo = 7
Case "AUGUST"
myMo = 8
Case "SEPTEMBER"
myMo = 9
Case "OCTOBER"
myMo = 10
Case "NOVEMBER"
myMo = 11
Case "DECEMBER"
myMo = 12
End Select
...
sSQL = "SELECT [LUMBER].[INV] AS INV_NUMBER, Sum([LUMBER].[VOLUME]) AS _VOLUME, Sum([LUMBER].[TOTAL]) AS _AMOUNT, [LUMBER].[CUST] AS CUSTOMER, [LUMBERD].[MIL], [LUMBERD].[COND], [LUMBERD].[SPEC], [LUMBERD].[USD], [LUMBERD].[NOTE] From LUMBERD Where (Month(DATE1) = " & myMo & ") GROUP BY [LUMBER].[INV], [LUMBER].[CUST], [LUMBERD].[USD], [LUMBERD].[MIL], [LUMBERD].[COND], [LUMBERD].[SPEC], [LUMBERD].[NOTE];"

it looks like ado doesn't like MonthName function (even it is sql, vba, ...)

http://social.msdn.microsoft.com/Fo...s/thread/5cee755a-e1ba-4e3c-8705-63685224c890

 

[strongm]

That's what golom said