Hi Everyone

I have a form with a button on it. Pressing this button opens a report which displays the results from a table. I have about 7 text fields which I can use search (Name, propertyNameNumber, Street, Locality, Town, County and Postcode). So there are obviously quite a few different combinations to search by. So when searching by Name, I need the coding.....

Me.txtOffendersFullName.Value = Me.txtOffendersFullName.Value & "*"
DoCmd.OpenReport "Show Details", acViewPreview

....and when searching by name and propertyNameNumber i need the code....

Me.txtOffendersFullName.Value = Me.txtOffendersFullName.Value & "*"
Me.txtPropNameNum.Value = Me.txtPropNameNum.Value & "*"
DoCmd.OpenReport "Show Details", acViewPreview

The problem is the amount of combinations I can search by using the 7 textfields. For example the following code shows how many combinations there are and the mass amount of coding involved with only 3 combinations.

Does anyone know any other way to do this?

Thanks a lot, ohh, and here's the code........sorry about the size



If IsNull(Me.txtOffendersFullName) And IsNull(Me.txtOffendersPropertyNameNumber) _
And IsNull(Me.txtStreet) And IsNull(Me.txtLocality) _
And IsNull(Me.txtTown) And IsNull(Me.txtCounty) And IsNull(Me.txtPostcode) Then
MsgBox ("Please type in a value to search for")
Else
If IsNull(Me.txtPerName) And IsNull(Me.txtPerAddress) And Not IsNull(Me.dDownIntCat) Then
Me.dDownIntCat.Value = "*" & Me.dDownIntCat.Value & "*"
MsgBox ("Search by Int")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
Else
If IsNull(Me.txtPerName) And IsNull(Me.dDownIntCat) And Not IsNull(Me.txtPerAddress) Then
Me.txtPerAddress.Value = "*" & Me.txtPerAddress.Value & "*"
MsgBox ("Search by Address")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
Else
If IsNull(Me.txtPerAddress) And IsNull(Me.dDownIntCat) And Not IsNull(Me.txtPerName) Then
Me.txtPerName.Value = "*" & Me.txtPerName.Value & "*"
MsgBox ("Search by Name")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
Else
If IsNull(Me.dDownIntCat) And Not IsNull(Me.txtPerName) And Not IsNull(Me.txtPerAddress) Then
Me.txtPerName.Value = "*" & Me.txtPerName.Value & "*"
Me.txtPerAddress.Value = "*" & Me.txtPerAddress.Value & "*"
MsgBox ("Search by Name and Address")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
Else
If IsNull(Me.txtPerAddress) And Not IsNull(Me.txtPerName) And Not IsNull(Me.dDownIntCat) Then
Me.txtPerName.Value = "*" & Me.txtPerName.Value & "*"
Me.dDownIntCat.Value = "*" & Me.dDownIntCat.Value & "*"
MsgBox ("Search by Name and Int")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
Else
If IsNull(Me.txtPerName) And Not IsNull(Me.txtPerAddress) And Not IsNull(Me.dDownIntCat) Then
Me.txtPerAddress.Value = "*" & Me.txtPerAddress.Value & "*"
Me.dDownIntCat.Value = "*" & Me.dDownIntCat.Value & "*"
MsgBox ("Search by Address and Int")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
Else
If Not IsNull(Me.txtPerName) And Not IsNull(Me.txtPerAddress) And Not IsNull(Me.dDownIntCat) Then
Me.txtPerName.Value = "*" & Me.txtPerName.Value & "*"
Me.txtPerAddress.Value = "*" & Me.txtPerAddress.Value & "*"
Me.dDownIntCat.Value = "*" & Me.dDownIntCat.Value & "*"
MsgBox ("Search using all three fields")
DoCmd.OpenForm "test1b"
DoCmd.Close acForm, "test1a"
End If
End If
End If
End If
End If
End If
End If
DoCmd.OpenReport "Show Details", acViewPreview
End If

It's only your message box that needs all the criteria to be checked simultaneously. You could do:

Dim MyString as String

MyString = "Search using: "

If Not IsNull(Me.txtPerName)
Me.txtPerName.Value = "*" & Me.txtPerName.Value & "*"
MyString = MyString & "Name" )
End if

If Not IsNull(Me.txtPerAddress)
Me.txtPerAddress.Value = "*" & Me.txtPerAddress.Value & "*"
MyString = MyString & "Address " )
End if

If Not IsNull(Me.dDownIntCat)
Me.dDownIntCat.Value = "*" & Me.dDownIntCat.Value & "*"
MyString = MyString & "Int " )
End if

MsgBox (MyString)
...

Just a general observation: I have found nested Select Case...End Case to be easier to code and maintain than monstrous If statements.

Hi Ann

Thanks for the response, I used your code which I'll show at the end of this post.

I'm not sure if that will work thought. I'll explain what the set up is a little more if you

don't mind helping me that is. You'll notice in the code I open the report once I've sorted out what values

i want to search by, and then added the wildcards to the beginning and end of the values from

the textboxes. The report I open is bound to a query. In the query, under the person's fullname

, propertyNameNUmber etc I have....

Like [Forms]![Create Search - Report]![txtOffendersFullName]
Like [Forms]![Create Search - Report]![txtOffendersPropertyNameNumber]
Like [Forms]![Create Search - Report]![txtOffendersStreet]
....
etc

Lets say all of person no. 1's details are all "a" (for every value in the database)....and
Lets say all of person no. 2's details are all "b"

With your coding if you type "a" in the name and "b" in the "address" then it displays all of

person's 1 and person's 2s details. Whereas I would like it to NOT display any details as no-one in the

database matches this criteria. And obviously only typing in "a" in one or both the name and address fields will show person 1 only.

ppheeeww, its actually hard work trying to explain a system, hope that made sence.

Cheers

Dave

The code currently being used......

If Not IsNull(Me.txtOffendersFullName) Then
Me.txtOffendersFullName.Value = "*" & Me.txtOffendersFullName.Value & "*"
MyString = MyString & "Name"
DoCmd.OpenReport "Show Details", acViewPreview
End If

If Not IsNull(Me.txtOffendersPropertyNameNumber) Then
Me.txtOffendersPropertyNameNumber.Value = "*" & Me.txtOffendersPropertyNameNumber.Value & "*"
MyString = MyString & "Address "
DoCmd.OpenReport "Show Details", acViewPreview
End If

If Not IsNull(Me.txtOffendersStreet) Then
Me.txtOffendersStreet.Value = "*" & Me.txtOffendersStreet.Value & "*"
MyString = MyString & "Int "
DoCmd.OpenReport "Show Details", acViewPreview
End If

Sorry David, had to nip out.

All I did was rearrange the code and change the way the message box is populated. I didn't change the criteria - at least I didn't think I had.

Can you post the database - or at least the SQL for the query. Then I can see a bit more of what's going on as this should be totally doable without millions of nested Ifs.

Hi Anne

Sorry thanks for helping me. I'll attach the database. The form you are looking at is called "Create Search - Report", and the code is on the one that says "Search (If Statement)"

Any help will be greatly appreciated.

Dave

Dave,

I can't post the database back because I've only got 2003, but this should be easy enough. There are a couple of problems here.

First off, in the query, if the criteria are on different lines, then they are either / or - so having "like form.name" on one line and "like form.address" on another line will catch records with either value - not both.

So, put all the criteria on the same line in the query. The SQL should say something like:
WHERE (((UsedBackup.offendersFullName) Like [Forms]![Create Search - Report]![txtOffendersFullName]) AND ((UsedBackup.offendersPropertyNameNumber) Like ...

Be aware that any field in the query that has a criteria MUST have a value set on the form - see how below - otherwise the record will not be found - even if it matches al lthe other criteria.

Second, the values in the form. Two problems - one, if the value of a field on the form is null, the query will ignore it, so we need to do something with these. Second, we need to check ALL the fields before opening the report so we catch all the criteria the user wants. I've cut this down to two fields to make it simple, but the same theory will apply to them all. So the code behind the button is:



Hope this makes sense - it can be a pain getting multiple criteria right, but once you have the pattern, it should all fall into place.

I'll give that a try Anne

Thanks for looking

Dave

No problem - hope you get it going, but if not, come back and let me know

Your search works like a treat, it knows exactly what its searching by, its quality. However, I'm getting in a total muddle with the Parenthesis. There is so much going on with it, I've tried to break it down. Do you think this looks right?

WHERE((([UsedBackup].[offendersFullName]) Like [Forms]![Create Search - Report]![txtOffendersFullName]) And (([UsedBackup].[offendersPropertyNameNumber]) Like [Forms]![Create Search - Report]![txtOffendersPropertyNameNumber]) And (([UsedBackup].[offendersStreet]) Like [Forms]![Create Search - Report]![txtOffendersStreet]) And (([UsedBackup].[offendersLocality]) Like [Forms]![Create Search - Report]![txtOffendersLocality]) And (([UsedBackup].[offendersTown]) Like [Forms]![Create Search - Report]![txtOffendersTown]) And (([UsedBackup].[offendersCounty]) Like [Forms]![Create Search - Report]![txtOffendersCounty]) And (([UsedBackup].[offendersPostcode]) Like [Forms]![Create Search - Report]![txtOffendersPostcode]))

Its ok, its because I had a WHERE in it, I'll try again thanks

Wow, that works absolutely fine, thanks a lot Anne, I think it would have taken me weeks to work out that one.

Really appreciated

Cheers

Dave

If you create it in design view, Access will put in all the parenthesis it wants. If you want to to it in SQL, leave them all out - again Access will do the needful. You don't HAVE to have them, because there are no groupings - you're not trying to say

(A or B) AND (C or D)

You just want

A and B and C and D ...