Share via

Query on a calculated yes/no field

Anonymous
2013-10-08T22:00:00+00:00

I’m trying to set up a query where one of the fields is a yes/no field.  The expression I used for the yes/no field in the form is: =IIf([AmtRec]+[AmtRec2]>=[TotalFees],True,False). 

The [TotalFees] is also a calculated field: =[ServiceFee]+[ReturnFee]+[Mileage]. 

The mileage field is calculated, too: =Int(-100*([NoMiles]*0.595))/-100. 

Everything works great in the form.  But when I try to set up the query and insert the Paid field (the yes/no field) and type in False in Criteria, it brings up all the records.  If I use True in the Critria, it brings up no records at all.  There are about 15 records that are not paid (no check marks).]

Any help would be greatly appreciated!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

16 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-10-09T14:32:06+00:00

    I had to close out my database and reopen and this comes up.  I had run it before I closed out though and it showed 0s & -1s in the paid column.  I only have one table and one form.

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-10-09T12:55:28+00:00

    I made the changes to what you have but it still brings up all records, paid and not paid.  I do not have the calculations in the table, just the form.  Thanks for all your time on this!

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2013-10-09T12:28:06+00:00

    Have you looked at the table? If you are using this expression {=IIf([AmtRec]+[AmtRec2]>=[TotalFees],True,False)} as the Controlsource of a control on your form, then the result is not being stored. Nor should it be. If Paid is a calculation then you should be using the same calculations in your query. So your query should actually look something like this:

    SELECT PapersTbl.Attorney, PapersTbl.Address1, PapersTbl.Address2, PapersTbl.City, PapersTbl.State, PapersTbl.Zip, PapersTbl.Plaintiff, [ServiceFee]+[ReturnFee]+(Int(-100*([NoMiles]*0.595))/-100) AS TotalFees, IIf([AmtRec]+[AmtRec2]>=[TotalFees],True,False) AS Paid

    FROM PapersTbl

    WHERE (((PapersTbl.Paid)=False) AND ((PapersTbl.Returned) BETWEEN #7/1/2013# And #7/25/2013#))

    ORDER BY PapersTbl.Attorney;

    Note, I prefer the BETWEEN operator than the >=,<= that you used, but using them is not wrong.

    0 comments
  4. Anonymous
    2013-10-09T12:10:44+00:00

    SELECT PapersTbl.Attorney, PapersTbl.Address1, PapersTbl.Address2, PapersTbl.City, PapersTbl.State, PapersTbl.Zip, PapersTbl.Plaintiff, PapersTbl.Paid

    FROM PapersTbl

    WHERE (((PapersTbl.Paid)=False) AND ((PapersTbl.Returned)>=#7/1/2013# And (PapersTbl.Returned)<=#7/25/2013#))

    ORDER BY PapersTbl.Attorney;

    0 comments
  5. ScottGem 68,810 Reputation points Volunteer Moderator
    2013-10-09T11:57:09+00:00

    Can you post the SQL for your query?

    0 comments