SQL Query is not working - nothing new ... (1 Viewer)

[daievans]

Set prst = CurrentDb.OpenRecordset("SELECT Count ([AHC_data].[AccountNo]) AS CountOfAccountNo, DSum ([AHC_data].[Balance],AHC_data) AS TotalBal " & _
                                            "FROM [AHC_data] " & _
                                            "WHERE [AHC_data].[Processedfile] = '" & strFile & "' " & _
                                            "GROUP BY [AHC_data].[Processedfile], DSum (AHC_data.[Balance],AHC_data) ; ")

\\

returns a 3051 Too few parameters. Expected 1.

I *think* the problem is in the syntax of CurrentDB.Open REcordset, but as usual at this point of my day, I'm flummoxed ... :banghead:

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, you don't have the required quote delimiters around the DSum() arguments.

 

[daievans]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, you don't have the required quote delimiters around the DSum() arguments.

Thank you! That is avery useful and I've already integrated it - greatly appreciate your tip!

 

[pbaldy]

Happy to help!

 

[daievans]

So I've been beavering away at my SQL stuff (with Baldy's clever little scheme with Debug.Print strSQL doing yeoman's work for me ...

I created a query in Access' design field and the resulting SQL provides a perfect result - it looks like this in the SQL view of Design

SELECT Count(AHC_data.AccountNo) AS CountOfAccountNo, DSum("[Balance]","[AHC_data]") AS TotalBal, AHC_data.Processedfile
FROM AHC_data
WHERE (((AHC_data.Processedfile)=" SJF_AHC20130430-XXX-01.TXT"))
GROUP BY AHC_data.Processedfile, AHC_data.Processedfile;

When I try to reproduce this in my strSQL I get this out

SELECT [AHC_data] COUNT (AHC_data.[AccountNo]) AS CountOfAccountNo, DSum[Balance],[AHC_data] AS TotalBal,AHC_data.Processed_file FROM  [AHC_data] WHERE  [AHC_data].[Processedfile] = ' SJF_AHC20130430-XXX-01.TXT ' GROUP BY  AHC_data.Processed_file, AHC_data.Processed_file ;

So - I did the old-fashioned thing and printed both versions on a piece of 'paper'! and lo and behold .....


Thanks PBaldy

 

[pbaldy]

Glad it worked for you!