If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
New records not showing
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. |
#2
|
|||
|
|||
New records not showing
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "CorporateQAinTX" wrote: 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. |
#3
|
|||
|
|||
New records not showing
Please post back with the SQL statement that isn't returning the records you
expect. Regards Jeff Boyce Microsoft Office/Access MVP "CorporateQAinTX" wrote in message ... 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. |
#4
|
|||
|
|||
New records not showing
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" wrote: Please post back with the SQL statement that isn't returning the records you expect. Regards Jeff Boyce Microsoft Office/Access MVP "CorporateQAinTX" wrote in message ... 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. |
#5
|
|||
|
|||
New records not showing
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 "CorporateQAinTX" wrote in message ... 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" wrote: Please post back with the SQL statement that isn't returning the records you expect. Regards Jeff Boyce Microsoft Office/Access MVP "CorporateQAinTX" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|