Trouble with sql in VBA, too much? 4

[benisma]

I am trying to put the following sql into VBA to run 134 different districts.

SELECT cash.REGION, cash.DISTRICT, cash.STORE, cash.LINE, cash.IPLCD, cash.IPCODE, cash.cash_sls_total, 1-(cash.cash_cost_total/cash.cash_sls_total), installer.charge_sls_total, 1-(installer.chg_cost_total/installer.charge_sls_total), (cash.cash_sls_total+installer.charge_sls_total), 1-((cash.cash_cost_total+installer.chg_cost_total)/(cash.cash_sls_total+installer.charge_sls_total)), `ytd and prev ytd sls`.YTD_TOTAL_SALES_AT_RETAIL, `ytd and prev ytd sls`.PRV_YTD_TOTAL_SALES_AT_RETAIL, internal_one.sum_of_internal, month_end_inv_one.inv_val
FROM cash cash, installer installer, internal_one internal_one, month_end_inv_one month_end_inv_one, `ytd and prev ytd sls` `ytd and prev ytd sls`
WHERE month_end_inv_one.DISTRICT = `ytd and prev ytd sls`.DISTRICT AND month_end_inv_one.IPCODE = `ytd and prev ytd sls`.SUB_CODE AND month_end_inv_one.IPLCD = `ytd and prev ytd sls`.PRODUCT_LINE_CODE AND month_end_inv_one.LINE = `ytd and prev ytd sls`.LINE AND month_end_inv_one.REGION = `ytd and prev ytd sls`.REGION AND month_end_inv_one.STORE_NUMBER = `ytd and prev ytd sls`.STORE AND cash.DISTRICT = installer.DISTRICT AND cash.IPCODE = installer.IPCODE AND cash.IPLCD = installer.IPLCD AND cash.LINE = installer.LINE AND cash.REGION = installer.REGION AND cash.STORE = installer.STORE AND installer.DISTRICT = internal_one.DISTRICT AND installer.IPCODE = internal_one.IPCODE AND installer.IPLCD = internal_one.IPLCD AND installer.LINE = internal_one.LINE AND installer.REGION = internal_one.REGION AND installer.STORE = internal_one.STORE AND internal_one.DISTRICT = month_end_inv_one.DISTRICT AND internal_one.IPCODE = month_end_inv_one.IPCODE AND internal_one.IPLCD = month_end_inv_one.IPLCD AND internal_one.LINE = month_end_inv_one.LINE AND internal_one.REGION = month_end_inv_one.REGION AND internal_one.STORE = month_end_inv_one.STORE_NUMBER AND ((cash.DISTRICT In (21)))

The trouble I am running into is a compile error stating that a list seperator or ) is expected. I have looked at every period, comma, quotation mark and list seperator. I can not find what is wrong. I thought that maybe I was putting too much criteria in the sql. I then tried to remove some of the joins and ended up with duplicate information. Then I tried to put unique values and group records in Excel to get rid of duplicate information, and it did not work.

Is there anything I can do? If it is too many joins and criteria, is there a way around it? I would really appreciate any suggestions.

I had also planned on re-writing this report, but I was asked to produce the 134 district reports by Monday, so I need a fix until I can get the report right.

Thanks in advance!

 

[fredericofonseca]

before placing this SQL on the VBA string consider the following.


1- Depending on your database you may be a LOT better off by having this as a Stored Procedure or as a Query (Access).

2- Before trying in VBA go to your database SQL window and execute the sql there. Fix whatever is wrong there and THEN place it on VBA if so required. Option 1 is better.

3- change
`ytd and prev ytd sls` `ytd and prev ytd sls`
to
[ytd and prev ytd sls] as tblx (use a shorter name here with NO SPACES)

and then reference this on the remaining of the sql as
tblx.PRODUCT_LINE_CODE

Avoid using long names like that on the table alias as it only makes things worst.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

Hi,

Could be a disaster if any of the highlighted row values or calculated results is ZERO, cuz you'll get a divide by ZERO error.

SELECT  
  CSH.REGION
, CSH.DISTRICT
, CSH.STORE
, CSH.LINE
, CSH.IPLCD
, CSH.IPCODE
, CSH.cash_sls_total
, 1-(CSH.cash_cost_total/[b]CSH.cash_sls_total[/b])
, INS.charge_sls_total
, 1-(INS.chg_cost_total/[b]INS.charge_sls_total[/b])
, (CSH.cash_sls_total+INS.charge_sls_total)
, 1-((CSH.cash_cost_total+INS.chg_cost_total)/[b](CSH.cash_sls_total+INS.charge_sls_total)[/b])
, YTD.YTD_TOTAL_SALES_AT_RETAIL
, YTD.PRV_YTD_TOTAL_SALES_AT_RETAIL
, INT.sum_of_internal
, END.inv_val

FROM 
  CASH                   CSH
, INSTALLER              INS
, INTERNAL_ONE           INT
, month_end_inv_one      END
, `ytd and prev ytd sls` YTD

WHERE END.DISTRICT     = YTD.DISTRICT 
  AND END.IPCODE       = YTD.SUB_CODE 
  AND END.IPLCD        = YTD.PRODUCT_LINE_CODE 
  AND END.LINE         = YTD.LINE 
  AND END.REGION       = YTD.REGION 
  AND END.STORE_NUMBER = YTD.STORE 
  AND CSH.DISTRICT     = INS.DISTRICT 
  AND CSH.IPCODE       = INS.IPCODE 
  AND CSH.IPLCD        = INS.IPLCD 
  AND CSH.LINE         = INS.LINE 
  AND CSH.REGION       = INS.REGION 
  AND CSH.STORE        = INS.STORE 
  AND INS.DISTRICT     = INT.DISTRICT 
  AND INS.IPCODE       = INT.IPCODE 
  AND INS.IPLCD        = INT.IPLCD 
  AND INS.LINE         = INT.LINE 
  AND INS.REGION       = INT.REGION 
  AND INS.STORE        = INT.STORE 
  AND INT.DISTRICT     = END.DISTRICT 
  AND INT.IPCODE       = END.IPCODE 
  AND INT.IPLCD        = END.IPLCD 
  AND INT.LINE         = END.LINE 
  AND INT.REGION       = END.REGION 
  AND INT.STORE        = END.STORE_NUMBER 
  AND CSH.DISTRICT In (21)

Other than that, I can't find a syntax problem.

Tip: Use the IIF function to test for ZERO.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[xlbo]

please can you post the code that goes just before and just after this as, if Skip says there are no syntax errors, it may be that it is something else in the code that is tripping up - can you also show how you are calling this SQL string (no need to repost the whole lot - just a marker will do)

 

[benisma]

Here is the begining of the code for xlbo:
range("C4").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\jsneed\My Documents\work for fleet 1-2006.mdb;DefaultDir=C:\Documents and " _
), Array( _
"Settings\jsneed\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
))
.CommandText = Array( _

I have tried the code that you posted for me Skip, and I am having a little trouble with it. I am getting a syntax error in the from clause statement. I have copied and pasted yours, written mine, and changed the actual table names in the access database to the csh, ins, int, end, and ytd. It still gives me the invalid syntax error. Am I missing something?

 

[xlbo]

the error is being thrown as you are trying to put the SQL string in an array which will not hold it - you don't actually need the arrays - I'm not sure why the macro recorder uses them. Anyway - if I record a querytable setup, the 1st thing I do is dispense with the arrays and set the property to a normal string. Try this:

range("C4").Select
    With Selection.QueryTable
        .Connection = "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\jsneed\My Documents\work for fleet 1-2006.mdb;DefaultDir=C:\Documents and Settings\jsneed\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" 
        .CommandText = 'SQL goes here

 

[benisma]

Now it is giving me an "expected end of statement" at the first line continuation. I have tried a variety of things, and can't get past this. I think it's probably something I am overlooking. Here is the first part until it stops me.

range("C4").Select
With Selection.QueryTable
.Connection = "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\jsneed\My Documents\work for fleet 1-2006.mdb;DefaultDir=C:\Documents and Settings\jsneed\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = "SELECT cash.region, cash.district, cash.store, cash.line, cash.iplcd, cash.ipcode, cash.cash_sls_total" _
, _

Any ideas?

 

[PHV]

Replace this:
, _
with this:
& ", 1-(cash.cash_cost_...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[benisma]

It worked!! Thank you all so much for your help.

 

[benisma]

Foe some reason, I can not get the query to refresh on the worksheet through VBA. I have tried:
Sheets("detail").querytables(1).refresh
worksheets("detail").backgroundquery.refresh

The first that I tried was the Excel command
.refresh background query:=false

What can I use? It will save the workbook but it has the old info in it when I go back to check it.

 

[SkipVought]

Are you ABSOLUTELY sure that you have no other QueryTables on that sheet?

Try this...

dim qt as querytable
for each qt in Sheets("detail").querytables
  qt.refresh
next

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[PHV]

You may also consider ActiveWorkbook.RefreshAll

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[benisma]

Skip,
I am not absolutely sure of the number of query tables. I know that I built this query and this is the only thing on the page. On the next worksheet, there is a pivot table. But, I have built this out of an Access database that has 7 different queries linked to it. I am sorry I am being so thick headed, but I just can't get it. I tried copying and pasting the code, but I am getting
"run time error'1004'sql syntax error"
Thanks for the help.

 

[SkipVought]

That'sw not my code, it's your query!

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[benisma]

Sorry, I am having such a rough time of it lately. I will work on the query.