INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query results not matching data entered on ORDERS form.

Query results not matching data entered on ORDERS form.

(OP)
I am working on a function that will cause a email messages to be sent when payment is received for an order. There can be more than one line item for each order, so I have to use queries rather than get values from checkboxes or other objects on my order form. The four fields I need to reference are boolean, and my code gets totals on each of them. So a True value is actually less than 0. The fields represent the following:

Payment Required to Start Work
Payment Received to Start Work
Payment Required to Ship
Payment Received to Ship


If the first two boolean values are True (less than 0) and have an equal value, and Payment Required to Ship does not equal Payment Received to Ship, a message should be sent that a down payment has been received. If all the values are equal and less than 0, a message should be sent that payment has bee received for the whole order.

The problem I am running into is that, even after I enter a checkmark in a checkbox, the new value does not get piacked up by my query. So I get a 0 when I should get a number less than 0, and the criteria do not get met.

I have tried DoCmd.RunCommand acCmdSaveRecord and Me.Dirty = False in the Click subroutines and the AfterUpdate subroutines for the Payment Received to Start Work checkbox and the Payment Received to Ship checkbox. So far, no luck.

Here is the query:

Set rs3 = db.OpenRecordset("SELECT ORDERS.OrderNumber, REPRESENTATIVES.Initials, " & _
"Sum(Not IsNull([PayStartReqDate])) As PayStartReq, Sum(Not IsNull([PayShipReqDate])) AS PayShipReq," & _
"Sum(Not IsNull([PayStartRecvdDate])) As PayStartRecvd, Sum(Not IsNull([PayShipRecvdDate])) AS PayShipRecvd " & _
"FROM REPRESENTATIVES INNER JOIN (ORDERS LEFT JOIN Payments ON ORDERS.OrderID = Payments.OrderID) " & _
"ON REPRESENTATIVES.RepID = ORDERS.RepID " & _
"WHERE ORDERS.OrderNumber = " & OrderNumber & _
" GROUP BY ORDERS.OrderNumber, REPRESENTATIVES.Initials", dbReadOnly)


Any thoughts on what else I should look at?

Thank you in advance.

RE: Query results not matching data entered on ORDERS form.

You have no criteria in your query to pull where not equal. I must be missing something. You say Payment Required to Start Work and
Payment Received to Start Work are booleans. If Payment Required to Ship and Payment Received to Ship are not booleans, why would they be <0?

RE: Query results not matching data entered on ORDERS form.

(OP)
A Boolean is essentially a Yes/No, zero or not zero, with a not zero actually being a minus one. Just to let you know, I tried simply counting values (as opposed to summing them), and ran into the same sort of problem: my query results do not match the entries I make on my form. If each sales order had only one line item I could just get values directly from my form, but there can be multiple line items for each sales order.

RE: Query results not matching data entered on ORDERS form.

Exactly. That does not answer my question. What data type are the Payment Required to Ship and Payment Received to Ship fields?

RE: Query results not matching data entered on ORDERS form.

(OP)
Those are boolean.

RE: Query results not matching data entered on ORDERS form.

I would put this in the After Update:
If [Payment Required to Ship] <> [Payment Received to Ship] Then
MsgBox("down payment has been received")
Else
If [Payment Required to Ship] = True And [Payment Received to Ship] = True Then
MsgBox("payment received for the whole order")
End If
End If

RE: Query results not matching data entered on ORDERS form.

(OP)
That still will not tell me why I can have a check box checked off on my form, but when I query the underlying tables I do not get information that matches

RE: Query results not matching data entered on ORDERS form.

I guess you need to find out exactly when your checkbox's state gets transfered as data change to your table.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Query results not matching data entered on ORDERS form.

Maybe I'm missing something obvious, but you seem to expect the underlying table (that stores a checkbox value) to immediately reflect a change on the form, and then basing logic on 'reflected' changes in tables.

Is that correct?

If so, then, it doesn't work that way; it's not like typing a value directly into a table - you've got to force the data update.
If you moved OFF that record (after ticking a checkbox), then back to that record, THEN the underlying table would reflect the data change.

Try: clicking a checkbox, moving to a previous record, then moving back to the edited record.
Does the 'query' NOW reflect that change?

Either way, I've got a strong feeling that your logic and / or table designs are not correct (if check boxes are being used to indicate 'numbers of records' and are the basis for event triggers etc).

Please explain in more detail what your requirement is - you may be about to produce 1,000 lines of code for something that can be done in 10.

Def




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close