Access VBA SQL Statement too long

[Gregorys05]

Hi,

I am trying to run an SQL statement in VBA but it keep saying there are too few parameters. Below is the VBA SQL

SQL1 = "PARAMETERS [Forms]![NBV date]![Chosen Date] Text ( 255 );"
SQL1 = SQL1 & " SELECT [all meters - operating leases].[Lease type], [all meters - operating leases].[Current Supplier],"
SQL1 = SQL1 & " tblSuppliercode.Group_Supplier, Count([all meters - operating leases].[Meter type 2]) AS [CountOfMeter type 21],"
SQL1 = SQL1 & " Sum([all meters - operating leases].[Current Rental]) AS [SumOfCurrent Rental1], Sum([all meters - operating leases].[Asset cost]) AS [SumOfAsset cost1],"
SQL1 = SQL1 & " Sum(IIf([all meters - operating leases]![Asset cost]-(((DateDiff(""m"",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1)"
SQL1 = SQL1 & " *[all meters - operating leases]![Depn amount]))<=0,0,[all meters - operating leases]![Asset cost]-"
SQL1 = SQL1 & " (((DateDiff(""m"",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount])))) AS NBV,"
SQL1 = SQL1 & " Sum([all meters - operating leases].[Asset cost])-(Sum(IIf([all meters - operating leases]![Asset cost]-"
SQL1 = SQL1 & " (((DateDiff(""m"",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount]))<=0,0,"
SQL1 = SQL1 & " [all meters - operating leases]![Asset cost]-(((DateDiff(""m"",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1)"
SQL1 = SQL1 & " *[all meters - operating leases]![Depn amount]))))) AS Depn"
SQL1 = SQL1 & " FROM [all meters - operating leases] INNER JOIN tblSuppliercode ON [all meters - operating leases].[Current Supplier] = tblSuppliercode.Supplier"
SQL1 = SQL1 & " GROUP BY [all meters - operating leases].[Lease type], [all meters - operating leases].[Current Supplier], tblSuppliercode.Group_Supplier;"

When i look at it in the immediate window using debug.print i get

PARAMETERS [Forms]![NBV date]![Chosen Date] Text ( 255 ); SELECT [all meters - operating leases].[Lease type], [all meters - operating leases].[Current Supplier], tblSuppliercode.Group_Supplier, Count([all meters - operating leases].[Meter type 2]) AS [CountOfMeter type 21], Sum([all meters - operating leases].[Current Rental]) AS [SumOfCurrent Rental1], Sum([all meters - operating leases].[Asset cost]) AS [SumOfAsset cost1], Sum(IIf([all meters - operating leases]![Asset cost]-(((DateDiff("m",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1) *[all meters - operating leases]![Depn amount]))<=0,0,[all meters - operating leases]![Asset cost]- (((DateDiff("m",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount])))) AS NBV, Sum([all meters - operating leases].[Asset cost])-(Sum(IIf([all meters - operating leases]![Asset cost]- (((DateDiff("m",[all meters - operating leases]![Depn start date],[Form
s]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount]))<=0,0, [all meters - operating leases]![Asset cost]-(((DateDiff("m",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1) *[all meters - operating leases]![Depn amount]))))) AS Depn FROM [all meters - operating leases] INNER JOIN tblSuppliercode ON [all meters - operating leases].[Current Supplier] = tblSuppliercode.Supplier GROUP BY [all meters - operating leases].[Lease type], [all meters - operating leases].[Current Supplier], tblSuppliercode.Group_Supplier;

there is a break in the line around

s]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount]))<=0,0,

if i remove the space and paste into an access query it runs but when i try and run the VBA i get the error.#


Any ideas on how to fix this


This is how it looks in access query

PARAMETERS [Forms]![NBV date]![Chosen Date] Text ( 255 ); SELECT [all meters - operating leases].[Lease type], [all meters - operating leases].[Current Supplier], tblSuppliercode.Group_Supplier, Count([all meters - operating leases].[Meter type 2]) AS [CountOfMeter type 21], Sum([all meters - operating leases].[Current Rental]) AS [SumOfCurrent Rental1], Sum([all meters - operating leases].[Asset cost]) AS [SumOfAsset cost1], Sum(IIf([all meters - operating leases]![Asset cost]-(((DateDiff("m",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1) *[all meters - operating leases]![Depn amount]))<=0,0,[all meters - operating leases]![Asset cost]- (((DateDiff("m",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount])))) AS NBV, Sum([all meters - operating leases].[Asset cost])-(Sum(IIf([all meters - operating leases]![Asset cost]- (((DateDiff("m",[all meters - operating leases]![Depn start date],[Form
s]![NBV date]![Chosen Date])+1)*[all meters - operating leases]![Depn amount]))<=0,0, [all meters - operating leases]![Asset cost]-(((DateDiff("m",[all meters - operating leases]![Depn start date],[Forms]![NBV date]![Chosen Date])+1) *[all meters - operating leases]![Depn amount]))))) AS Depn FROM [all meters - operating leases] INNER JOIN tblSuppliercode ON [all meters - operating leases].[Current Supplier] = tblSuppliercode.Supplier GROUP BY [all meters - operating leases].[Lease type], [all meters - operating leases].[Current Supplier], tblSuppliercode.Group_Supplier;

Thank you

 

[SydneyGeek]

You can reduce the length of the SQL by aliasing the table names, and at some points you used table!field instead of table.field notation.

This SQL statement reduces the original code from 1833 to 1205 characters:

SQL1 = "PARAMETERS [Forms]![NBV date]![Chosen Date] Text ( 255 );"
SQL1 = SQL1 & " SELECT al.[Lease type], al.[Current Supplier],"
SQL1 = SQL1 & " sc.Group_Supplier, Count(al.[Meter type 2]) AS [CountOfMeter type 21],"
SQL1 = SQL1 & " Sum(al.[Current Rental]) AS [SumOfCurrent Rental1], Sum(al.[Asset cost]) AS [SumOfAsset cost1],"
SQL1 = SQL1 & " Sum(IIf(al.[Asset cost]-(((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)"
SQL1 = SQL1 & " *al.[Depn amount]))<=0,0,al.[Asset cost]-"
SQL1 = SQL1 & " (((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)*al.[Depn amount])))) AS NBV,"
SQL1 = SQL1 & " Sum(al.[Asset cost])-(Sum(IIf(al.[Asset cost]-"
SQL1 = SQL1 & " (((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)*al.[Depn amount]))<=0,0,"
SQL1 = SQL1 & " al.[Asset cost]-(((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)"
SQL1 = SQL1 & " *al.[Depn amount]))))) AS Depn"
SQL1 = SQL1 & " FROM [all meters - operating leases] AS al INNER JOIN tblSuppliercode AS sc ON al.[Current Supplier] = sc.Supplier"
SQL1 = SQL1 & " GROUP BY al.[Lease type], al.[Current Supplier], sc.Group_Supplier;"

EDIT just noticed... Why is the parameter Text when it contains a Date?

Denis

 

[Gregorys05]

thank you for this, i have now changed the code to the below:

But i now get
runtime error 3061
Too few parameters. Expected 1

But when i run the query in access it works fine.

SQL1 = " SELECT al.[Lease type], al.[Current Supplier],"
SQL1 = SQL1 & " sc.Group_Supplier, Count(al.[Meter type 2]) AS [CountOfMeter type 21],"
SQL1 = SQL1 & " Sum(al.[Current Rental]) AS [SumOfCurrent Rental1], Sum(al.[Asset cost]) AS [SumOfAsset cost1],"
SQL1 = SQL1 & " Sum(IIf(al.[Asset cost]-(((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)"
SQL1 = SQL1 & " *al.[Depn amount]))<=0,0,al.[Asset cost]-"
SQL1 = SQL1 & " (((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)*al.[Depn amount])))) AS NBV,"
SQL1 = SQL1 & " Sum(al.[Asset cost])-(Sum(IIf(al.[Asset cost]-"
SQL1 = SQL1 & " (((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)*al.[Depn amount]))<=0,0,"
SQL1 = SQL1 & " al.[Asset cost]-(((DateDiff(""m"",al.[Depn start date],[Forms]![NBV date]![Chosen Date])+1)"
SQL1 = SQL1 & " *al.[Depn amount]))))) AS Depn"
SQL1 = SQL1 & " FROM [all meters - operating leases] AS al INNER JOIN tblSuppliercode AS sc ON al.[Current Supplier] = sc.Supplier"
SQL1 = SQL1 & " GROUP BY al.[Lease type], al.[Current Supplier], sc.Group_Supplier;"

 

[SydneyGeek]

It looks like you have left off the PARAMETERS line at the top of the original.

Denis

 

[strive4peace]

> "Too few parameters. Expected 1"

this happens when you reference a control that is not filled out. Wrap ALL control references with NZ, ie:

nz([Forms]![NBV date]![Chosen Date])

 

[strive4peace]

instead of this:

sSQL = "blah blah"
sSQL = sSQL & "blah blah"

do this:

sSQL = "blah blah" _
      & "blah blah"

space underscore at the end of the line means the statement is continued

your names are waaay too long! Your fieldnames seem like they should be the field DESCRIPTIONS. Fieldnames should be short -- at least not any longer than the data they contain

also, you are making a LOT of references to controls on forms ... what is the query being used for?

 

[strive4peace]

I also just noticed this:

PARAMETERS [Forms]![NBV date]![Chosen Date] Text ( 255 )

shouldn't this be a Date/Time? Later in the query you add 1 ... cannot do that with text, it will just put a "1" on the end ...

 

[strive4peace]

ps, I would like to comment on your structure ... am a petroleum engineer ... assume this is O&G data from your names