QUICK SEARCHING PROBLEM

[jodym43]

Hello All,
I have the following code on a form used to search a table. My goal is that if someone enters any information into the field, it will search for any entry's that contains the entered string. I also want it so that if anyone leaves the field blank, it will return every record.

The search part works great. My problem is that any record that has a null value for that field won't get returned.

If IsNull(Me![MapNameNumber]) Then
Criteria = [MapName/Number] Like ""*"""
Else: Criteria = [MapName/Number] Like ""*" &
Me.MapNameNumber & "*"""
End If

I've tried including something like:

Criteria = [MapName/Number] Like "("*" OR null)""

and just get compile errors. I'm not even sure if I'm heading down the right path or not. If anyone has any insight or quick ideas, I would appreciate it.
Thanks again,
jodym43

 

[jebry]

Hi!

I'm making two assumptions here; 1. Criteria is declared as a string and 2. [MapName/Number] is the field in your table. With those two assumptions being correct your structure should be:

Criteria = "[MapName/Number] Like '*'" for returning everything and
Criteria = "[MapName/Number] Like '*" & Me.MapNameNumber & "*'" when you need to limit the field.

hth
Jeff Bridgham
P.S. If my two assumptions are wrong, repost with more information.

 

[jodym43]

Jebry--
Yes, your assumptions are correct. I was trying to limit the ammount of code that I copied to the board to hopefully eliminate any extraneous confusion about the question I was asking.

Out of curiousity, what is the difference in placing the single quote (') vs the double quote (&quot regarding how the code reads it???? I made the changes to the single quote (apostrophy), but it still doesn't return any record that has a null value.

If IsNull(Me![MapNameNumber]) Then
Criteria = "[MapNameNumber] Like '*'"
Else: (yada yada yada)
End If

BTW--thank you very much for your help with this and my previous post. Ususally I'm pretty good about locating things on my own (or at least tracking down examples of code over the internet or in books) but haven't had very much luck on these last few problems.

jodym43

 

[jebry]

Hi!

Actually there shouldn't be much difference I'm just used to the single quote. How are you using Criteria after you set it? Maybe we are looking at the wrong part of the code. Could you post the whole procedure if it isn't too long?

Jeff Bridgham

 

[jodym43]

Here is the code minus a couple of commented out areas...I hope that the line spacing comes out wide enough....it's pretty confusing when it carries to the next line. The form is basically several input fields with a check box next to each to specify which fields you would like to search by. If the box is checked, then it includes that specific field in it's query.


Dim dB As Database
Dim rst As Recordset
Dim Criteria As String
Dim strSQL As String

Criteria = "[MapID] like '*'"
Debug.Print Criteria
If chkMapCounty Then
If IsNull(Me![MapCounty]) Then
Criteria = Criteria & " AND [MapCounty] Like ""*"""
Else: Criteria = Criteria & " AND [MapCounty] Like ""*" & Me.MapCounty & "*"""
End If
End If
If chkMapType Then
If IsNull(Me![MapType]) Then
Criteria = Criteria & " AND [MapType] Like ""*"""
Else: Criteria = Criteria & " AND [MapType] Like ""*" & Me.MapType & "*"""
End If
End If
If chkMapNameNumber Then
If IsNull(Me![MapNameNumber]) Then
Criteria = Criteria & " AND [MapName/Number] Like '*'"
Else: Criteria = Criteria & " AND [MapName/Number] Like ""*" & Me.MapNameNumber & "*"""
End If
End If
If chkMapBookNumber Then
If IsNull(Me![MapBookNumber]) Then
Criteria = Criteria & " AND [MapBookNumber] Like '*'"
Else: Criteria = Criteria & " AND [MapBookNumber] Like ""*" & Me.MapBookNumber & "*"""
End If
End If
If chkMapBeginPageRange Then
If IsNull(Me![MapBeginPageRange]) Then
Criteria = Criteria & " AND [MapBeginPageRange] Like '*'"
Else: Criteria = Criteria & " AND [MapBeginPageRange] Like ""*" & Me.MapBeginPageRange & "*"""
End If
End If
If chkMapEndPageRange Then
If IsNull(Me![MapEndPageRange]) Then
Criteria = Criteria & " AND [MapEndPageRange] Like '*'"
Else: Criteria = Criteria & " AND [MapEndPageRange] Like ""*" & Me.MapEndPageRange & "*"""
End If
End If


Debug.Print Criteria

Dim stDocName As String

stDocName = "Maps_View"

Debug.Print stLinkCriteria
DoCmd.OpenForm stDocName, , , Criteria



End Sub

 

[jebry]

Hi!

The first thing I noticed is that you named your text boxes the same as you named the fields. That can get Access confused so I would recommend changing the name of the text boxes. I'll continue to look at the code to see if I notice anything else.

hth
Jeff Bridgham

 

[jodym43]

Actually, it shouldn't be the same name, I've added a chk in front of each, but it's close.

Appreciate your help.