QueryDef multiple parameter

[Victoria.sv]

Dear all! Please help! Is it possible to set the paramentres as
something like "1006 or 1106" ????????

Option Compare Database
Private Sub Command78_Click()
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef


Set db = CurrentDb()
Set qdf = db.QueryDefs("FinalXXMissed2")


qdf.Parameters(0) = 1006

With Me!List
.RowSourceType = "Table/Query"
Set .Recordset = rs
End With

End Sub


THANKS a LOT!!!

Victoria

 

[Stefan Hoffmann]

hi Victoria,

Dear all! Please help! Is it possible to set the paramentres as
something like "1006 or 1106" ????????

Yes, if you pass it as a string, but i assume that is not what you want.

Set db = CurrentDb()
Set qdf = db.QueryDefs("FinalXXMissed2")

If you have a condition like

"... WHERE A = 1023 OR A = 1024"

Then you must use two parameters:

"... WHERE A = P1 OR A = P2"

with

qdf.Parameters(0) = 1006

qdf.Parameters(1) = 1106


mfG
--> stefan <--

 

[Victoria.sv]

"""Stefan Hoffmann ÐÉÓÁÌ(Á):
"""

hi Victoria,


Yes, if you pass it as a string, but i assume that is not what you want.

If you have a condition like

"... WHERE A = 1023 OR A = 1024"

Then you must use two parameters:

"... WHERE A = P1 OR A = P2"

with
qdf.Parameters(1) = 1106


mfG
--> stefan <--

Dear Stefan,
Sorry, but in this case I get the error 3265 ( Item not fount in this
collection). Frankly speaking I am going to use the variable for
parameter and it should change the parameter depending on the data from
the form, so the quantity of "OR" each time will be different
;(((((((((((

Victoria

 

[Stefan Hoffmann]

hi Victoria,

Sorry, but in this case I get the error 3265 ( Item not fount in this
collection). Frankly speaking I am going to use the variable for
parameter and it should change the parameter depending on the data from
the form, so the quantity of "OR" each time will be different

Without the SQL of your query and the code it is not that easy to guess.
So post them.


mfG
--> stefan <--

 

[Victoria.sv]

"""Stefan Hoffmann ÐÉÓÁÌ(Á):
"""

hi Victoria,


Without the SQL of your query and the code it is not that easy to guess.
So post them.


mfG
--> stefan <--

Dear Stefan, thank you so much for your help! Sorry to trouble you !!

My query "FinalXXMissed" consists of several subqueries and tables.


1. Code for "FinalXXMissed" is

"SELECT operators!OperatorID AS ID, XMissed.TAP, operators!Name AS
îÁÚ×ÁÎÉÅ, [pasporta sdelki]![Debitor No] AS äÅÂÉÔÏÒ,
XMissed!Period AS ðÅÒÉÏÄ, XMissed!Amount AS [óÕÍÍÁ SDR]
FROM (XMissed INNER JOIN [pasporta sdelki] ON XMissed.TAP = [pasporta
sdelki].TAP) INNER JOIN operators ON XMissed.TAP = operators.TAP;"

2. Query XMissed code is

SELECT Xamounts.TAP, Xamounts.Period, Xamounts.Amount, Rates.RURSDR
FROM (XReceived RIGHT JOIN Xamounts ON XReceived.TAPper =
Xamounts.TapPer) INNER JOIN Rates ON Xamounts.Period = Rates.Period
WHERE (((XReceived.TAPper) Is Null));


3. Query Xamounts code is

SELECT AmountsIn.TapPer, AmountsIn.Period, AmountsIn.Amount,
AmountsIn.TAP
FROM AmountsIn
WHERE (((AmountsIn.Period)=[]));

4. Query XReceived code is

SELECT operators.Name, [pasporta sdelki].[Debitor No],
Invoices.OperatorID, Invoices.TAPName, Invoices.TAPper,
Invoices.InvoicePeriod, operators.TAP3
FROM [pasporta sdelki] INNER JOIN (operators INNER JOIN Invoices ON
operators.TAP = Invoices.TAPName) ON ([pasporta sdelki].TAP =
Invoices.TAPName) AND ([pasporta sdelki].TAP = operators.TAP)
WHERE (((Invoices.InvoicePeriod)=[]));


Sorry, I do not know how to explain else...... The goal is to identify
the records which ARE in table AmountsIN and not presented in table
invoices.....

Really appreciate your assistance....

Victoria

 

[Stefan Hoffmann]

hi Victoria,

My query "FinalXXMissed" consists of several subqueries and tables.
1. Code for "FinalXXMissed" is

No parameters used in here.

2. Query XMissed code is

No parameters used in here.

3. Query Xamounts code is
SELECT AmountsIn.TapPer, AmountsIn.Period, AmountsIn.Amount,
AmountsIn.TAP
FROM AmountsIn
WHERE (((AmountsIn.Period)=[]));

Parameter used in here.

4. Query XReceived code is
SELECT operators.Name, [pasporta sdelki].[Debitor No],
Invoices.OperatorID, Invoices.TAPName, Invoices.TAPper,
Invoices.InvoicePeriod, operators.TAP3
FROM [pasporta sdelki] INNER JOIN (operators INNER JOIN Invoices ON
operators.TAP = Invoices.TAPName) ON ([pasporta sdelki].TAP =
Invoices.TAPName) AND ([pasporta sdelki].TAP = operators.TAP)
WHERE (((Invoices.InvoicePeriod)=[]));

Parameter used in here.

Rewrite the queries with parameters:

PARAMETERS ParamPeriod1 Date;
SELECT AmountsIn.TapPer, AmountsIn.Period, AmountsIn.Amount,
AmountsIn.TAP
FROM AmountsIn
WHERE AmountsIn.Period=[ParamPeriod1];

and

PARAMETERS ParamPeriod2 Date;
SELECT operators.Name, [pasporta sdelki].[Debitor No],
Invoices.OperatorID, Invoices.TAPName, Invoices.TAPper,
Invoices.InvoicePeriod, operators.TAP3
FROM [pasporta sdelki] INNER JOIN (operators INNER JOIN Invoices ON
operators.TAP = Invoices.TAPName) ON ([pasporta sdelki].TAP =
Invoices.TAPName) AND ([pasporta sdelki].TAP = operators.TAP)
WHERE Invoices.InvoicePeriod=[ParamPeriod2];

Now you should be able to address both parameters.


mfG
--> stefan <--