Full Version: Passing Parameters Via Vba In Access 2007
UtterAccess Forums > Microsoft® Access > Access Forms
sag
Hello,
I have an Access query, which needs three criteria.
I am passing these values via. VBA.
Set RLSales1 = RL.QueryDefs("GetAmtTemp")
RLSales1.Parameters(0) = DrCode
RLSales1.Parameters(1) = CurrMnth1
RLSales1.Parameters(2) = CurrMnth6
The second and third parameters are in one field with a Between [CurrMnth1] and [CurrMnth6]. These are both dates - however, the query is fine and runs without an error when I just run the query.
When in VBA though, it abends at Parameters(2) and says there is no current record. If I do not have a between..... in the query but just CurrMnth1 the query runs fine in VBA. It almost seems that I am not able to specify a second parameter.
Is it possible to help me?
Thank you.
SAG
Bob G
could you post the SQL of the query that fails ??
sag
Hello Bob,
I have not specified the SQL statement in VBA - I have created a query in Access. Here is the SQL statement from the query design:
Thank you.
SELECT [Invoice Detail].[Dr Code], Dr.[Dr Name], Dr.[Dr Category], [Invoice Detail].[Entry Month Year], Sum([Invoice Detail].[Reimbursement Claim]) AS [SumOfReimbursement Claim], Sum([Invoice Detail].[Reimbursement Amount Paid]) AS [SumOfReimbursement Amount Paid]
FROM Dr INNER JOIN [Invoice Detail] ON Dr.[Dr Code] = [Invoice Detail].[Dr Code]
GROUP BY [Invoice Detail].[Dr Code], Dr.[Dr Name], Dr.[Dr Category], [Invoice Detail].[Entry Month Year]
HAVING ((([Invoice Detail].[Dr Code])=[DlrCode]) AND (([Invoice Detail].[Entry Month Year]) Between [CurrMnth1] And [CurrMnth6]));
LPurvis
Hard to be sure with just this to go on...
Have you defined the parameters?
ARAMETERS [DlrCode] Text(50), [CurrMnth1] DateTime, [CurrMnth6] DateTime;
SELECT [Invoice Detail].[Dr Code], Dr.[Dr Name], Dr.[Dr Category], [Invoice Detail].[Entry Month Year], Sum([Invoice Detail].[Reimbursement Claim]) AS [SumOfReimbursement Claim], Sum([Invoice Detail].[Reimbursement Amount Paid]) AS [SumOfReimbursement Amount Paid]
FROM Dr INNER JOIN [Invoice Detail] ON Dr.[Dr Code] = [Invoice Detail].[Dr Code]
GROUP BY [Invoice Detail].[Dr Code], Dr.[Dr Name], Dr.[Dr Category], [Invoice Detail].[Entry Month Year]
HAVING ((([Invoice Detail].[Dr Code])=[DlrCode]) AND (([Invoice Detail].[Entry Month Year]) Between [CurrMnth1] And [CurrMnth6]));
I was guessing DlrCode is a Text field. Just guessing.
Are you sure the problem date is defined properly and unambiguously?
sag
I have defined DrCode as string and CurrMnth1 and CurrMnth6 as variants.
et RLSales1 = RL.QueryDefs("GetAmtTemp")
RLSales1.Parameters(0) = DrCode
RLSales1.Parameters(1) = CurrMnth1
RLSales1.Parameters(2) = CurrMnth6
I have other queries in the code as well and they run with the 0 and 1 parameters. It almost appears that there is a problem specifying the 2 (the third) parameter where it abends. Just running the query in Access itself works without a problem.
LPurvis
Can you just show your full code?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.