Query with parameters?

[Gasman]

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"

CJ,

I think you have hit the nail on the head there.
That would appear to match up with what is happening.

Back to the drawing board then.

 

[Gasman]

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?

Cronk,

I am planning to execute the sql in vba within loops for each introducer.

 

[CJ_London]

@cronk - you are right - it is not an action query so should be openrecordset.


Was quite late when I responded

 

[Gasman]

Does anyone see anything wrong with this method?

I removed the joins from the two tables in question that I had to put in qryInvoice1 and that has the date parameter/criteria and placed the criteria in the grid?
Date is hardcoded for now, but could be replaced by form control.

SELECT tblClient.SVS_Account, tblClient.Forename, tblClient.Surname, tblSubmitter.SubmitterName, tblIntroCommission.IntroCommission, tblIntroCommission.SubmitterClientID, tblSVSTrades.TradeDate
FROM tblClient, tblStockTraded, tblSVSTrades, tblCommission INNER JOIN (tblIntroCommission INNER JOIN tblSubmitter ON tblIntroCommission.SubmitterClientID = tblSubmitter.SubmitterID) ON tblCommission.CommissionID = tblIntroCommission.CommissionID
WHERE (((tblClient.SVS_Account)=[tblSVSTrades].[SVSAccount]) AND ((tblSVSTrades.Stock)=[tblStockTraded].[stock]) AND ((tblSVSTrades.TradeDate)=[tblStockTraded].[Tradedate]) AND ((tblStockTraded.IntroducerInvoicedDate)=#7/19/2016#) AND ((tblCommission.TradeID)=[tblSVSTrades].[SVSTradesID]));

This appears to give me the same data that the two queries did?

I might not have all the fields I require in this query at present. but I have all the data, so it is a case of just adding extra fields.

 

[CJ_London]

you can move joins to criteria, just don't understand why you need to do it

 

[Gasman]

Hi CJ,

Well I am thinking of this method due to
a) because of the problem I have had with the form parameter and (too me) the complexities of nesting queries. I have other queries that have had to be split due to the joins problem. This way I should be easily able to construct the SQL string from one query window?
b) In the design window I can see everything in one go
c) TBH it is easier for me to read and understand as proposed to inner and outer joins, and trying to understand which is causing the problem?

Remember I am not a professional like most of the people helping here, an enthusiastic amateur at the most and I must admit I was having a lot of problems with these joins.

I just wanted to be aware if this was leading down to a more difficult path further down the road, a bit like having spaces in field and control names?

Thanks for getting to the bottom of the problem.

 

[CJ_London]

only time joins are a problem is if you try to mix Cartesian 'joins' (i.e. no join) and left or right joins, or have inconsistent left/right joins (i.e. going in two directions - tbla left join to tblb and left join to tblc is OK, tblb left join tbla and tblc left join tbla is not.

Might be an idea for you to upload a screenshot of your relationships, it may be you have an inconsistency

 

[Gasman]

CJ,

I posted a relationship pic in the original post.?
I'm at home now so cannot report until Monday.
I'm fairly happy with the structure, might not be perfect but I believe it will handle our situations at present at least, but it is how to implement the joins I am also having difficulty with.
At present I am attempting to bring the data from Excel and use in parallel with the Excel workbook and see where the pitfalls are.