See the attached I now believe the Invoicefrm is giving you the correct result.
I removed a number of Lookup Fields in your tables as these are a NO NO in Access.
See the attached I now believe the Invoicefrm is giving you the correct result.
I removed a number of Lookup Fields in your tables as these are a NO NO in Access.
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Try reading post 13 and pretend you do not know the system?
How does that read to you now?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Hi
The problem stems from bad table design. Use of Lookup fields in tables and linking Main Form to Subform using the wrong Controls.
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
I've not read the whole thread but, based on your last comments, you need three filter criteria
SES Is Not Null AND PurchaseOrder = 4500100001 AND POLine =10
Colin,
I believe the OP needs to identify all PO and PO lines where SES is null
Then sum all records that match the PO and PO lines BUT where the SES is not Null ?
I would only be able to do that in two queries?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Sorry Welshgasman, I did not see that you had provided an edit. Yes, it should add up the total for the same PO and POLIne where the SES is blank.
Have you not checked my solution?
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
Further to comments on design in posts 4,7 and 18, I just noticed that in invoice table, PurchaseOrder is a number and it seems to me that you'd want to join that to POLineTbl.POLine - but that one is text. I tried and raised a type mismatch error. I'm thinking WTH? Wasn't obvious because the number field has been left justified so I thought both were text at first. There are design errors as previously noted. The PK ID field of PO should be the FK field in PO line, and invoices should have FK of either PO or PO line - depending on whether payments are made against PO's or PO lines. This might not ever be solvable unless (I think) the minimum of converting POLineTbl.POLine or vice versa on the other field. Probably will give that a try just for 'fun'.
EDIT - will never work as one of them is a table level lookup (probably already mentioned). I'm afraid I'm out of steam on this one.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I'm creating a new database from scratch. I'm doing my best to try and get it set up the correct way. I'll try to get that posted in a few days, but I'm getting really confused trying to remember or to learn all the rules or standards for setting up a proper database. I'm going to throw that DSUM calculation in on the invoice form as well, but that will take me a few days to get there most likely. From what I was able to read, that sound like it should work. I was just having a real tough time following how to create that expression with the exception. I kept messing up everything I was trying to do, but when I redo the DB it will hopefully all fall in place. Like you were all saying, it is probably just an issue with me trying to take a short cut, that winds up taking 10 times the amount of time, LOL. My brain looses it thought pattern to easily after I suffered a stroke a couple years ago. I thought this would be a good way for me to start doing something, but my attempt at trying to save time and make shortcuts, has actually increased my stress. So, I'm just taking it real slow going forward.
I'm closing out this thread and showing this as solved. If I was to ask you all to look at a fresh db structure, I would want that to be on a fresh post. I know this post was getting really confusing. I will do my best to incorporate all your notes in my new db.
Thanks for your help.
You DSum() would be along the lines of ...........
This is assuming that you change PUrchaseOrder and POLine to be numeric, else you will need to surround the form values with single quotes.Code:Dim strCriteria AS String strCriteria = "PurchaseOrder = " & Me.PurchaseOrder & " AND POLine = " & Me.POLine & " AND SES IS NOT Null") ' not too sure about the Null part :) Debug.Print strCriteria ' so as to see EXACTLY what is produced =DSum("ProcessedBalance","ProcessedQry",strCriteria)
So whatever line you are on, it should show the sum. Where you put that, is up to you.
https://support.microsoft.com/en-us/...f-386056e61a32
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
See my prior post (edited last night) - one field is a lookup field. As you know, it shows that the value is 10 but it is not. Delta729 I'll post all the links I usually do for those who might need it. These will save you a lot of grief. If any of them don't really speak to you, research the subject and find ones that do. In your new thread I'd suggest that you start with a link to this one and post a pic of your relationships before going much farther. I wouldn't bother with forms or queries before doing that. Make sure you understand normalization, especially with respect to your situation. If any questions on that, might be a good time to start the new thread?This is assuming that you change PUrchaseOrder and POLine to be numeric,
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV
- http://rogersaccessblog.blogspot.com...ng-part-i.html
How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html
Naming conventions -
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837
What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.