Completely Stumped! How is this happening!!! (1 Viewer)

[BlueIshDan]

Hello lads & lassies!

I have myself in a sticky situation. I've been assigned to do some work on this project management database that was built by numerous people in the past.

I need an explanation as to why this query is capable of returning 2 of 38 """possible""" records.

Query

SELECT 
	tblProArcEWOs.[Project ID], 
	tblProArcEWOs.[EWO Number], 
	tblProArcEWOs.Rev, 
	tblProArcEWOs.Description, 
	tblProArcEWOs.Date, 
	tblProArcEWOs.STATUS, 
	tblProArcEWOs.[Project ID], 
	tblProArcEWOs.[EWO Number], 
	tblProArcEWOs.Discipline, 
	tblProArcEWOs.Resource, 
	tblEWO_AdditionInfo.[Planned Date], 
	tblEWO_AdditionInfo.[Forecast Date], 
	tblEWO_AdditionInfo.[Actual Date], 
	tblEWO_AdditionInfo.[EWO Comments]

FROM
	tblProArcEWOs 
	LEFT JOIN tblEWO_AdditionInfo 
		ON (tblProArcEWOs.Rev = tblEWO_AdditionInfo.Rev) 
		AND (tblProArcEWOs.[EWO Number] = tblEWO_AdditionInfo.[EWO Number])

WHERE tblProArcEWOs.[Project ID] Like [Forms]![frmMain]![cboProjectNum]

ORDER BY tblProArcEWOs.[Project ID];

When [Forms]![frmMain]![cboProjectNum] is requested as a parameter I put in C00031. There are 38 records in tblEWO_AdditionInfo with that project number. The query returns 2 records.

THE CATCH IS!!!!!!!!!! tblProArcEWOs is empty! hahaha
WHAT'S GOING ON HERE!

 

[namliam]

doesnt the other 36 have spaces in them ?

Regardless given the left join and where clause I dont think it should return anything at all?

 

[CJ_London]

because you are putting criteria on the join here

AND (tblProArcEWOs.[EWO Number] = tblEWO_AdditionInfo.[EWO Number]

You need to add an

OR tblEWO_AdditionInfo.[EWO Number] is null

into the critieria as well - leave you to sort out where the brackets need to go

Ignore this - I'm talking rubbish

 

[BlueIshDan]

Thank you CJ for your quick response.

I'm not asking what to do to make a correct query. I'm asking how in the world this query is even returning a result.

 

[BlueIshDan]

doesnt the other 36 have spaces in them ?

Regardless given the left join and where clause I dont think it should return anything at all?

Exactly what my thoughts are. I'm blown away by this hahaha!

No spaces. No differences between the 2 results and the rest of them.

 

[jdraw]

My concern is with the WHERE clause

Like [Forms]![frmMain]![cboProjectNum]
with no * before and/or after the control name

I think that should work as an =.
I am surprised it returns anything else.

 

[CJ_London]

My mistake - I didn't see the whole thread.

I have occasionally come across a corrupt querydef, nothing visible and everything appears OK but does not return the expected results.

Perhaps worth a try to copy the sql to a new query? I usually pass it through notepad to make sure I don't copy any hidden characters.

What does the query return in respect of the data from tblProArcEWOs?

 

[namliam]

My concern is with the WHERE clause

Like [Forms]![frmMain]![cboProjectNum]
with no * before and/or after the control name

I think that should work as an =.
I am surprised it returns anything else.

Hence my 'spaces' suggestion, you are right though, no * or other wildcard(s) makes like work like =

 

[BlueIshDan]

Still nothing to do with trying to fix the query. I realize how screwed up the thing is lol.

Just trying to figure out HOW on earth does this return anything.

The messed up querydef seems to be the only viable answer thus far.

What does the query return in respect of the data from tblProArcEWOs?

THE CATCH IS!!!!!!!!!! tblProArcEWOs is empty! hahaha

 

[BlueIshDan]

The messed up querydef seems to be the only viable answer thus far.

Nope. I tested and received the same result.

 

[BlueIshDan]

Figured out what was going on and you'll never believe it!

tblProArcEWOs was not empty... the first 1993 of 4246 records were completely empty...
Making me expect that the table was empty when scrolling down to see all empty fields with no id...

Will someone take over this project for me? Like seriously!!!! LOL

Thanks for all your attention and help trying to solve this. <3