New records not showing

[CorporateQAinTX]

Ok, thanks to Allen Browne and his great website, I've gotten a good bit done
on this db I'm working on. I've got 2 main data entry forms and 1 data
retrieval form. I've created 8 queries using SQL and a little adjustment in
the QBE. The reporting form has a standard search for all records in a
specific location and between a start and end date. All 8 searches
incorporate this because there are 7 different locations that will be using
the db. The user can use a specific query and enter the parameter criteria
based on which command button clicked on the report form.

Now that I've explained all of that, here's my problem. I was initially
working with 3 records that I entered using the data entry form. As I
completed each query I would verify that it would return correct values. Each
one did. Now I have added 2 more records and they are not showing up in the
query results. I've double checked the query code and each of the form codes.
I can't see anything that could be affecting it in this way. I've got all of
the queries saved in Word so I can copy and paste them if that seems
necessary, but I'll wait until someone asks first. I know I'll have plenty of
other questions later, so thanks in advance.

 

[Jerry Whittle]

First open up the tables and make sure that the records are there.

Do any of the querys have the words "INNER JOIN" in them? If so test
changing them to "LEFT JOIN" or "RIGHT JOIN".

Also look for criteria in the WHERE clause that might stop records from
showing.

If viewing from a query or form, make sure that there are no filters applied.

 

[Jeff Boyce]

Please post back with the SQL statement that isn't returning the records you
expect.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[CorporateQAinTX]

This is the generic requirement for each query. It's the same in all of them.
If I can get this one to work then I should be able to get the rest of them
to work. I'm wondering if Jerry is right about the JOIN's. I didn't think too
much about that. I have to be out of here in a little less than an hour, but
I'll check back later. Thanks for your help.
Code Follows:

PARAMETERS [Forms]![f_HoldReport]![StartDate] DateTime,
[Forms]![f_HoldReport]![EndDate] DateTime, [Forms]![f_HoldReport]![LID] Short;
SELECT t_HoldTicket.TicketNum, t_Location.Location, t_HoldTicket.DateHeld,
t_Defect.DftName, t_HoldTicket.TotHeld, t_Shift.Shift, t_Machine.MachType,
t_Machine.MachNum, t_Product.ProdDescription, t_Product_1.ProdDescription,
t_HoldTicket.Dsp1, t_HoldTicket.TotDsp1, t_HoldTicket.Dsp2,
t_HoldTicket.TotDsp2, t_HoldTicket.Dsp3, t_HoldTicket.TotDsp3,
t_HoldTicket.Status
FROM t_Shift INNER JOIN (t_Product AS t_Product_1 INNER JOIN (t_Machine AS
t_Machine_1 INNER JOIN (t_Location INNER JOIN (t_Defect INNER JOIN (t_Product
INNER JOIN (t_Machine INNER JOIN t_HoldTicket ON t_Machine.MID =
t_HoldTicket.BMID) ON t_Product.PID = t_HoldTicket.BPID) ON t_Defect.DftID =
t_HoldTicket.DftID) ON t_Location.LID = t_HoldTicket.LID) ON t_Machine_1.MID
= t_HoldTicket.IMID) ON t_Product_1.PID = t_HoldTicket.PPID) ON t_Shift.SftID
= t_HoldTicket.SftID
WHERE (((t_HoldTicket.DateHeld) Between [Forms]![f_HoldReport]![StartDate]
And [Forms]![f_HoldReport]![EndDate])
AND (([Forms]![f_HoldReport]![Location] Is Null)
OR (t_HoldTicket.LID) = [Forms]![f_HoldReport]![Location]));

 

[Jeff Boyce]

Give Jerry's suggestion a try. It might be that you have records in one
table that aren't in the other. When you join as you do below, Access only
shows records that show up in BOTH tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is the generic requirement for each query. It's the same in all of
them.
If I can get this one to work then I should be able to get the rest of
them
to work. I'm wondering if Jerry is right about the JOIN's. I didn't think
too
much about that. I have to be out of here in a little less than an hour,
but
I'll check back later. Thanks for your help.
Code Follows:

PARAMETERS [Forms]![f_HoldReport]![StartDate] DateTime,
[Forms]![f_HoldReport]![EndDate] DateTime, [Forms]![f_HoldReport]![LID]
Short;
SELECT t_HoldTicket.TicketNum, t_Location.Location, t_HoldTicket.DateHeld,
t_Defect.DftName, t_HoldTicket.TotHeld, t_Shift.Shift, t_Machine.MachType,
t_Machine.MachNum, t_Product.ProdDescription, t_Product_1.ProdDescription,
t_HoldTicket.Dsp1, t_HoldTicket.TotDsp1, t_HoldTicket.Dsp2,
t_HoldTicket.TotDsp2, t_HoldTicket.Dsp3, t_HoldTicket.TotDsp3,
t_HoldTicket.Status
FROM t_Shift INNER JOIN (t_Product AS t_Product_1 INNER JOIN (t_Machine AS
t_Machine_1 INNER JOIN (t_Location INNER JOIN (t_Defect INNER JOIN
(t_Product
INNER JOIN (t_Machine INNER JOIN t_HoldTicket ON t_Machine.MID =
t_HoldTicket.BMID) ON t_Product.PID = t_HoldTicket.BPID) ON t_Defect.DftID
=
t_HoldTicket.DftID) ON t_Location.LID = t_HoldTicket.LID) ON
t_Machine_1.MID
= t_HoldTicket.IMID) ON t_Product_1.PID = t_HoldTicket.PPID) ON
t_Shift.SftID
= t_HoldTicket.SftID
WHERE (((t_HoldTicket.DateHeld) Between [Forms]![f_HoldReport]![StartDate]
And [Forms]![f_HoldReport]![EndDate])
AND (([Forms]![f_HoldReport]![Location] Is Null)
OR (t_HoldTicket.LID) = [Forms]![f_HoldReport]![Location]));

Please post back with the SQL statement that isn't returning the records
you
expect.

Regards

Jeff Boyce
Microsoft Office/Access MVP