Query excludes records with certain blank fields (Access 101) 2

[rdy4trvl]

This simple query that combines data from two simple tables. Main table (400 records) includes a year field with data in 2 digit form (e.g. 1998 is shown as 98). The Year table includes both the 2 and 4 digit year. The two tables are linked by the 2 digit year code so my query provides the proper 4 digit year (1998). (There are probably better ways to handle this simple conversion – open to suggestions.)

Here is the problem: When I run the query, only 300 of the 400 records show - this appear to be because 100 records in the Main table have no 2 digit year. I’ve even tried running the query with “is null” in the year criteria but get no results at all. Is there some setting in the query that I must change to get all the records even if the linked field is blank?

 

[SteveR77]

What is the join property of the two tables set to. By default it will be to only show the value of the fields in the two tables where the fields are equal.

 

[Hap007]

I can only guess since you have not included the SQL.

In the design view of the query, click (highlight0 on the relationship line between the 2 tables, then right click on that same line and select 'Join Properties'

Now, in the 'Join Properties' window, select the option:

'Include all records from table xx(where your 400 records are stored) and only those records from table yy (Your year table) where the joined fields are equal.'

That should solve your problem.

Enjoy,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers