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

[vbaInet]

Some people like their steak burned anyway

But you wouldn't have known about the cons of building a search form that way if you hadn't used it or got to know about it earlier on.

 

[namliam]

But I am sure the 3 star chef that normally runs the kitchen didnt teach him to burn it

 

[Galaxiom]

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.

Your code would still exclude records that had Null in any of the searched fields.

I have built several dynamic SQL search tools and never even once required the use of WHERE 1=1. That kludge is used to allow an AND before the first significant subclause.

 

[Eljefegeneo]

Thank you to everyone who has responded. Please do remember that while I am not a "novice", my understanding of the intricacies of SQL are very limited. I have learned quite a bit from this forum and I am more than extremely grateful for all the help that I have received.

I have not had a chance to figure which steps I should take, but I am sure with everyone's help I will find the answer that I am seeking.

I will post something on my findings tomorrow.

 

[Eljefegeneo]

Not sure what is happening, but when I used the example in post #15, I get about the same results as before I started. So I went back into the query design view and it has so many conditions that I do not think I could reproduce them here. The SQL statement is extremely large. So I don't think that is the answer. When I look at the design view of the query, the part after the "or" is gone and a second line appears.

After thinking about this, I went back to my original query and thought about which fields in the underlying table always had data and which ones might have no data. They are [Surname] and [Telephone] which are txtSurname and txtAreaCode on the search form. If I use ... or is null on the txtAreaCode, then I can find all the records in a state that have data with data in the surname. If I add ... or is null to the txtSurname then I get all the records in the state including those with no data in the Surname, even if I am searching for a specific name in a specific State. So that is not the answer.

Then after more consideration, I realized that although it would be nice to have all the criteria in one query, I would never search for a name and a telephone number area code, so I deleted the criteria for the telephone number. I get a different result. There are only five records, the one I specifically searched for and four others with no data in surname. But searching only on the State in question, i.e., AK for Alaska, there are 26 records, some with data in Surname and some with no data.

So I am back to square 1-1/2. Better than before but no roses yet.

I am going to try a few more things tomorrow, but if there are any more suggestions on this, I am very appreciative.

 

[Eljefegeneo]

I did not mean to send the post just yet.

I am going to try the suggestion by namliam if I can figure out what the SQL should be.

 

[tehNellie]

with due respect, you are a novice, you're generating dynamic SQL in code rather than creating a query and passing parameters to it which would at least let you debug your SQL independently from your code and vice versa and, when it comes to passing those parameters, identify more explicitly where your problems are.

Just because Access encourages people to be silly doesn't mean you have to follow its advice.

There are times when it is useful to generate dynamic SQL in code, this, as far as I can see isn't one of them.

 

[Eljefegeneo]

Yes I am a novice, but I do try to follow advice. I am not trying to create some exotic query. All I want to do is to be able to create a search form that conforms to what my users have requested. That being said, I am going to go back to square 1, create a query with the fields on which I want to search, and then go through them one by one seeing how two or more react to each other.

It may just be that what I want to do is not possible, and I may look at the various fields and decide which ones should be in the query and which ones may be deleted, that is the search criteria for them. This is a method that has proven good to me in the past. One step at a time for novices.

I do appreciate all comments. But please remember that there are perhaps 10,000 novices out there for every MVP.

And thanks to the MVPs that are trying to help.

 

[Eljefegeneo]

P.S. the problems are in the fields that may or may not contain data. They are the Surname and the Telephone fields in the table. All the others must have data in them. So I will see what the criteria should be with respect to those fields that may be "Is Null" and the others which have to be "Not Is Null".

 

[tehNellie]

You can do it, the wider question is perhaps whether you should be able, or need, to.

I'm not trying to suggest some fancy Dan solution, I'm suggesting a practical approach to your development that will help you develop and bug fix your solutions moving forwards.

DIm strSQL as string

strSQL = "SELECT"
strSQL = strsql & " acolumn"
strSQL = stsql & "  from atable"

is a bug fix nightmare. you can't test your SQL outside the code and, frequently, you're (generic you) fighting your expectations around what you think the SQL does versus what you actually wrote.

On top of that you're passing unvalidated control values into the SQL


Write a query, test it, save it, define some parameters into it and then embed it into your code. At that point your SQL is tested and you know it works.

Here's a basic query

PARAMETERS [@testId] long;
SELECT
  column
FROM table
WHERE anId = [@testID] OR anID IS NULL

You can test this until the cows come home, outside of your code, you know what value it takes and you can test that as well.

Your code is now simple, explicit and equally easy to test:

dim qdf as dao.querydef
dim rs = dao.recordset

set qdf = currentdb.querydefs("theQuery")
qdf.parameter("[@TestID]").value = 1234

set rs = qdf.openrecorset

Now, the wider question around Dealing with NULL is a lot easier to deal with because you've already written and testing a query that you know works with it while you figure out whether allowing NULL was such a good idea to begin with, safe in the knowledge that your code doesn't care one way or another.

 

[namliam]

Your code would still exclude records that had Null in any of the searched fields.

I have built several dynamic SQL search tools and never even once required the use of WHERE 1=1. That kludge is used to allow an AND before the first significant subclause.

True 1=1 is a way to make things a little easier, however it isnt as incommon as you may think... I see it all over the place even at this new environment I am in now for a few month only... Yes the Like will still exclude NULL in the column that is searched, however you want to search for name like "*namliam*" then you dont want to see NULL values anyways.
This however prevents like "**" in other columns that do exclude NULL values there that you may want to see.

Using Debug.print strSQL you can test the sql just fine without any problems and sure designing the basic SQL in the query builder is offcourse the way to go... It is faster and more accurate than any developer can be, even for someone like me that knows SQL inside and out.
However cutting the SQL into pieces and only using the parts you need in a "dynamic" query is not that hard to do... AND gives a less experienced person a perfect solution, because they only need to worry about that what they are doing and not worry about ZLS and NULL ....

 

[Galaxiom]

True 1=1 is a way to make things a little easier, however it isnt as incommon as you may think... I see it all over the place even at this new environment I am in now for a few month only... Yes the Like will still exclude NULL in the column that is searched, however you want to search for name like "*namliam*" then you dont want to see NULL values anyways.
This however prevents like "**" in other columns that do exclude NULL values there that you may want to see.
.

Something being common does not mean it a good practice. The fact is WHERE 1=1 doesn't actually achieve anything significant.

 

[namliam]

It achieves readability and a continuity to the query building in dynamic queries in general.

True in a "true" seperate SQL statement its useless but dynamic queries see it a lot.
The 1=1 or even the 2=1 "way" is kindoff black and white, either you like it and love it or you hate it with a passion.

 

[Eljefegeneo]

Ok, this is WAY over my head. The novice has cried "uncle"! I tried to figure out the code in post #30, but after an hour my head was still spinning. I think I understand the principle of what is trying to be related to me (and possibly others), but when I try to use it in a query, I keep getting the message that the query is looking for a "Where" statement.

So, I am going to be happy with what I have. Maybe I will return to this subject later on, but I have more pressing issues with my DB. Like finishing it.

However, I would like to leave with this thought. There is the now famous dynamic search form that has been quoted on this forum in the past, and I have used it many times. But one cannot search by say field1 and field2 and possible field3. It only brings up a result based on the search criteria. That is, if one is searching for the name Jones, you will get all people named Jones, all people living on Jones street and all people living in the city of Jones.
But most of my users have indicated that they would like to have a search form as I first described. That is, five or six possible search criteria, so that one can look for someone named Jones in the state of New York.
As a novice who has used this forum numerous times and have been helped so many times I cannot count, I would like to thank all who have participated in this thread. I just ask you to remember that us novices are sometimes mystified by what is being posted.

Thanks to all.

 

[namliam]

I usually pride myself on being able to help anyone and use as much the common english vs the tech speak as I can...

I feel saddened by the fact you feel overwhelmed and backed into a corner to the point you have to abandon this

 

[Eljefegeneo]

I haven't abandoned this quest, merely postponed it for the time being. I truly believe that I can solve it, but I have numerous and more pressing issues on the DB to finish. And I know that this forum will help me. I look at the forum almost every day and learn a great deal. Thanks!

 

[Uncle Gizmo]

However, I would like to leave with this thought. There is the now famous dynamic search form that has been quoted on this forum in the past, and I have used it many times. But one cannot search by say field1 and field2 and possible field3.

Is this what you are After?

Building Advanced Search Criteria 7

Added later:-
Actually this is a better reply to your question:-

Building Advanced Search Criteria 1