Query not showing new records added though they exist in table. (1 Viewer)

[Ahmsv1]

Hello,
I have a simple database in access 2007. It contains only one table.
I have a form called qryAll. From this form the user can enter criteria for a query that queries all of the fields in a table. (so you can query with criteria from more that one field)

Anyway this all works fine. I imported data into the database from excel, some 900 records and with these records the query works as expected.

Now I notice when I add a new record, It wont appear in query results. Even if I search for it by its ID, yet it appears in the table.

This has me stumped. Im no advanced user. I have tried clicking 'refresh all' but it makes no difference. Ive been searching for some time now for a solution and now I seek your help!

If it helps, I went into the query and went to SQL view. See below.

SELECT [TBL CANDIDATES].*, [TBL CANDIDATES].ID, [TBL CANDIDATES].CANDIDATE_NAME, [TBL CANDIDATES].ADDRESS, [TBL CANDIDATES].PHONE1, [TBL CANDIDATES].PHONE2, [TBL CANDIDATES].TYPE_OF_WORK
FROM [TBL CANDIDATES]
WHERE ((([TBL CANDIDATES].ID) Like "*" & [forms]![Candidate Search Form]![txtcriteria6] & "*") AND (([TBL CANDIDATES].CANDIDATE_NAME) Like "*" & [forms]![Candidate Search Form]![txtcriteria2] & "*") AND (([TBL CANDIDATES].ADDRESS) Like "*" & [forms]![Candidate Search Form]![txtcriteria4] & "*") AND (([TBL CANDIDATES].PHONE1) Like "*" & [forms]![Candidate Search Form]![txtcriteria3] & "*") AND (([TBL CANDIDATES].PHONE2) Like "*" & [forms]![Candidate Search Form]![txtcriteria5] & "*") AND (([TBL CANDIDATES].TYPE_OF_WORK) Like "*" & [forms]![Candidate Search Form]![txtcriteria] & "*"));


Thanks for reading.
Ams

 

[jdraw]

You are "ANDing" your criteria. That is you want records with criteria1 AND criteria2 AND criteria3 etc. Do you really have records in your table that have ALL the criteria?

You might want to try "OR" instead of "AND" just to see what recdords are selected.

Good luck

 

[Galaxiom]

New records don't appear in a query until it is rerun but I don't think this is the main problem.

Your query will not find any record that contains a Null in any field. The Excel import probably inserted the NullString for blanks where a new record in Access won't do this.

One way is to change your query to deal with the Nulls. Each field in the query can be placed in an Nz function to substitute the NullString for Null
Nz([fieldname]."")

Another is to include a default value in the table design (or controls on the form) for the fields that are not entered.

Also note that [tablename].* returns all fields. Don't repeat them in the select.

You don't need to repeat the tablename where the query is on one table. (Access will put that in automatically if you use the designer but it is unnecessary if you write the sql yourself).

 

[Ahmsv1]

New records don't appear in a query until it is rerun but I don't think this is the main problem.

Your query will not find any record that contains a Null in any field. The Excel import probably inserted the NullString for blanks where a new record in Access won't do this.

One way is to change your query to deal with the Nulls. Each field in the query can be placed in an Nz function to substitute the NullString for Null
Nz([fieldname]."")

Another is to include a default value in the table design (or controls on the form) for the fields that are not entered.

Also note that [tablename].* returns all fields. Don't repeat them in the select.

You don't need to repeat the tablename where the query is on one table. (Access will put that in automatically if you use the designer but it is unnecessary if you write the sql yourself).

Excellent post. I think you hit the nail on the head with the points on null values and excel substituting them, it makes perfect sense.

It's 2:20 am so I shall act on this after a sleep!
Also I did not write the excel I used the designer. Is this bad SQL? Should I try editing it ( not that I currently have the knowledge of how)

Also jdraw noted I am AND ing all my criteria. I thought this would be good as I may want to search for candidates who satisfy multiple criteria.
The code has Like "*" in it, which if I'm not wrong means the record only needs to contain a portion of the criteria entered. Thus if it is blank, and the record is blank it would be a match.

Anyway I'll try and figure out how to implement your suggestion regarding null characters and feedback tomorrow b
Goodnight and thanks!

 

[Ahmsv1]

Hello

To firstly look at whether the problem lies in null fields I have added records which have content in every field. However these are still not featuring in the search results so im thinking this wont be it?

 

[vbaInet]

Lessen the criteria until they become visible. Remove all criteria first and see if it appears. If it does then start putting back the criteria one by one. You will be able to spot which criteria is restricting the new records.

 

[Ahmsv1]

very logical suggestion vbaInet, im working on it now thanks

 

[vbaInet]

Let us know how you get on.

 

[Ahmsv1]

Hi Again folks, So like was suggested by vbaInet I removed all criteria, and added them back one by one.
And immediately the queries now return the new records entered.
I have just created another brand new record and it returns in the query.

I have changed nothing, not a bean. So it makes me think that this has not changed whatever caused it and it may happen again.
What do you folks think?

 

[vbaInet]

When you initially had the problem, did rerun the query after importing? Like pressing refresh or close and reopen the query.

 

[Ahmsv1]

Hi,
In normal operation, this query is run from a form accessed by the user via the switchboard.
When it is run, the button simply runs the query and displays it to a form.
There is no refreshing happening that I know of.
The database has been closed and reopened numerous times now.

 

[vbaInet]

Does the user perform the import?

 

[Ahmsv1]

hello again.

Earlier to check if the problem was null field related I created new records in which all fields had values and they did not show up, so i dismissed this.

Then I tried as per vbaInet has suggested and now I am able to see these records I added.

Now, I have added a new complete record.
I then went through and deleted the content from one of the fields which are in the query criteria, and it did not show up in search results.
I replaced the criteria and alas it showed up in query results.
I repeated this for all of the 6 fields which are criteria and got the same result.

So now I believe I am back to the Null problem.
Would it be suitable in the query criteria, for each field we are searchin to encapsulate the criteria in some kind of If field not Null then apply criteria statement?

 

[Ahmsv1]

Does the user perform the import?

Hi,
The data was initially imported from excel. From that point onwards any additional data is entered by the user via a form I created.

 

[vbaInet]

Something like this (which you would need to apply to the rest):

WHERE ((([TBL CANDIDATES].ID) Like IIF(IsNull([forms]![Candidate Search Form]![txtcriteria6]),"*", "*" & [forms]![Candidate Search Form]![txtcriteria6] & "*") AND ...

 

[Ahmsv1]

Looking back to what GalaxiomAtHome, who was right about the Null thingy.
I tried to put the Nz function into the SQL which was a disaster.
I then went into design view for the query and put it in there, and now I can delete the field which it applies to and the record is still returned in the query.

Im going to apply this to all fields in the criertia, and hopefully thats it sorted!

Many thanks for the excellent posts by all, ill report back

 

[Ahmsv1]

Something like this (which you would need to apply to the rest):

WHERE ((([TBL CANDIDATES].ID) Like IIF(IsNull([forms]![Candidate Search Form]![txtcriteria6]),"*", "*" & [forms]![Candidate Search Form]![txtcriteria6] & "*") AND ...

Aah thats how it would be in the SQL.
Now I have two methods to use. If one fails ill use the other. working on it now!

 

[vbaInet]

If it's Nz() then it would be:

WHERE ((([TBL CANDIDATES].ID) Like "*" & [COLOR=Red][B]Nz([/B][/COLOR][forms]![Candidate Search Form]![txtcriteria6][COLOR=Red][B], "")[/B][/COLOR] & "*") AND

 

[Ahmsv1]

Many thanks.
I beleive the problem has been solved.
Firstly after using vbaInets logical suggestion of removing and re building the query got it working again and made me realise it really was a null character problem.

The Nz. method suggested by GalaxiomAtHome was used in the query via design view.

Many thanks guys you both have helped solve a problem in the universe!

 

[HRN_SciTech]

Something like this (which you would need to apply to the rest):

WHERE ((([TBL CANDIDATES].ID) Like IIF(IsNull([forms]![Candidate Search Form]![txtcriteria6]),"*", "*" & [forms]![Candidate Search Form]![txtcriteria6] & "*") AND ...

Hello. Sorry to regurgitate this thread. I'm having the exact same problem so I'm trying to use the code you've written here but I can't get it to work for all 3 fields. Could you help me with where the "IIF(IsNull" is supposed to go in the following?

WHERE (((Chemicals.Chemical) Like "*" & [Forms]![ChemSearch]![Chemical] & "*") AND ((Chemicals.Synonyms) Like "*" & [Forms]![ChemSearch]![Synonyms] & "*") AND ((Chemicals.Category) Like "*" & [Forms]![ChemSearch]![Chemical] & "*"));


Thank you