Solved

Access 2003 Query Issue

Posted on 2015-02-05
11
97 Views
Last Modified: 2015-02-06
I have a query in Access 2003 that is quite extensive.  When I run the query one of the criteria is not being met and it returns blank fields.  Is there a way that Access can tell me what criteria did not get met?  This way I can go straight to the problem and correct it.  Please advise and thanks!
0
Comment
Question by:submarinerssbn731
  • 5
  • 2
  • 2
  • +2
11 Comments
 

Author Comment

by:submarinerssbn731
Comment Utility
Better yet can Access show the query return if the field is null or contains the criteria requested?
0
 
LVL 62

Expert Comment

by:☠ MASQ ☠
Comment Utility
You could use "Is Null" or "Is Not Null" as the criteria for the query field.
Are your queries in MSSQL or DesignView?
Normally I'd just backtrack through the expression used to populate the field to find likely contenders that are now missing - depends just how "extensive" your extensive query is.
0
 
LVL 84
Comment Utility
Can you post the SQL of the query?
0
 

Author Comment

by:submarinerssbn731
Comment Utility
SELECT [PSI Order Details].[Order Seq No], [PSI Order Details].[PO Number] AS PONumber, [PSI Order Details].PlantCode, [PSI Order Details].DLOC, BOM.CUSTNO, BOM.EC_LEVEL, [PSI Order Details].DockCode, CStr([Label Registry].Quantity) AS Qnty, [Label Registry].JobNo AS LotNo, [Label Registry].[Label ID] AS SN, [Label Registry].PSINO, [Label Registry].JobNo, [Label Registry].[Label ID] AS LabelNo, [Label Registry].[Label number] AS BoxNo, Format([DateProd],"mmmyyyy") AS FIFO, BOM.MATERIALID2 AS MatID, "113222074" AS MfgDUNS, BOM.PARTNAME, Tbl_Ship_to.DUNSCode, Format([DateProd],"yy/mm/dd") AS MfgDate, BOM.EC_DATE, BOM.CUSTCODE, Date() AS Today, BOM.PARTWEIG, [Label Registry].CartonLbs, Tbl_Ship_to.LocationCode
FROM (Tbl_Ship_to INNER JOIN (BOM INNER JOIN [PSI Order Details] ON BOM.PSINO = [PSI Order Details].PSINO) ON Tbl_Ship_to.SHIPTO = [PSI Order Details].[Ship To]) INNER JOIN [Label Registry] ON BOM.PSINO = [Label Registry].PSINO
WHERE ((([PSI Order Details].[Order Seq No])=[Forms]![frm_NEX_Labels]![lstDocIDs]) AND (([Label Registry].[Label ID])=[Forms]![frm_NEX_Labels]![LabelID]))
ORDER BY [Label Registry].JobNo, [Label Registry].[Label ID];
0
 

Author Comment

by:submarinerssbn731
Comment Utility
Design view.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 62

Expert Comment

by:☠ MASQ ☠
Comment Utility
Are you getting one blank field (if so which one) or does the query return no results?
0
 

Author Comment

by:submarinerssbn731
Comment Utility
returns no results.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
When you enter values on the form, are you sure there is a record that matches the values?

If you take the where clause off do you get data?

mlmcc
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
A couple of things to look at:
1.  You are joining 4 tables with inner joins, so you will only get records where there are related records in each of those tables.  If you don't get any records when you run your query without the WHERE clause, this could be the cause, in which case you should consider changing the joins to LEFT JOINs (without the WHERE clause) to help you determine which table is missing the matching records.

2. Have you checked to make sure that the fields you are using in your WHERE clause are of the same data type as the controls on the form?  You might want to consider actually declaring those form controls as parameters in your query, so that the data will be typed properly.
0
 

Author Closing Comment

by:submarinerssbn731
Comment Utility
Great answer!  Thanks!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
glad to help.

Can you share with us which of my recommendations solved the problem.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now