[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Access 2003 Query Issue

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
submarinerssbn731
Asked:
submarinerssbn731
  • 5
  • 2
  • 2
  • +2
1 Solution
 
submarinerssbn731Author Commented:
Better yet can Access show the query return if the field is null or contains the criteria requested?
0
 
☠ MASQ ☠Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post the SQL of the query?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
submarinerssbn731Author Commented:
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
 
submarinerssbn731Author Commented:
Design view.
0
 
☠ MASQ ☠Commented:
Are you getting one blank field (if so which one) or does the query return no results?
0
 
submarinerssbn731Author Commented:
returns no results.
0
 
mlmccCommented:
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
 
Dale FyeCommented:
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
 
submarinerssbn731Author Commented:
Great answer!  Thanks!
0
 
Dale FyeCommented:
glad to help.

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now