Access Filter loses new records 1

[SellOut]

Hello,

I have a large access database with several users and tables etc.. I have a query that shows only the records applicable to my area from a large table (filtered on one column). In this query I have added two new records filling in about half the columns and leaving the others blank (NB the primary key column is filled). The data appears now in the main table ok and in the original query when I open it, however when I modify the query to filter by another column I have problems. In this column 'Status', cells are blank unless records have been amended at some point. I want to filter out all the 'deleted' records so I use <>"deleted" in the design view. This produces a list of all the records that are not classified as deleted, however my 2 new records are not there (they have empty cells, like many others, so should appear) in total 1424 recods appear. When I filter for just the deleted "deleted", I get 56 records. If I don't filter in that column at all I get 1482 (The mystery new two are present again). Why does the information appear in the query when it isnt filtered by the second column but appears in neither group when it is?

SO

 

[MJPPaba]

The Null state of these fields 'blank' fields are counted as neither 'deleted' nor 'not deleted'. You need to amend your code to include the option of a null field.

Use this as your criteria...

<>"deleted" or Is Null



The only bad question is the question you dont ask!

 

[SellOut]

Hello,

You are right but I am still slightly confused. The table (imported from excel) contains lots of empty squares which seem to not be Null. They don't appear to contain anything, not even a 'space', but are still counted as not null. Is there a way to change either my empty cells to null or the null ones to empty so that things are consistant?!

Thanks for your help,

SO

 

[MJPPaba]

Hi SO,

When importing an excel spreadsheet into access empty cells are regarded as string cells containing "" rather than a NULL or absolutely blank value.

To rectify this, once you have imported the table, view the table in 'design view' by clicking on the set square in the top left corner...

The screen is split into to two halves:
top
bottom

Within the top half you will see all the fields in your table, select the first field.
Within the bottom half of the sceen locate the General Option "Default Value"
Enter next to this a double quote "". This will ensure that any new entries created but not specifically entered into that cell are regarded as a blank rather than a NULL.

Repeat this process for each field in the table.

Cheers

Matthew
Crazypabs.

The only bad question is the question you dont ask!

 

[SellOut]

Matthew,

Thanks for your help, that is working very well!

SO