Report to find only records that are checked in a checkbox

[mark6455]

I have posted this question before and still cannot find the proper format. I figured I would try one more time with what I hope is more clarity on what I need. I wrote this database years ago and have forgotten a lot since I have not done programming for so long.

I have a detailed database to track every item about my pickup truck. Fuel use, maintenance, issues, years owned and more. Specifically, I need the following:

I have a checkbox that if checked means the truck had a regeneration of the of the diesel emissions. The checkbox is labeled "Regeneration". I want to create a report that ONLY lists a data entry on the report if the checkbox is checked.

I want to take the starting mileage called "Mileage this trip" and subtract this from the ending mileage called "Ending Mileage" ONLY if the checkbox is checked. I need a printout of all the regenerations only that the truck has done.

In my mind, If Regeneration is true then Ending Mileage minus Mileage this trip will show in the printout but only for a Regeneration. To this date, I have had 179 regenerations in 7 years so that is the only information I need on this report.



Thank you.

[Welshgasman]

So use that as criteria for the query that supports the report.
WHERE Regeneration = True

I believe you were already shown how to take one value from another.? I will call that the math query.

In fact if you are that rusty, create two queries. The first just gets all regens with their mileage ordered by date.
Then use the math query to get the difference between each record. Use that query for your report.

[June7]

Which table is the yes/no field in? Post SQL of attempted query.

If you want to provide db for analysis, follow instructions at bottom of my post.

[mark6455]

Which table is the yes/no field in? Post SQL of attempted query.

If you want to provide db for analysis, follow instructions at bottom of my post.

The YES/NO table is called REGENERATION.

[June7]

Yes/No field is called REGENERATION and it is in which table?

What is the SQL statement?

If you still need help, fastest way to get resolution is to provide database.

[mark6455]

Sorry I forgot to put it in the reply; table is Expenses.

[June7]

Really, pulling teeth here.

What is the SQL statement?

Don't think I will reply any more unless db provided as requested.

[Micron]

Looks to me that this is about your other thread, which has replies but you seem to have abandoned. You won't win any points by creating a new thread for the same issue if that's what you've done.
https://www.accessforums.net/showthread.php?t=84333

[mosinjack]

This is definitely doable! In your report’s query, set the criteria for the "Regeneration" checkbox field to True (or -1 in Access). Then add a calculated field like =[Ending Mileage]-[Mileage this trip] to show the distance per regeneration. That should filter your report to only show regeneration entries with the mileage difference.

[mark6455]

cannot provide database. Legal reasons.

[Minty]

If it's your truck and your database, I doubt there are any legal ramifications.
Just anonymise the truck data.

[jojowhite]

you create a Query against the table that has the "regeneration" field:

SELECT *, [end mileage] - [mileage] AS MileageDiff FROM YourTableName WHERE [regeneration] = True ORDER BY [date field from your table];

save the query and create a Report based on the query.

[mark6455]

Lawsuit in progress with Nissan. Cannot send DB at lawyer's advice until suit is over.

[jojowhite]

you create a Copy of the db, so you only send the Copy not the original.
i think you need to get the approval of your lawyer for any modification to your db
while litigation is in progress.

[madpiet]

Then post the query.

And maybe for the tables involved, create a query for each with all the columns. We don't want your data, thanks.