Solved Need Help (1 Viewer)

[KadeFoster]

Hey All,

I am not good with queries and my focus of my project has been my forms for getting the data in. Unfortunately one of the queries i had has broken and i cannot work out why, due to my lack of understanding and limited knowledge of them.

So i have a continuous form that "was" displaying my maps, and now nothing. Below is my form.

Below is my table PK-TCRAID

Below is the current code, that has stopped for some reason.

SELECT LevelT.Level, TCRAActiveMapsT.TCRAID, TCRAActiveMapsT.MapBlock, TCRAActiveMapsT.IsActive, TCRAActiveMapsT.LevelID, TCRAActiveMapsT.MineID
FROM TCRAActiveMapsT INNER JOIN LevelT ON TCRAActiveMapsT.LevelID = LevelT.LevelID
WHERE (((LevelT.Level) Like "*" & [Forms]![TCRAIDLookupF]![LevelSearch] & "*") AND ((TCRAMapDataT.TCRAID) Not In (SELECT TCRAID FROM TCRAInspectionT WHERE YEAR(INSPECTEDDATE)=YEAR(DATE()))) AND ((TCRAActiveMapsT.MapBlock) Like "*" & [Forms]![TCRAIDLookupF]![BlockSearch] & "*") AND ((TCRAActiveMapsT.IsActive)="Yes"));

I want to display all the maps from TCRAActiveMapsT that have not been inspected in the current year. The TCRAInspectionT.InspectedDate is where the date of the inspection has been done.

The SQL above was working but it is not now.

Please help, my project was going good until this happened and now i am stuck.

 

[The_Doc_Man]

Saying "was working but it is not now" doesn't tell us quite enough. If you open that query in Datasheet view, what specifically does it do? Produce nothing? Produce an error? Produce something incorrectly? When describing a problem, we need symptoms.

 

[Ranman256]

Run the query (not the form) with known criteria that will return a result.
make sure the joins are correct.

 

[arnelgp]

if it is working before, then there must be some Update on your computer
that might cause it to fail.

 

[GPGeorge]

Hey All,

I am not good with queries and my focus of my project has been my forms for getting the data in. Unfortunately one of the queries i had has broken and i cannot work out why, due to my lack of understanding and limited knowledge of them.

So i have a continuous form that "was" displaying my maps, and now nothing. Below is my form.
View attachment 103512


Below is my table PK-TCRAID
View attachment 103513

Below is the current code, that has stopped for some reason.

SELECT LevelT.Level, TCRAActiveMapsT.TCRAID, TCRAActiveMapsT.MapBlock, TCRAActiveMapsT.IsActive, TCRAActiveMapsT.LevelID, TCRAActiveMapsT.MineID
FROM TCRAActiveMapsT INNER JOIN LevelT ON TCRAActiveMapsT.LevelID = LevelT.LevelID
WHERE (((LevelT.Level) Like "*" & [Forms]![TCRAIDLookupF]![LevelSearch] & "*") AND ((TCRAMapDataT.TCRAID) Not In (SELECT TCRAID FROM TCRAInspectionT WHERE YEAR(INSPECTEDDATE)=YEAR(DATE()))) AND ((TCRAActiveMapsT.MapBlock) Like "*" & [Forms]![TCRAIDLookupF]![BlockSearch] & "*") AND ((TCRAActiveMapsT.IsActive)="Yes"));

I want to display all the maps from TCRAActiveMapsT that have not been inspected in the current year. The TCRAInspectionT.InspectedDate is where the date of the inspection has been done.

The SQL above was working but it is not now.

Please help, my project was going good until this happened and now i am stuck.

In addition to all of the other excellent suggestions and comments, I would like to point out that, at least in my experience, data is often the problem.

When some complex calculations and concatenations of data "work fine" for a while, but suddenly "stop working" with a new set of data, it is highly likely that there is some critical difference in that new set of data that no longer satisfies the original assumptions used to create those complex calculations and concatenations. Verify the data on which "it stopped working".

 

[KadeFoster]

So when i run the query i get the below results.

So when i delete

Not In (SELECT TCRAID FROM TCRAInspectionT WHERE YEAR(INSPECTEDDATE)=YEAR(DATE()))

from the criteria it works and shows all records.

Also when i remover the "Not" from the statement it displays all the records even the ones i have completed.

So is this saying that the issues is with this Criteria?

I want it to only show TCRAs from TCRAActiveMapsT where there is no inspections for it from the current year. TCRAInspectionT.InspectedDate

I am just trying to make it so i can select a map that hasn't been done, then once its done it disappears off the list.

 

[MajP]

I believe the whole query fails if there is a NULL value in the inspection date since Year(NULL) fails.

(SELECT TCRAID FROM TCRAInspectionT WHERE YEAR(NZ(INSPECTEDDATE,0))=YEAR(DATE())

Also that would explain why it use to work and does not now. 9 times out of 10 when a form or query used to work and then stops it is because there is bad or incomplete data.

 

[KadeFoster]

OK i feel like an idiot, but yes it did fail due to the fact that there was a Null value. It was not in the TCRAInspectionT.InspectedDate field but was in the TCRAID field in the TCRAInspectionT Table, as TCRAID is an FK in that table.

Thank you every one for giving me ideas and in the end helping me solve it. Another lesson learnt.

 

[GPGeorge]

No need to beat yourself up over a learning exercise. It might have been a painful lesson, but it's one we all have to master at some point in our careers.