|
| 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
May 18th, 2007, 05:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
query doesn't return new records
I have created some vba code that inserts new records into my db. However,
even though a record exists in all tables for the new id and all other criteria are met for being selected by the query, the new records are not selected. This is access 2003. We have seven tables all joined on id. The code adds the records to all the tables. I've created a new query and it gets the same results. The new records are not selected. TIA, -- MorningStarFan 
|
|
#2
May 18th, 2007, 06:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query doesn't return new records
When having problems with a query not returning expected records, simplify it
until you see them. I'm assuming that you double, no triple, checked the data and it's going into the correct tables in the correct backend database file if split...... First start removing criteria. Remove one at a time if there are many in an AND or OR in the WHERE clause then run the query. Next try changing some of the INNER JOINS to LEFT or RIGHT JOINS one table at a time then run the query. After that try removing a table at a time. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "MorningStarFan" wrote: I have created some vba code that inserts new records into my db. However, even though a record exists in all tables for the new id and all other criteria are met for being selected by the query, the new records are not selected. This is access 2003. We have seven tables all joined on id. The code adds the records to all the tables. I've created a new query and it gets the same results. The new records are not selected. TIA, -- MorningStarFan
|
|
#3
May 18th, 2007, 10:16 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query doesn't return new records
Thanks Jerry. We found that because our query was eliminating any records
with any of five fields that were not null, this new record was being excluded. Which means I am on to that next mystery- why does this new record have five fields that appear and should be null but they actually aren't? The record was created with VBA code... variables were initialized... and I used this statement sINTERNAL_REMOVE_REASON = IIf(IsNull(rst4(15)), "", rst4(15)) in case the value coming from rst4 IsNull... That part is still a mystery. -- MorningStarFan "Jerry Whittle" wrote: When having problems with a query not returning expected records, simplify it until you see them. I'm assuming that you double, no triple, checked the data and it's going into the correct tables in the correct backend database file if split...... First start removing criteria. Remove one at a time if there are many in an AND or OR in the WHERE clause then run the query. Next try changing some of the INNER JOINS to LEFT or RIGHT JOINS one table at a time then run the query. After that try removing a table at a time. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "MorningStarFan" wrote: I have created some vba code that inserts new records into my db. However, even though a record exists in all tables for the new id and all other criteria are met for being selected by the query, the new records are not selected. This is access 2003. We have seven tables all joined on id. The code adds the records to all the tables. I've created a new query and it gets the same results. The new records are not selected. TIA, -- MorningStarFan
|
|
#4
May 19th, 2007, 02:30 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query doesn't return new records
"" isn't null. It's an empty string which is a different thing. If you
want it to be null, replace "" with Null . No quotes needed. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "MorningStarFan" wrote: Thanks Jerry. We found that because our query was eliminating any records with any of five fields that were not null, this new record was being excluded. Which means I am on to that next mystery- why does this new record have five fields that appear and should be null but they actually aren't? The record was created with VBA code... variables were initialized... and I used this statement sINTERNAL_REMOVE_REASON = IIf(IsNull(rst4(15)), "", rst4(15)) in case the value coming from rst4 IsNull... That part is still a mystery. -- MorningStarFan "Jerry Whittle" wrote: When having problems with a query not returning expected records, simplify it until you see them. I'm assuming that you double, no triple, checked the data and it's going into the correct tables in the correct backend database file if split...... First start removing criteria. Remove one at a time if there are many in an AND or OR in the WHERE clause then run the query. Next try changing some of the INNER JOINS to LEFT or RIGHT JOINS one table at a time then run the query. After that try removing a table at a time. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "MorningStarFan" wrote: I have created some vba code that inserts new records into my db. However, even though a record exists in all tables for the new id and all other criteria are met for being selected by the query, the new records are not selected. This is access 2003. We have seven tables all joined on id. The code adds the records to all the tables. I've created a new query and it gets the same results. The new records are not selected. TIA, -- MorningStarFan
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
