Query with parameters?

[Gasman]

Hi all,

I have created a query qryInvoice2 that will produce what I want to export to Excel for emailing on. I had to create qryInvoice1 to supply some of the data as I got the joins problem again.

I will be creating the queries in VBA, but am a little puzzled on how to go about it.

qryInvoice1 at present has a date parameter in it which I currently key in when asked, but it will eventually come from an unbound textbox on a form.

qryInvoice2 will also have a parameter that will be supplied from a recordset

My plan of attack is
Read recordset of Submitters
Loop through recordset of submitters
For each submitter execute qryInvoice2 with SubmitterID as parameter
Write data from qryInvoice2 to Excel

Repeat until eof recordset of Submitters

However my form is calling qryInvoice2, so how do I get the date on the form to the first query.

Is my structure incorrect that is causing me this problem.
I am trying to base this process on something I created at another workplace a year or so ago and is shown below as well for clarity.

As you can see I have tried to give the text control value to qryInvoice1, but I get the error message 'two few parameters Expected 1'

At present I am just trying to build the process in small steps, testing as I go.

qryinvoice1

PARAMETERS [Forms]![frmCreateInvoices]![txtInvoiceDate] DateTime;
SELECT tblStockTraded.TradeDate, tblSVSTrades.TradeType, tblSVSTrades.NetCost, tblSVSTrades.BuySell, tblStockTraded.Stock, tblStockTraded.IntroducerInvoicedDate, tblSVSTrades.SVSTradesID, tblSVSTrades.SVSAccount
FROM tblStockTraded INNER JOIN tblSVSTrades ON (tblStockTraded.Stock = tblSVSTrades.Stock) AND (tblStockTraded.TradeDate = tblSVSTrades.TradeDate);

qryInvoice2 (without extra criteria for submitterID)

SELECT qryInvoice1.TradeDate, tblClient.SVS_Account, tblClient.Forename, tblClient.Surname, qryInvoice1.TradeType, qryInvoice1.NetCost, qryInvoice1.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, qryInvoice1.Stock, tblIntroCommission.SubmitterID
FROM ((qryInvoice1 INNER JOIN tblCommission ON qryInvoice1.SVSTradesID = tblCommission.TradeID) INNER JOIN tblClient ON qryInvoice1.SVSAccount = tblClient.SVS_Account) INNER JOIN (tblIntroCommission INNER JOIN tblSubmitter ON tblIntroCommission.SubmitterID = tblSubmitter.SubmitterID) ON tblCommission.CommissionID = tblIntroCommission.CommissionID;

Old code

Private Sub cmdAllocate_Click()
On Error GoTo Err_Handler

Dim dbsPA As Database
Dim rstWA As Recordset
Dim rstPA As Recordset
Dim strSQLWA As String, strSQLPA As String
Dim strProduct As String, strMethod As String, StrQueue As String, strUser As String, strProgress As String
Dim intAllocate As Integer, intLoop As Integer, lngTotalAllocated As Long
Dim curLimit As Long, curAmount As Long
Dim blnShort As Boolean



'Set dbsPA = OpenDatabase("PA_Allocation")
Set dbsPA = CurrentDb()

'SQL statement to get Payment Analysts

strSQLPA = "SELECT Analyst.File_ID, Analyst.Full_Name, Analyst.Queue, Analyst.Product, Analyst.Pay_Method, Analyst.Workstream, Analyst.Allocation, Analyst.Received, Analyst.Working "
strSQLPA = strSQLPA & "FROM Analyst WHERE (((Analyst.Working)=True)) ;"

' SQL statemnt to get data to be allocated

Set rstPA = dbsPA.OpenRecordset(strSQLPA)

'WE are progressing, so open Progress form that will act as a status update to user

'DoCmd.OpenForm "Progress"
'Forms!Progress.txtProgress.Value = " Starting Allocation..."
'Forms!Progress.Refresh

Do While Not rstPA.EOF
' First get the extra criteria for the recordset

    strProduct = Chr$(39) & rstPA.Fields("Product") & Chr$(39)
    strMethod = Chr$(39) & rstPA.Fields("Pay_Method") & Chr$(39)
    StrQueue = Chr$(39) & rstPA.Fields("Queue") & Chr$(39)
    strUser = rstPA.Fields("Full_Name")
    intAllocate = rstPA.Fields("Allocation")
' Payment limits are £5000 or £100,000
    If rstPA.Fields("Workstream") = "Under 5K" Then
        curLimit = 5000
    Else
        curLimit = 100000
    End If
    
strSQLWA = "SELECT Daily_Work_Allocation.Amount, Daily_Work_Allocation.Process_Payment_Cashiers_allocated_to_name, Daily_Work_Allocation.payment_method, Daily_Work_Allocation.product_01, Daily_Work_Allocation.Payment_Case_Awaiting_Payment_allocated_to_name FROM Daily_Work_Allocation "
strSQLWA = strSQLWA & "WHERE (((Daily_Work_Allocation.Payment_Case_Awaiting_Payment_allocated_to_name) = '') AND ((Daily_Work_Allocation.Process_Payment_Cashiers_allocated_to_name)= " & StrQueue & ") AND ((Daily_Work_Allocation.payment_method)=" & strMethod & ") AND ((Daily_Work_Allocation.product_01)=" & strProduct & ")"
strSQLWA = strSQLWA & " AND ((Daily_Work_Allocation.Status) = '" & Me.cmbStatus & "'));"

    ' MsgBox strSQLWA
    
    Set rstWA = dbsPA.OpenRecordset(strSQLWA, dbOpenDynaset)
    
    intLoop = 0
    If Not rstWA.EOF Then
        rstWA.MoveFirst
      Else
        MsgBox "No payments found for " & strProduct & " " & strMethod & " for queue " & StrQueue
    End If
       
'    Debug.Print rstWA.RecordCount
    Do While intLoop < intAllocate And Not rstWA.EOF
        curAmount = rstWA.Fields("amount")
' Check the PA is authorised for the amount of payment
        If curAmount <= curLimit Then
            With rstWA
                .Edit
                .Fields("Payment_Case_Awaiting_Payment_allocated_to_name").Value = strUser
                .Update
            End With
            intLoop = intLoop + 1
        End If
        rstWA.MoveNext
    Loop
        lngTotalAllocated = lngTotalAllocated + intLoop
'Now update Analyst table with amount allocated.
    With rstPA
        .Edit
        .Fields("Received").Value = .Fields("Received").Value + intLoop
        .Update
    End With

' Now close WA ready for next select
    rstWA.Close
    rstPA.MoveNext
Loop

' Now refresh the Crosstab datasheet
' Me.[Daily_Work_Allocation_Crosstab_subform].Form.Refresh
    
    Set dbsPA = Nothing
    Set rstPA = Nothing
    Set rstWA = Nothing
    

Me.Refresh
MsgBox lngTotalAllocated & " payments allocated..."

Err_Exit:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Err_Exit

End Sub

 

[Minty]

In qryInvoice1 you don't appear to have actually used the parameter as part of a Where clause?

 

[Gasman]

Ah,

I had it in there and then moved it to the parameters screen
Must have cut as pasted not copied and pasted. :-(
That makes me question what is the benefit of the PARAMETERS screen if you can get away with it in the criteria of the design window on it's own.
It is meant to filter on the tblStockTraded.IntroducerInvoicedDate
What I want to do is flag up the stocks to invoice for in the StockTraded table, and then select all the records in SVSTrades for those stocks and tradedates.

 

[CJ_London]

what is the benefit of the PARAMETERS screen if you can get away with it in the criteria of the design window on it's own.

Benefits are:

1. you can set the datatype which reduces the risk of a query failing due to a type mismatch.

2. Not sure, but probably the query will execute a little bit quicker on larger datasets because it is not having to interpret a variant

3. You can use code to set the parameters in a querydef rather than needing to build a criteria string to pass as the criteria parameter

 

[Gasman]

CJ_London,

Now I have seen that is previous posts a good while back and seem to recall I thought that was a good way of doing things. Have the basic query set up and just pass the parameters through.
However all I have found at present is to declare the SQL string to a query and then use the querydef. For that I thought you may as well just use the string itself?

 

[CJ_London]

I don't disagree, but it is simpler to have code which just assigns a value or values rather than to build a criteria with a all those 'and's and 'or's and necessary brackets. As a long time developer I tend to go the build a string route simply because I've been doing it forever.

I have to admit that with my style I rarely use queries (as objects) but build them in VBA as required and use .execute. Partly because I don't want to risk exposure of the query to meddling by others and partly because there can be hundreds of variations which I can manage better in code by being able to document them as they are built - not to mention having to come up with hundreds of meaningful names for the queries.

 

[Minty]

One other not mentioned gotcha - If you have a crosstab query with parameters you have to declare them otherwise it wont run.

And from memory the error it gives is about as helpful as Darth Vader on a grumpy day...

 

[Gasman]

OK, after a nights rest I am back on it.

Had some mysterious problem with bracketing for the parameter, even though I went through the build process for syntax and content?

Anyway I now have the form and a button which runs qryInvoice2 directly via a macro and I get the desired result.
However if I try and run the code below

Private Sub Command4_Click()
Dim dbsCurr As Database
Dim rstTrades As Recordset
Dim strSQL As String

Set dbsCurr = CurrentDb()

strSQL = "SELECT qryInvoice1.TradeDate, tblClient.SVS_Account, tblClient.Forename, tblClient.Surname, qryInvoice1.TradeType, qryInvoice1.NetCost, qryInvoice1.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, qryInvoice1.Stock, tblIntroCommission.SubmitterID "
strSQL = strSQL & "FROM ((qryInvoice1 INNER JOIN tblCommission ON qryInvoice1.SVSTradesID = tblCommission.TradeID) INNER JOIN tblClient ON qryInvoice1.SVSAccount = tblClient.SVS_Account) INNER JOIN (tblIntroCommission INNER JOIN tblSubmitter ON tblIntroCommission.SubmitterID = tblSubmitter.SubmitterID) ON tblCommission.CommissionID = tblIntroCommission.CommissionID;"
'strSQL = strSQL & "WHERE [qryInvoice1].[Invoice Date] = #07/19/2016#;"

Set rstTrades = dbsCurr.OpenRecordset(strSQL, dbOpenDynaset)
End Sub

I get Too few parameters, expected 1 ??
Yet the form is open and a date is present in the control.

What am I missing? Is there a better way to approach this? I am happy to create the code in VBA where needed if there is a better approach?

TIA

 

[CJ_London]

looks like you have a superfluous ; in your FROM line - needs to be replaced with a space

 

[Gasman]

CJ,

Thank you for the quick reply.

I can only find one ";" in the FROM line?
The last line is commented out. Is this what you are referring to?
I did not do the find by eye, but the Find function?

Could I perhaps do this a better way?
What I am trying to achieve is

Mark records in tblStockTraded with a date. Done
Use these records to find matching records in tblSVSTrades
Using those records find the clients for the trades and Submitter details for the clients
Output various fields to Excel (once I have the correct data, that is the easy bit )

 

[CJ_London]

sorry didn't notice that! you don't actually need the ; anyway, will run perfectly OK without it.

bracket count looks OK but think you need an additional ( after FROM and another ) after the last one. But suggest you debug.print the sql and copy/paste to the query window - or build it in the query window and compare

 

[Gasman]

sorry didn't notice that! you don't actually need the ; anyway, will run perfectly OK without it.

OK, thanks for that.

bracket count looks OK but think you need an additional ( after FROM and another ) after the last one. But suggest you debug.print the sql and copy/paste to the query window - or build it in the query window and compare

I tend to build the sql in the design window and copy and paste to code and then amend. That is why the ';' is in there.

I'll try the debug.print method as well. Thank you.

 

[CJ_London]

I tend to build the sql in the design window and copy and paste to code and then amend.

So do I, so suggests something has got lost in translation when copy/pasting

 

[Gasman]

I cannot see what is different?

here is the code

Private Sub Command4_Click()
Dim dbsCurr As Database
Dim rstTrades As Recordset
Dim strSQL As String

Set dbsCurr = CurrentDb()

strSQL = "SELECT qryInvoice1.TradeDate, tblClient.SVS_Account, tblClient.Forename, tblClient.Surname, qryInvoice1.TradeType, qryInvoice1.NetCost, qryInvoice1.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, qryInvoice1.Stock, tblIntroCommission.SubmitterClientID "
strSQL = strSQL & " FROM ((qryInvoice1 INNER JOIN tblCommission ON qryInvoice1.SVSTradesID = tblCommission.TradeID) INNER JOIN tblClient ON qryInvoice1.SVSAccount = tblClient.SVS_Account) INNER JOIN (tblIntroCommission INNER JOIN tblSubmitter ON tblIntroCommission.[SubmitterClientID] = tblSubmitter.SubmitterID) ON tblCommission.CommissionID = tblIntroCommission.CommissionID"

Set rstTrades = dbsCurr.OpenRecordset(strSQL, dbOpenDynaset)
End Sub

here is what ? strSQL shows (I've split it for clarity)

SELECT qryInvoice1.TradeDate, tblClient.SVS_Account, tblClient.Forename, tblClient.Surname, qryInvoice1.TradeType, qryInvoice1.NetCost, qryInvoice1.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, qryInvoice1.Stock, tblIntroCommission.SubmitterClientID  FROM ((qryInvoice1
 INNER JOIN tblCommission ON qryInvoice1.SVSTradesID = tblCommission.TradeID)
 INNER JOIN tblClient ON qryInvoice1.SVSAccount = tblClient.SVS_Account)
 INNER JOIN (tblIntroCommission
 INNER JOIN tblSubmitter ON tblIntroCommission.[SubmitterClientID] = tblSubmitter.SubmitterID) ON tblCommission.CommissionID = tblIntroCommission.CommissionID

here is the SQL from the design window

SELECT qryInvoice1.TradeDate, tblClient.SVS_Account, tblClient.Forename, tblClient.Surname, qryInvoice1.TradeType, qryInvoice1.NetCost, qryInvoice1.BuySell, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, qryInvoice1.Stock, tblIntroCommission.SubmitterClientID
FROM ((qryInvoice1 INNER JOIN tblCommission ON qryInvoice1.SVSTradesID = tblCommission.TradeID) INNER JOIN tblClient ON qryInvoice1.SVSAccount = tblClient.SVS_Account) INNER JOIN (tblIntroCommission INNER JOIN tblSubmitter ON tblIntroCommission.SubmitterClientID = tblSubmitter.SubmitterID) ON tblCommission.CommissionID = tblIntroCommission.CommissionID;

I've removed the parameter entry and only have the form control as criteria. Form is open as before at the time of running.

Again running the query directly shows the data (as long as the form is open) else request the parameter. When I try and run the above code I get the expected parameters 1 error.

I cannot see any difference?

 

[CJ_London]

your sql includes qryInvoice1 - is there a parameter in that? perhaps referring to a form?

Normally when you get a parameter error, it tells you which one. The other possibility is the parameter is null, so try the nz function

 

[Gasman]

Yes, the date parameter is in qryInvoice1. I have had to do it that way as I kept getting the joins error. :-( So I get some data, the key being the date the stocks were traded. Then using that data I can link the other tables to get the output I need to export to Excel.

As I need (or think I need) to do it in VBA along the lines I did that other project.

I was thinking that as I had the form open with a value in the control and that control is the parameter for the first query, that when I run qryInvoice2, it of course runs qryInvoice1 to start with. It works fine when the query is run from a macro, so I *thought* the same could be done via VBA, all as long as the form is open.

However just before I left work I found I could link fields with a simple where clause.
Need to try it out more tomorrow.

This is a slow long learning curve.:-(
I thought I was doing well with the structure that will allow the data to be processed the way it will have to, but did not realise it would be such a task to bring the data together.

 

[Cronk]

There does not seem to be enough bracketing in the JOIN part.

Debug.print strSQL during run time and paste back into the query sql window.

 

[Gasman]

There does not seem to be enough bracketing in the JOIN part.

Debug.print strSQL during run time and paste back into the query sql window.

Hi Cronk,

I posted the debug output in post 14, albeit edited at the INNER statements.
I'll run it again tomorrow and paste as is.

 

[CJ_London]

you can't execute a query string which has parameters in it - even if they are in a referenced query. Although I've not tried it, referring back to the start of this thread, you could try predeclaring the parameters in the query and then assigning it a value through the querydef. might work.

Other way is to bring the sql of the query into your executed sql.

e.g. simple exmaple

query1:

SELECT * FROM myTable WHERE ID=forms!myform!ID

then you want an executeable sql string

currentdb.execute "select * FROM query1 inner join table2 on query1.fielda=table1.fielda"

won't work because of the parameter. But this will

currentdb.execute "select * FROM (SELECT * FROM myTable WHERE ID=" & forms!myform!ID & ") AS query1 inner join table2 on query1.fielda=table1.fielda"

 

[Cronk]

CJ, pardon me for asking but are you sure it will execute, with or without the criteria (parameter)?

And if it does, what does it achieve? Create an unreferenced recordset in memory?