help with query with checkbox/calculated field (1 Viewer)

Kelly@AllenEnt

Registered User.
Local time
Today, 15:56
Joined
Oct 4, 2012
Messages
51
Hello,

I have a database that has a check box that automatically checks when the outstanding amount is less than or equal to $0.05.

=IIf([OUTSTANDING]<=0.05 And [COMMISSION]>=0,-1,0)

When the criteria is true, my Form automatically checks the box marked "paid".

I have a report based on a query so I know which documents I need to pull based on a certain date. What I need it to do is filter out those that have a check in the paid column. PAID is not listed on the orders table but OUTSTANDING is. I tried adding the OUTSTANDING column to my query and putting the criteria to <=0.05 but nothing shows in the column when I run the query. I believe that is because it is also a calculated amount.

=Nz([COMMISSION],0)-Nz([txtTotalPayments],0)

Does anyone know how I can make the query work?
 

gblack

Registered User.
Local time
Today, 22:56
Joined
Sep 18, 2002
Messages
632
To recreate that PAID checkbox, Copy this code:

Code:
PAID: IIf((Nz([COMMISSION],0)-Nz([txtTotalPayments],0)) <=0.05 And [COMMISSION]>=0,"Yes","No")

Open the query your Report is based on, then paste the code into the query In the area where the field name usually goes.
Then, in the criteria of the new field you pasted put: "No"

(See the attached: QueryFieldExample)

This should weed out all the "PAID" documents.
 

Attachments

  • QueryFieldExample.PNG
    QueryFieldExample.PNG
    7.3 KB · Views: 118

Kelly@AllenEnt

Registered User.
Local time
Today, 15:56
Joined
Oct 4, 2012
Messages
51
I did as suggested and it wants me to enter parameter value for "txtTotalPayments". If I don't, everything comes back as NO.
 

gblack

Registered User.
Local time
Today, 22:56
Joined
Sep 18, 2002
Messages
632
When you added the OUTSTANDING column into your query... if delet your criteria... is it still blank when you run the query... or are you getting values, then?
 

gblack

Registered User.
Local time
Today, 22:56
Joined
Sep 18, 2002
Messages
632
Blank in the query or blank in the report...?
 

gblack

Registered User.
Local time
Today, 22:56
Joined
Sep 18, 2002
Messages
632
Hmm... I am not sure what you are using as a query that your report is bound to. Sounds like maybe your Orders table. You say that OUTSTANDING is a field in the orders table but (when you run your query) OUTSTANDING is "blank" even without any criteria. If you just open the Orders table are there any values in the OUTSTANDING column? If so, make sure there's no other filters in your query that might pull null values for that field

Other than that... I would say, find the underlying query that your form is based on... Then use the structure from that query (i.e. tables/queries) to base your report off of.

If the database is small and houses non-sensitive data, you might think about zipping it and post it and I could take a look... Without seeing what you are looking at, it's kind of tough to envision why your data isn't coming up.

Other than that maybe some other big-brained folks would know how to help, there are certainly a lot smart people on this site than me:)

Respectfully,
Gary
 

Kelly@AllenEnt

Registered User.
Local time
Today, 15:56
Joined
Oct 4, 2012
Messages
51
"OUTSTANDING" is a calculated field in the Form based on the "payment subtotal" which is also a calculated field.

I think the "OUTSTANDING" column in the "ORDERS" table is erroneous. I wish the calculation from the form would store in the table as a number. But that is wishful thinking.

What I really need to happen is for the "CODES WITH ORDER REPORT" to eliminate those items that are not "PAID" or where the "OUTSTANDING" is <=0.05. (both of those are the same because PAID checks when that outstanding criteria is met).

I have attached a PDF of the DB in design view because it does contain sensitive information and is too large to zip. Please let me know if you can help me using this information.:banghead:
 

Attachments

  • Access screenshot.zip
    835.2 KB · Views: 105

gblack

Registered User.
Local time
Today, 22:56
Joined
Sep 18, 2002
Messages
632
Sorry it's been so long to get back with you. I took a look at your screen shot, it's kind fo impossible to tell what's going on because you are dealing with two underlying queries one bound to a form, the other to a report...

If you can:
find which two queries these are and post the SQL code of both (telling which one is bound to the form and which is bound to the report) that might help...

Next... if you can go into the report design and get the exact formula from the "PAID" check box and post it... that would also be helpful.

GL,
Gary
 

Users who are viewing this thread

Top Bottom