Can’t get the first record. (1 Viewer)

Eljefegeneo · Aug 21, 2014

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 · Aug 21, 2014

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

Eljefegeneo · Aug 21, 2014

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 · Aug 21, 2014

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

Eljefegeneo · Aug 21, 2014

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 · Aug 21, 2014

Eljefegeneo said:
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?

Eljefegeneo said:
Code:

((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.

Eljefegeneo said:
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 · Aug 21, 2014

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 · Aug 21, 2014

Galaxiom said:
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 · Aug 21, 2014

vbaInet said:
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 · Aug 22, 2014

Indirectly all non null records.

Galaxiom · Aug 22, 2014

vbaInet said:
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 · Aug 22, 2014

Galaxiom said:
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 · Aug 22, 2014

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 · Aug 22, 2014

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 · Aug 22, 2014

Eljefegeneo said:
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:

Code:

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 · Aug 22, 2014

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.

Code:

'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 · Aug 22, 2014

namliam said:
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 · Aug 22, 2014

vbaInet said:
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 · Aug 22, 2014

namliam said:
I disagree, half solutions lead to big problems.

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

namliam · Aug 22, 2014

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 !

Can’t get the first record. (1 Viewer)

Still trying to learn

Nifty Access Guy

Still trying to learn

AWF VIP

Still trying to learn

AWF VIP

Super Moderator

AWF VIP

Super Moderator

AWF VIP

Super Moderator

AWF VIP

Super Moderator

Still trying to learn

AWF VIP

The Mailman - AWF VIP

AWF VIP

The Mailman - AWF VIP

AWF VIP

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread