Can’t get the first record.

[Eljefegeneo]

I have a simple search form that searches for, among other things, the surname.



The criteria for the query that is used for the search is very basic: Like "*" & [Forms]![frmSearch]![txtSurname] & "*". I can get any record I want except for the first record, that is, I have searched for the name that appears on the first record in the table where the search is made. I have even tried to search the city for this person and the name does not appear in the search.



And I have tried the usual remedies, compact and repair, etc. The query doesn't exclude any criteria, and there is nothing on the search form to exclude any names. I can search for ClientId number and the record does appear.



Is this just one of the strange quirks of Access (version 2010), or perhaps I have inadvertently done something to cause this.



It is not a big deal, but it is a conundrum worth investigation.

 

[Uncle Gizmo]

Does the name you are searching for happen to have a space at the front?

 

[Eljefegeneo]

No, there is no space. That was the first thing that I looked for. Then I tried typing something like ZZZZZ and the result was the same. All the search field criteria were basically the same format as the Surname search. So I changed other fields on the record hoping that there was something else that might be causing the problem, but I couldn't find anything. It just won't find the first record no matter what field I search on except for the ClientId number.

This may be just an unsolved mystery. I am going to try something. Make a copy of the DB, delete the first record in the table in question, then try to search for the new "first" record. Will let you know what happens.

Thanks for the reply.

 

[vbaInet]

And if you remove the filter completely can you actually see that first record? Show us the SQL.

 

[Eljefegeneo]

Yes, the record can be displayed as the first record since the records are sorted as ClientID ascending. The SQL for the query is as follows:

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone, ("Surname: "+IIf(Nz([Forms]![frmjeffSearch]![txtSurname],"")="",Null,[Forms]![frmjeffSearch]![txtSurname]+",")) AS Sur, ("Organization: "+IIf(Nz([Forms]![frmjeffSearch]![txtOrganization],"")="",Null,[Forms]![frmjeffSearch]![txtOrganization]+",")) AS Org, ("Program: "+IIf(Nz([Forms]![frmjeffSearch]![txtProgramTitle],"")="",Null,[Forms]![frmjeffSearch]![txtProgramTitle]+",")) AS Prog, ("City: "+IIf(Nz([Forms]![frmjeffSearch]![txtCity],"")="",Null,[Forms]![frmjeffSearch]![txtCity]+",")) AS Cty, ("State: "+IIf(Nz([Forms]![frmjeffSearch]![CboState],"")="",Null,[Forms]![frmjeffSearch]![CboState]+",")) AS St, ("Zip: "+IIf(Nz([Forms]![frmjeffSearch]![txtZip4],"")="",Null,[Forms]![frmjeffSearch]![txtZip4]+",")) AS Zip, ("Area Code: "+IIf(Nz([Forms]![frmjeffSearch]![txtAreaCode],"")="",Null,[Forms]![frmjeffSearch]![txtAreaCode]+",")) AS Ac
FROM tblMain
WHERE (((tblMain.Surname) Like "*" & [Forms]![frmJeffSearch]![txtSurname] & "*") AND ((tblMain.Organization) Like "*" & [Forms]![frmJeffSearch]![txtOrganization] & "*") AND ((tblMain.ProgramTitle) Like "*" & [Forms]![frmJeffSearch]![txtProgramTitle] & "*") AND ((tblMain.City) Like "*" & [Forms]![frmJeffSearch]![txtCity] & "*") AND ((tblMain.State) Like "*" & [Forms]![frmJeffSearch]![cboState] & "*") AND ((tblMain.Zip4) Like "*" & [Forms]![frmJeffSearch]![txtZip4] & "*") AND ((tblMain.Telephone) Like "(" & [Forms]![frmJeffSearch]![txtZip4] & "*"));


And I have figured out what is wrong. The first entry did not have a telephone number. But all the search codes are the same, each checking for a null or zero length string.

 

[vbaInet]

Yes, the record can be displayed as the first record since the records are sorted as ClientID ascending.

I don't understand what you said here. I was asking if all the records (plus the first record) get displayed if you remove all the criteria?

((tblMain.Telephone) Like "[COLOR="Red"][B][SIZE="3"]([/SIZE][/B][/COLOR]" & [Forms]![frmJeffSearch]![txtZip4] & "*"));

And I have figured out what is wrong. The first entry did not have a telephone number.

Look at the part in red.

But all the search codes are the same, each checking for a null or zero length string.

What do you mean by this? Your code either returns a match containing the search string or it returns all records.

 

[Galaxiom]

I don't see the Where clause checking for Nulls at all. A Null in any field will cause it not to return the record.

Each part of the Where clause needs to include "OR Is Null" because a Null is not equal to anything.

 

[vbaInet]

I don't see the Where clause checking for Nulls at all. A Null in any field will cause it not to return the record.

Each part of the Where clause needs to include "OR Is Null" because a Null is not equal to anything.

I see where you're coming from and if it was an equality search then "Or Is Null" is required but we're dealing with Like "*" & Null & "*" which equates to Like "**", meaning all records.

 

[Galaxiom]

I see where you're coming from and if it was an equality search then "Or Is Null" is required but we're dealing with Like "*" & Null & "*" which equates to Like "**", meaning all records.

Like "**" doesn't mean all records. It means any number of characters or none (ie a ZLS).

It won't find a Null because Null isn't Like anything either.

 

[vbaInet]

Indirectly all non null records.

 

[Galaxiom]

Indirectly all non null records.

Records that don't have Nulls in any of the fields tested.

Notice the Where subclauses are ANDed not ORed.

 

[vbaInet]

Records that don't have Nulls in any of the fields tested.

Notice the Where subclauses are ANDed not ORed.

I get your point. Multiple search criteria with ANDs and possibly some search criteria left blank.

Good catch!

 

[Galaxiom]

The code covers the balnk criteria because it regresses to Like "**".

The AND means that just one subclause that doesn't return True will miss the record.

 

[Eljefegeneo]

Thank you both. I think I now have it solved. Since the telephone field is one of two fields (also the surname field) in the underlying table that may not contain any data, I changed the criteria to:

Like "(" & [Forms]![frmJeffSearch]![txtAreaCode] & "*" Or Is Null

And voila!, the first record now shows.

And then, realizing what you are trying to tell me, I changed the surname search to include Or Is Null and now all records come up.

I hope my assumptions are correct. Thanks again.

 

[vbaInet]

Thank you both. I think I now have it solved. Since the telephone field is one of two fields (also the surname field) in the underlying table that may not contain any data, I changed the criteria to:

Like "(" & [Forms]![frmJeffSearch]![txtAreaCode] & "*" Or Is Null

I don't think so. In my post I pointed out a possible typo (again highlighted in red), and the "OR Is Null" part isn't quite what Galaxiom was talking about.

Each criteria should be in the form:

Like "[COLOR="red"]*[/COLOR]" & [Forms]![[COLOR="blue"]FormName[/COLOR]]![[COLOR="Blue"]FieldName[/COLOR]] & "*" Or [Forms]![[COLOR="blue"]FormName[/COLOR]]![[COLOR="Blue"]FieldName[/COLOR]] Is Null

 

[namliam]

You do not, let me re-peat, DO NOT want to go down this "Or is null" route with this query... it is a nightmare!
Instead you should build your sql in code custom to the search that the user actually wants....

I.e.

'NOTE the 1=1 is IMPORTANT you need it !
mysql = "Select ... from ... where 1=1 "
if not isnull(Me.txtAreacode) then
    myWhere = "AND Like ""*" & Me.txtAreacode & "*"" "
endif
if not isnull(Me.txtZip4) then
    myWhere = "AND Like ""*" & Me.txtZip4& "*"" "
endif
etc...
' now add your custom query to the recordsource without the last AND in there.
Yourform.recordsource = mysql & mywhere

This gets the exact search done the user wants without complicating things with Nulls or simular issues.

 

[vbaInet]

You do not, let me re-peat, DO NOT want to go down this "Or is null" route with this query... it is a nightmare!

No doubt it's a nightmare for a query with lots of criteria but it's a good starting point. Plus if you only have one criteria field then it's a good option.

The learner sees the complications and moves on to writing something more efficient.

 

[namliam]

No doubt it's a nightmare for a query with lots of criteria but it's a good starting point. Plus if you only have one criteria field then it's a good option.

I disagree, half solutions lead to big problems.

Same with i.e. DLookup, using it isnt a problem untill people get used to using it and use it 50 times in one form or something and get stuck because the database now "sucks" (i.e. it is very slow).
No you are using a half solution that only should be used here or there.

 

[vbaInet]

I disagree, half solutions lead to big problems.

It's not a half solution for a beginner. It's a learning process.

 

[namliam]

You are sitting in a restaurant and complain to a waiter, his/her reply:
Your steak is not burned sir, it is just a beginner cook learning.... it is a learning process, so eat it and like it.


Nah !