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
|
|||
|
|||
Query only showing specific records - i need all - please help
I have a combo box in a form (Based on a query) that looks up values in the
Finish Lookup table. The table has the following info: Finish Description Finish Rate Finish Sq Ft Coil .45 Cutting 0 Foam 3.50 X Folded 0 Lamintated .218 X The next text box has length Width To get the finish cost I have the following formula: =IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies Requested],[Finish Rate]*[Copies Requested]) After I add new records, I only see the records where I choice a finish description that has a finish rate associated with it, I do not see all records with the zeros for the finish rate. What confuses me is that I see all the tables being populated with all the info that I enter (0 rate descriptions and actual rate descriptions) I have to present all this info today and I have no clue why the query is only showing those records with the rates associated with them. I don't have any criterias set up. Thanks so much, Yula |
#2
|
|||
|
|||
Query only showing specific records - i need all - please help
If there is no finish rate defined, what would be the result?
You can use: =Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq Ft], 1), "undefined" ) which will display "undefined" (without quote) if any of the three fields is not defined. You could also have use a Format to format the NULL values as displaying "undefined" instead of the blank you should see at the moment when a null is involved in the computation. Vanderghast, Access MVP "Yula" wrote in message news I have a combo box in a form (Based on a query) that looks up values in the Finish Lookup table. The table has the following info: Finish Description Finish Rate Finish Sq Ft Coil .45 Cutting 0 Foam 3.50 X Folded 0 Lamintated .218 X The next text box has length Width To get the finish cost I have the following formula: =IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies Requested],[Finish Rate]*[Copies Requested]) After I add new records, I only see the records where I choice a finish description that has a finish rate associated with it, I do not see all records with the zeros for the finish rate. What confuses me is that I see all the tables being populated with all the info that I enter (0 rate descriptions and actual rate descriptions) I have to present all this info today and I have no clue why the query is only showing those records with the rates associated with them. I don't have any criterias set up. Thanks so much, Yula |
#3
|
|||
|
|||
Query only showing specific records - i need all - please help
Michael, Thank you, but I am still lost. I substituted your first formula
into my nish field and I am getting a calculation. The problem is that I want to see all the data that I entered into the database. I am missing 7 records because they did not require me to enter anything into the Length/Width fields. I see all that data in the tables, bit not in the form/query. Is it possible to fix? or should I erase them from my tables and reenter those records? "Michel Walsh" wrote: If there is no finish rate defined, what would be the result? You can use: =Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq Ft], 1), "undefined" ) which will display "undefined" (without quote) if any of the three fields is not defined. You could also have use a Format to format the NULL values as displaying "undefined" instead of the blank you should see at the moment when a null is involved in the computation. Vanderghast, Access MVP "Yula" wrote in message news I have a combo box in a form (Based on a query) that looks up values in the Finish Lookup table. The table has the following info: Finish Description Finish Rate Finish Sq Ft Coil .45 Cutting 0 Foam 3.50 X Folded 0 Lamintated .218 X The next text box has length Width To get the finish cost I have the following formula: =IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies Requested],[Finish Rate]*[Copies Requested]) After I add new records, I only see the records where I choice a finish description that has a finish rate associated with it, I do not see all records with the zeros for the finish rate. What confuses me is that I see all the tables being populated with all the info that I enter (0 rate descriptions and actual rate descriptions) I have to present all this info today and I have no clue why the query is only showing those records with the rates associated with them. I don't have any criterias set up. Thanks so much, Yula |
#4
|
|||
|
|||
Query only showing specific records - i need all - please help
If the data is in the table but not in the form, you may have a filter on
the form. If the form is based on a query, then, when you look at the query, in data view, do you see the records? if not, then, probably, you have a criteria in the query that filter out those records. Remove the criteria if that is the case. Sure, the easier way is to edit the data in the table, add the required data in the field (so you will be able to see the records in the form), but that won't avoid the problem to re-occur. You can always design the table to NOT ALLOW NULL under your field , if that makes sense. But knot having all the details, I strongly suspect you used a filter or a criteria which eliminates the records. A little bit if you have a criteria under a quantity field that said: 10 then only records having their quantity value 10 would be picked up. So start with the query. Is your records are available there? if not, remove the criteria which makes no sense. If the data is there, then open the form, and remove the form filter, if there is one. Vanderghast, Access MVP. "Yula" wrote in message ... Michael, Thank you, but I am still lost. I substituted your first formula into my nish field and I am getting a calculation. The problem is that I want to see all the data that I entered into the database. I am missing 7 records because they did not require me to enter anything into the Length/Width fields. I see all that data in the tables, bit not in the form/query. Is it possible to fix? or should I erase them from my tables and reenter those records? "Michel Walsh" wrote: If there is no finish rate defined, what would be the result? You can use: =Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq Ft], 1), "undefined" ) which will display "undefined" (without quote) if any of the three fields is not defined. You could also have use a Format to format the NULL values as displaying "undefined" instead of the blank you should see at the moment when a null is involved in the computation. Vanderghast, Access MVP "Yula" wrote in message news I have a combo box in a form (Based on a query) that looks up values in the Finish Lookup table. The table has the following info: Finish Description Finish Rate Finish Sq Ft Coil .45 Cutting 0 Foam 3.50 X Folded 0 Lamintated .218 X The next text box has length Width To get the finish cost I have the following formula: =IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies Requested],[Finish Rate]*[Copies Requested]) After I add new records, I only see the records where I choice a finish description that has a finish rate associated with it, I do not see all records with the zeros for the finish rate. What confuses me is that I see all the tables being populated with all the info that I enter (0 rate descriptions and actual rate descriptions) I have to present all this info today and I have no clue why the query is only showing those records with the rates associated with them. I don't have any criterias set up. Thanks so much, Yula |
#5
|
|||
|
|||
Query only showing specific records - i need all - please help
Michel - I am going to loose my mind with this stuff. The form is based on a
Query. I already erased the previous records and entered a new record that did not have length and width filled out. All the tables are now populated with the info I entered, except for the Size table (the one that has the length/width field). I checked the query for any criteria and unless there is some hidden rows, I do not see any criteria. All I see are the records that have length and width filled in. Is there other criteria that is not visable in a query design view? Not all records require the user to enter length and width, why is it not showing the records with nothing in the length/Width fields???? My last thought, before I complety loose it, is that maybe I was trying to put some code that was suggested to me into an event and I didn't erase everthing. I wanted the user to be required to enter data in the length/width field if there is an X in the Sq ft coloumn of the paper lookup table and the finish lookup table Paper Description Rate Sq ft Gloss .50 X Finish Description Rate Sq ft Laminate .25 X Thank you so much for your help!!1 I just have no clue what to do next, this is driving me Nuts!! "Michel Walsh" wrote: If the data is in the table but not in the form, you may have a filter on the form. If the form is based on a query, then, when you look at the query, in data view, do you see the records? if not, then, probably, you have a criteria in the query that filter out those records. Remove the criteria if that is the case. Sure, the easier way is to edit the data in the table, add the required data in the field (so you will be able to see the records in the form), but that won't avoid the problem to re-occur. You can always design the table to NOT ALLOW NULL under your field , if that makes sense. But knot having all the details, I strongly suspect you used a filter or a criteria which eliminates the records. A little bit if you have a criteria under a quantity field that said: 10 then only records having their quantity value 10 would be picked up. So start with the query. Is your records are available there? if not, remove the criteria which makes no sense. If the data is there, then open the form, and remove the form filter, if there is one. Vanderghast, Access MVP. "Yula" wrote in message ... Michael, Thank you, but I am still lost. I substituted your first formula into my nish field and I am getting a calculation. The problem is that I want to see all the data that I entered into the database. I am missing 7 records because they did not require me to enter anything into the Length/Width fields. I see all that data in the tables, bit not in the form/query. Is it possible to fix? or should I erase them from my tables and reenter those records? "Michel Walsh" wrote: If there is no finish rate defined, what would be the result? You can use: =Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq Ft], 1), "undefined" ) which will display "undefined" (without quote) if any of the three fields is not defined. You could also have use a Format to format the NULL values as displaying "undefined" instead of the blank you should see at the moment when a null is involved in the computation. Vanderghast, Access MVP "Yula" wrote in message news I have a combo box in a form (Based on a query) that looks up values in the Finish Lookup table. The table has the following info: Finish Description Finish Rate Finish Sq Ft Coil .45 Cutting 0 Foam 3.50 X Folded 0 Lamintated .218 X The next text box has length Width To get the finish cost I have the following formula: =IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies Requested],[Finish Rate]*[Copies Requested]) After I add new records, I only see the records where I choice a finish description that has a finish rate associated with it, I do not see all records with the zeros for the finish rate. What confuses me is that I see all the tables being populated with all the info that I enter (0 rate descriptions and actual rate descriptions) I have to present all this info today and I have no clue why the query is only showing those records with the rates associated with them. I don't have any criterias set up. Thanks so much, Yula |
#6
|
|||
|
|||
Query only showing specific records - i need all - please help
When you say you don't see the record, you mean you don't see the row at
all, OR you see the row but under the computed expression column, you see no result. If you see no row at all, then this seems to be a criteria in the query, OR a (dynamic) filter in the form. Sure, if you don't see the row at all in the query view (as data view), then it is a criteria in the query. A criteria in the query can be as a criteria, OR as an INNER JOIN. An inner join acts like a criteria too. Can you edit the SQL statement (in SQL view of the query, select all the text, then Ctrl_V will push it in the clipboard, and Ctrl-V here will paste it back, here, as example). If you see the row BUT NOT a result under a compute column, that is because NULL propagates through most arithmetic operations. Second 'question'. To impose the end user to fill the field length/Width when there is an X under the Sq ft column, you have to add a Table Validation Rule in the Table Design. ( [Sq ft] = "X") IMP ( NOT ( [length/Width] IS NULL )) can be the formulation of the rule, add some nice description for that rule (such as: "must supplied length/Width when [Sq ft] has an X" ) Note: I assume [Sq Ft] field is a TEXT field, NOT A BOOLEAN field formatted to show a check, or an X. If [Sq ft] is Boolean value type, the table validation rule becomes: [Sq ft] IMP ( NOT ( [length/Width] IS NULL )) and a more appropriate description: "must supplied length/Width when [Sq ft] is checked" If you wonder about what is the operator IMP, it is the logical operator IMPlies, a kind of electrical switch: if the left side of IMP is false, every thing is as if no rule was there; if the left side of IMP is true, then the right side MUST be true for the record be considered acceptable to the database. Vanderghast, Access MVP "Yula" wrote in message ... Michel - I am going to loose my mind with this stuff. The form is based on a Query. I already erased the previous records and entered a new record that did not have length and width filled out. All the tables are now populated with the info I entered, except for the Size table (the one that has the length/width field). I checked the query for any criteria and unless there is some hidden rows, I do not see any criteria. All I see are the records that have length and width filled in. Is there other criteria that is not visable in a query design view? Not all records require the user to enter length and width, why is it not showing the records with nothing in the length/Width fields???? My last thought, before I complety loose it, is that maybe I was trying to put some code that was suggested to me into an event and I didn't erase everthing. I wanted the user to be required to enter data in the length/width field if there is an X in the Sq ft coloumn of the paper lookup table and the finish lookup table Paper Description Rate Sq ft Gloss .50 X Finish Description Rate Sq ft Laminate .25 X Thank you so much for your help!!1 I just have no clue what to do next, this is driving me Nuts!! "Michel Walsh" wrote: If the data is in the table but not in the form, you may have a filter on the form. If the form is based on a query, then, when you look at the query, in data view, do you see the records? if not, then, probably, you have a criteria in the query that filter out those records. Remove the criteria if that is the case. Sure, the easier way is to edit the data in the table, add the required data in the field (so you will be able to see the records in the form), but that won't avoid the problem to re-occur. You can always design the table to NOT ALLOW NULL under your field , if that makes sense. But knot having all the details, I strongly suspect you used a filter or a criteria which eliminates the records. A little bit if you have a criteria under a quantity field that said: 10 then only records having their quantity value 10 would be picked up. So start with the query. Is your records are available there? if not, remove the criteria which makes no sense. If the data is there, then open the form, and remove the form filter, if there is one. Vanderghast, Access MVP. "Yula" wrote in message ... Michael, Thank you, but I am still lost. I substituted your first formula into my nish field and I am getting a calculation. The problem is that I want to see all the data that I entered into the database. I am missing 7 records because they did not require me to enter anything into the Length/Width fields. I see all that data in the tables, bit not in the form/query. Is it possible to fix? or should I erase them from my tables and reenter those records? "Michel Walsh" wrote: If there is no finish rate defined, what would be the result? You can use: =Nz( [Finish Rate] * [Copies Requested] * iif( [Finish Square Foot]="X", [Sq Ft], 1), "undefined" ) which will display "undefined" (without quote) if any of the three fields is not defined. You could also have use a Format to format the NULL values as displaying "undefined" instead of the blank you should see at the moment when a null is involved in the computation. Vanderghast, Access MVP "Yula" wrote in message news I have a combo box in a form (Based on a query) that looks up values in the Finish Lookup table. The table has the following info: Finish Description Finish Rate Finish Sq Ft Coil .45 Cutting 0 Foam 3.50 X Folded 0 Lamintated .218 X The next text box has length Width To get the finish cost I have the following formula: =IIf([Finish Square Foot]="X",[Finish Rate]*[Sq Ft]*[Copies Requested],[Finish Rate]*[Copies Requested]) After I add new records, I only see the records where I choice a finish description that has a finish rate associated with it, I do not see all records with the zeros for the finish rate. What confuses me is that I see all the tables being populated with all the info that I enter (0 rate descriptions and actual rate descriptions) I have to present all this info today and I have no clue why the query is only showing those records with the rates associated with them. I don't have any criterias set up. Thanks so much, Yula |
Thread Tools | |
Display Modes | |
|
|