My Access query doesn't pull all required records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query in Access that is supposed to pull up quotations between
certain dates. Each quotation in the table has a status of "Awarded",
"Upcoming", "Closed" or "Open". The problem I have now is that my query pulls
all status categories but "Open". I have looked at every associated table,
report and query, and cannot figure out why it's excluding this one
particular status. When I run the query with the status criteria as "Open",
nothing appears in the results.
Does anyone have an idea of why my query is excluding this status?
 
Post the SQL of the query. How do you run this query? Is the recordsource
for a form or report?

Do you have data that should match an Open status value? Try also running a
criterion of
Like "*" & "Open" & "*"
to see if there might be some leading blank spaces that aren't obvious to
the eye.
 
Here's the SQL of the query (sorry, it's long):

SELECT [BIDTAB: Bid Master File].[Log Nbr], [BIDTAB: Bid Master File].[Bid
Date], [BIDTAB: Bid Master File].Status, [BIDTAB: Bid Master File].Customer,
[BIDTAB: Bid Master File].[Final Result], [BIDTAB: Bid Master File].DSM,
[BIDTAB: Bid Master File].[Purch Type], [BIDTAB: Bid Master File].Winner,
[BIDTAB: Bid Master File].[Close Date], [BIDTAB: Bid Master File].Source,
[BIDTAB: Bid Master File].Comments, [BIDTAB: MFR File].Manufacturer,
[BIDTAB: Customer Master File].COM, [BIDTAB: Customer Master File].CIT,
[BIDTAB: Customer Master File].STA, [BIDTAB: Customer Master File].RGN,
[BIDTAB: Bid Master File].Status
FROM ([BIDTAB: Customer Master File] INNER JOIN [BIDTAB: Bid Master File] ON
[BIDTAB: Customer Master File].PID=[BIDTAB: Bid Master File].Customer) INNER
JOIN [BIDTAB: MFR File] ON [BIDTAB: Bid Master File].Winner=[BIDTAB: MFR
File].ID
ORDER BY [BIDTAB: Bid Master File].[Bid Date] DESC;
 
Also, I just tried the criterion you just suggested, and it did pull in
records with the "OPEN" status, but not all of them. It doesn't show recent
records... I wonder if it has something to do with the original version of
Access this file was saved in? I updated it to Access 2000 a few months ago.

Tracey
 
I don't see any WHERE clause in this query. You noted that you were
filtering it, if I've understood correctly? Or do you use this to return all
records and you're noticing that no records with the Open value are being
returned?

Also, with respect to running that criterion that I suggested, that suggests
that you don't have just Open in some records. It also suggests to me that
your query may need to use a Left or Right Join instead of an Inner Join --
it's possible that there is no matching record in one of the joined tables,
and as such some records are not being selected. Perhaps this:


SELECT [BIDTAB: Bid Master File].[Log Nbr], [BIDTAB: Bid Master File].[Bid
Date], [BIDTAB: Bid Master File].Status, [BIDTAB: Bid Master File].Customer,
[BIDTAB: Bid Master File].[Final Result], [BIDTAB: Bid Master File].DSM,
[BIDTAB: Bid Master File].[Purch Type], [BIDTAB: Bid Master File].Winner,
[BIDTAB: Bid Master File].[Close Date], [BIDTAB: Bid Master File].Source,
[BIDTAB: Bid Master File].Comments, [BIDTAB: MFR File].Manufacturer,
[BIDTAB: Customer Master File].COM, [BIDTAB: Customer Master File].CIT,
[BIDTAB: Customer Master File].STA, [BIDTAB: Customer Master File].RGN,
[BIDTAB: Bid Master File].Status
FROM ([BIDTAB: Customer Master File] RIGHT JOIN [BIDTAB: Bid Master File] ON
[BIDTAB: Customer Master File].PID=[BIDTAB: Bid Master File].Customer) LEFT
JOIN
[BIDTAB: MFR File] ON [BIDTAB: Bid Master File].Winner=[BIDTAB: MFR
File].ID
ORDER BY [BIDTAB: Bid Master File].[Bid Date] DESC;



--

Ken Snell
<MS ACCESS MVP>


Nolzgirl said:
Here's the SQL of the query (sorry, it's long):

SELECT [BIDTAB: Bid Master File].[Log Nbr], [BIDTAB: Bid Master File].[Bid
Date], [BIDTAB: Bid Master File].Status, [BIDTAB: Bid Master
File].Customer,
[BIDTAB: Bid Master File].[Final Result], [BIDTAB: Bid Master File].DSM,
[BIDTAB: Bid Master File].[Purch Type], [BIDTAB: Bid Master File].Winner,
[BIDTAB: Bid Master File].[Close Date], [BIDTAB: Bid Master File].Source,
[BIDTAB: Bid Master File].Comments, [BIDTAB: MFR File].Manufacturer,
[BIDTAB: Customer Master File].COM, [BIDTAB: Customer Master File].CIT,
[BIDTAB: Customer Master File].STA, [BIDTAB: Customer Master File].RGN,
[BIDTAB: Bid Master File].Status
FROM ([BIDTAB: Customer Master File] INNER JOIN [BIDTAB: Bid Master File]
ON
[BIDTAB: Customer Master File].PID=[BIDTAB: Bid Master File].Customer)
INNER
JOIN [BIDTAB: MFR File] ON [BIDTAB: Bid Master File].Winner=[BIDTAB: MFR
File].ID
ORDER BY [BIDTAB: Bid Master File].[Bid Date] DESC;

Ken Snell said:
Post the SQL of the query. How do you run this query? Is the recordsource
for a form or report?

Do you have data that should match an Open status value? Try also running
a
criterion of
Like "*" & "Open" & "*"
to see if there might be some leading blank spaces that aren't obvious to
the eye.
Click to expand...
Click to expand...
 
Back
Top