---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :
Hi Sarah
The problem is that the OpenRecordset method is not executed by Access, but by DAO, the database engine. Access understands things such as open forms and the values contained in their controls, so when you execute a saved query containing a reference to something like [Forms]![rebill]![transonly], Access finds the value in that control and substitutes it into the QueryDef's SQL string before passing it on to DAO. However, when you execute an OpenRecordset from VBA, the SQL string is passed directly to DAO and is not pre-processed by Access, so [Forms]![rebill]![transonly] is seen as a parameter for which no value has been specified.
For OpenRecordset, then, you need to do your own substitutions of values by concatenating them into your SQL string. You are already doing this with most of your "parameter" values, such as:
"([a/r open].denied)<>" & [Forms]![rebill]![rebillall]
The problem is with the two embedded references in this clause:
… and (iif([Forms]![rebill]![transonly]=true,[a/r open].[cp]>9,[a/r open].[cp]<9) or iif([Forms]![rebill]![daycareonly]=true,[a/r open].[cp]<9,[a/r open].[cp]>9))
The easy fix is to substitute the values of those controls in the string:
… and (iif(" & [Forms]![rebill]![transonly] & "=true,[a/r open].[cp]>9,[a/r open].[cp]<9) or iif(" & [Forms]![rebill]![daycareonly] &=true,[a/r open].[cp]<9,[a/r open].[cp]>9))
However, a cleverer and more efficient method would be to ascertain first from the values of [transonly] and [daycareonly] what values of [cp] are of interest, and insert just one simple comparison – e.g. " and ([a/r open].[cp]>9)"
By the way, I think your current logic will never return records where [cp]=9. Is this what you intend?
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 3 April 2017 10:31
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Run time error '3061': too few parameters, expected 2
The following query works as a stand alone query, but results in the above error if run in vba:
stand alone:
SELECT [a/r open].*
FROM ([a/r open] LEFT JOIN Patient ON [a/r open].[patient id] = Patient.[patient id]) LEFT JOIN insurance ON [a/r open].insrcd = insurance.insrcd
WHERE (((([a/r open].[balance])<>0) AND [a/r open].private=true and ([a/r open].denied)<>-1) AND (([a/r open].[insrcd])= 'GHI') AND (([a/r open].[patient id]) Between 0 And 90128) AND (([a/r open].[sdate from]) Between #2/1/2015# And #3/28/2015#)) and (iif([Forms]![rebill]![transonly]=true,[a/r open].[cp]>9,[a/r open].[cp]<9) or iif([Forms]![rebill]![daycareonly]=true,[a/r open].[cp]<9,[a/r open].[cp]>9))
ORDER BY [a/r open].[invoice#];
VBA:
"SELECT [a/r open].* " & _
"FROM ([a/r open] LEFT JOIN Patient ON [a/r open].[patient id] = Patient.[patient id]) LEFT JOIN insurance ON [a/r open].insrcd = insurance.insrcd " & _
"WHERE (((([a/r open].[balance])<>0) AND [a/r open].private=true and ([a/r open].denied)<>" & [Forms]![rebill]![rebillall] & ") AND (([a/r open].[insrcd])= '" & [Forms]![rebill]![insr] & "') AND (([a/r open].[patient id]) Between " & [Forms]![rebill]![ln from] & " And " & [Forms]![rebill]![ln to] & ") AND (([a/r open].[sdate from]) Between #" & [Forms]![rebill]![pid from] & "# And #" & [Forms]![rebill]![pid to] & "#)) and (iif([Forms]![rebill]![transonly]=true,[a/r open].[cp]>9,[a/r open].[cp]<9) or iif([Forms]![rebill]![daycareonly]=true,[a/r open].[cp]<9,[a/r open].[cp]>9)) " & _
"ORDER BY [a/r open].[invoice#];"
Since I added:
'and (iif([Forms]![rebill]![transonly]=true,[a/r open].[cp]>9,[a/r open].[cp]<9) or iif([Forms]![rebill]![daycareonly]=true,[a/r open].[cp]<9,[a/r open].[cp]>9))'
to the VBA query it give me the error 3061, before than it was fine
I created the stand alone query by using print.debug and copy paste and found that it works fine.
I have tried google, but can not figure it out.
As always your help is greatly appreciated.
Thanks
Sarah
Posted by: sarahk@schemesoftware.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar