Help with Query Filter, Please! (1 Viewer)

Pienuts · Jul 27, 2015

Okay, here's the dummy DB. Qry2 currently throws the error. Any ideas why?

plog · Jul 27, 2015

Actually query 1 is the problem. Open it to the data, then try and sort it by the InspectionYear field--it gives the same error.

Somewhere along the line of those daisy chained fields a function isn't returning valid data. Since just about every field is calculated off the one before it, I don't know exactly which one is the problem field, but its one of them.

Pienuts · Jul 27, 2015

Ahh.. Okay, I'll go through them again! Thanks for looking!

Pienuts · Jul 28, 2015

Hmm... I re-did the query in another way, and I'm having the exact same issue - are my expressions too complex for Access to be able to do this?

I think I'll post this separate issue into the forum to see if anyone can assist.

plog · Jul 28, 2015

Its never a good idea to use the calculated field in another calculation in the same query. So that means either making multiple queries each building on the prior, or using your logic to come up with one grand calculation.

Kind of like algebra. Suppose [Field1] is part of the table your query is built on and you had these calculated fields:

F1: Nz([Field1], 0)
F2: [F1] + 3
F3: [F2] *2
F4: [F3] - 1

You could just roll up all that logic and put it all in [F4] without going through those intermediate steps:

F4: ((Nz([Field1], 0) + 3) * 2) - 1

Of course with all the logic you would have to roll into one calculated field, my recommendation would be to write a custom function and put all that logic there and have your function calculate whatever that last field would be.

Pienuts · Jul 28, 2015

Well, I managed to get it to work! It turns out I had Null values in some of the Construction Dates so it was tripping Access up, even though I had filtered them out on the first query. Apparently that is a thing!
So to remedy that, I created *yet another* query to only grab the sites with construction dates, and based the original query on that. Everything is acting as desired.

So... Here are the three queries that bring up all the sheduled inspections to be completed in 2016:
1

Code:

SELECT tblSite.SITEID, Max(tblUSID.ConstructionDate) AS MaxOfConstructionDate
FROM tblSite INNER JOIN tblUSID ON tblSite.SiteKey = tblUSID.SITEID
GROUP BY tblSite.SITEID
HAVING (((tblSite.SITEID) Is Not Null) AND ((Max(tblUSID.ConstructionDate)) Is Not Null))
ORDER BY tblSite.SITEID;

2

Code:

SELECT qryNextScheduled1.SITEID, qryNextScheduled1.MaxOfConstructionDate, IIf(Year(Date())-Year([MaxOfConstructionDate])<=2,5,IIf(Year(Date())-Year([MaxOfConstructionDate])<=5,6,7)) AS NextInspectionType, IIf([NextInspectionType]=5,DateSerial(Year([MaxOfConstructionDate])+2,6,1),IIf([NextInspectionType]=6,DateSerial(Year([MaxOfConstructionDate])+5,6,1),IIf(Right(Year(Date()),1)=Right(Year([MaxOfConstructionDate]),1),DateSerial(Year(Date()),6,1),IIf(10-Right(Year(Date()),1)<10-Right(Year([MaxOfConstructionDate]),1),DateSerial(Left(Year(Date()),3)+1 & Right(Year([MaxOfConstructionDate]),1),6,1),DateSerial(Left(Year(Date()),3) & Right(Year([MaxOfConstructionDate]),1),6,1))))) AS InspectionDate, Year([InspectionDate]) AS InspectionYear
FROM qryNextScheduled1;

3

Code:

SELECT qryNextScheduled2.SITEID, qryNextScheduled2.MaxOfConstructionDate, qryNextScheduled2.NextInspectionType, qryNextScheduled2.InspectionDate, qryNextScheduled2.InspectionYear
FROM qryNextScheduled2
WHERE (((qryNextScheduled2.InspectionYear)=2016))
ORDER BY qryNextScheduled2.SITEID;

Now all I have to figure out is how to do the random percentages of each type.
I did see your recommendation on keeping the calculated cells to only one per query, and managed to figure out the last construction date on the new query I created, but I think every other calculated field has a purpose for the future random percentage figuring (InspectionType, InspectionDate).

Pienuts · Jul 29, 2015

Okay, I've got it!
I created a simple form that has 4 major controls - InspectionYear, 2YearPercentage, 5YearPercentage, and DecennialPercentage. Then on save I execute 3 SQL Update queries (one for each inspection type) that use TOP N PERCENT based on the controls and sorted by a rnd field. Works like a charm.
Thanks for all your assistance, plog! You helped me immensely!
Calling it solved!

Help with Query Filter, Please! (1 Viewer)

Pienuts

Registered User.

Attachments

plog

Banishment Pending

Pienuts

Registered User.

Pienuts

Registered User.

plog

Banishment Pending

Pienuts

Registered User.

Pienuts

Registered User.

Similar threads

Users who are viewing this thread