LEFT JOIN produces #Error in Query

[Cottonshirt]

I have two SELECT queries:



qry_400m_OG_h and qry_400m_OG_q

they have data on consecutive rounds of an athletic tournament, the h stands for heat and the q stands for quarter finals. the object of the exercise is to have a dataset with perfomances from the 2nd round (quarter finals) listed alongside performances from the 1st round heats. so we end up with something like this:

Bob Smith (CAN) 3 h2 48.7 3 q2 48.6

obviously, not every 1st round competitor gets into the 2nd round, so some fields will be left blank.

so, I put the two queries together in a third query, below:

SELECT qry_400m_OG_h.fname, qry_400m_OG_h.sname, qry_400m_OG_h.code, qry_400m_OG_h.sol, qry_400m_OG_h.cha, qry_400m_OG_h.ordh, qry_400m_OG_h.heat, qry_400m_OG_q.ordq, qry_400m_OG_q.markq, qry_400m_OG_h.order
FROM qry_400m_OG_h LEFT JOIN qry_400m_OG_q ON (qry_400m_OG_h.sol = qry_400m_OG_q.sol) AND (qry_400m_OG_h.code = qry_400m_OG_q.code) AND (qry_400m_OG_h.sname = qry_400m_OG_q.sname) AND (qry_400m_OG_h.fname = qry_400m_OG_q.fname)
ORDER BY qry_400m_OG_h.sol, qry_400m_OG_h.order;

this kind of works. where there is a performance in the 2nd round, this query returns accurate results. where there is no performance in the 2nd round, the field for ordq is left blank as expected, but the field for markq has #Error.

my understanding is, that this means that ACCESS is confused about what type of variable markq is.

so, I run the following SELECT query:

SELECT qry_400m_OG_q.markq, VarType([markq]) AS test
FROM qry_400m_OG_q;

which leaves no doubt that markq is a type 8, a string. which is exactly what I expect it to be.

I would very much appreciate some clue as to what I'm doing wrong?


many thanks.

[JoeM]

It might be best if you are able to upload a sample of your database for analysis.
Just be sure to remove any sensitive data (or "dummy up" the data), if necessary.

[Cottonshirt]

as suggested, I attach a stripped down version of the database.

athletic_sample.accdb


many thanks,

[JoeM]

OK. I am at work right now and cannot download that file from here (corporate security policy prevents me from doing so), but I can take a look at it tonight when I am at home.

[ArviLaanemets]

Change qry_400m_OG_hq

SELECT qry_400m_OG_h.fname, qry_400m_OG_h.sname, qry_400m_OG_h.code, qry_400m_OG_h.sol, qry_400m_OG_h.cha, qry_400m_OG_h.ordh, qry_400m_OG_h.heat, qry_400m_OG_q.ordq, Iif(Nz(qry_400m_OG_q.ordq,"")="",Null,qry_400m_OG_q.markq) AS [markq], qry_400m_OG_h.order
FROM qry_400m_OG_h LEFT JOIN qry_400m_OG_q ON (qry_400m_OG_h.sol = qry_400m_OG_q.sol) AND (qry_400m_OG_h.code = qry_400m_OG_q.code) AND (qry_400m_OG_h.sname = qry_400m_OG_q.sname) AND (qry_400m_OG_h.fname = qry_400m_OG_q.fname);

It looks like your function Racetime() returns an error when the record is empty.

[Cottonshirt]

thank you,

your code is rather curious.

the Nz() function takes two arguments. the first one is a variable, and the second one is what to do if the variable is Null.

your IIf() statement, therefore, says: If the variable is Null, and NZ() would return "", call it Null.

but ACCESS wouldn't be able to do that unless the variable was Null to start with. so all your code says is: If it's Null, call it Null.

I therefore stripped out the IIf() part and just used Nz([qry_400m_OG_q.ordq],"") which says: if the variable is Null, replace it with this empty string.

and that works.

if I'm missing some deeply profound nuance to the additional IIf() then please don't hesitate to correct me.

obviously, the "correct" way to do this would be to amend the Racetime() function so that it knows what to do when the record is empty, but I don't have the VBA for that.


many thanks for your help.

[ArviLaanemets]

I simply din't dig deep enough into your source queries to be sure what they returned exacty. Nz() was there for case when the source query returns Null somehow - to be on safe side.