Query not showing all results

[SoCal]

Hello,

I have been working on this for awhile, and am definitely missing something.

I have a query that is pulling data from multiple tables. When on of the fields are blank, it does not show the result in the query. I have tried Is Null in the Criteria and Is Not Null in the OR section with no success.

The field in the query that has no value is a text field. Is this why the Is Null and Is Not Null are not working?

Any help will be appreciated!

Thanks

 

[MSAccessRookie]

Are you using an INNER JOIN to join the Tables?

An INNER JOIN will only select records where BOTH Tables have equal values in the JOIN Field. If Either is Null, the record will not be selected.


To Get around this:

• A LEFT JOIN will select all of the records from the Table on the LEFT and will supply NULL Values for any Fields from the Table on the RIGHT if the Table has a NULL Value in the JOIN Field.
• A RIGHT JOIN will select all of the records from the Table on the RIGHT and will supply NULL Values for any Fields from the Table on the LEFT if the Table has a NULL Value in the JOIN Field.

If BOTH Tables have missing Entries, then a UNION Query may be required.

-- Rookie

 

[SoCal]

Got it. Worked perfectly! Thanks for the Help!!

 

[MSAccessRookie]

Got it. Worked perfectly! Thanks for the Help!!

I am curious as to which of the resolutions you required.

 

[SoCal]

The LEFT Join worked. I can now see all of my results.

Next problem - I can't get any calculations to work in the query. I put [fieldname] + [fieldname2] in the expression, and it askes me for a parameter value for [fieldname]. I have the fields in the query, all visible, and pulling the correct data, but calculations will not work.

Any help?

Thanks!

 

[MSAccessRookie]

The LEFT Join worked. I can now see all of my results.

Next problem - I can't get any calculations to work in the query. I put [fieldname] + [fieldname2] in the expression, and it askes me for a parameter value for [fieldname]. I have the fields in the query, all visible, and pulling the correct data, but calculations will not work.

Any help?

Thanks!

Try formatting the Field Name using brackets (like [FieldName]). You may also need to include the Table Name (like [TableName].[FieldName]).

 

[SoCal]

Hi there,

Tried both ways, and still not working. If there is a value in the top (first) row of the query, the calculation works fine - I have [HeadWholesale] with data entered, and I can add, subtract, multiply, etc. by this value. However, when I try to add [Headwholesale] + [Shaftwholesale] (when [shaftwholesale] has no value in the field) the calculation does not work.

Any ideas? and Thanks!

 

[MSAccessRookie]

Show us the complete current version of the Query that is failing and perhaps we will be able to make further observations.