I'm going to try this again. I have got somewhere, but now I'm stuck.
I have created a form that will be the find dialog popup. On it, so far I have 2 fields - first name and last name, however I will have a number of other fields, including address, D.O.B. and parent's names. This is to be a multi-talented find form that calls up the sought after record based on the input names (and whether all/some/one of the fields are used). However, I don't know how to get the code to ignore a field if it is left blank. It simply searches for the record with a Null value for the first name for example. Also, I am trying to work with IIf statements within the Where conditions of the OpenForm command. Access doesn't have a problem with the syntax when I type it, however it gives me an expected expression error when I run it.
This looks rather convoluted, but it is supposed to work. I don't know why I need the '= [CLIENT LIST]' at the end -
but I get an expected = error otherwise

DoCmd.OpenForm([CLIENT LIST], , , IIf([Forms]![Find]![cfirst] Is Null, "", [Forms]![Find]![cfirst] = [CLIENT LIST]![CLIENT NAME (FIRST)]) And IIf([clast] Is Null, "", [Forms]![Find]![clast] = [CLIENT LIST]![CLIENT NAME (LAST)])) = [CLIENT LIST]

Thanks. I know this isn't brief, but help would be greatly appreciated.
Ben

OK -- you are trying to do something that I have implemented in what I call the "multi-criteria query" form. It's too long and complicated to post everything here but I'll give you the general idea and tell you the book that I got it from.

When the user checks the "multi-search" button it open up this search form. The form has a textbox field with an associated checkbox for each searchable entity. All the textbox fields are dimmed (ENABLED=FALSE, LOCKED=FALSE) initially. Some of the entities are ranges (like start and end dates) and in that case the checkbox controls the associated pair of boxes. If the user wants to include a field (like COLOR) in the search, s/he checks the box. Code on the CLICK event of the checkbox (chkColor) sets the ENABLED property of the associated textbox (txtColor), and moves the focus to that box where the desired search data is typed in. Unchecking the box dims the field in case they change their mind. (My database is a DVD cataloger, thus the example used):

Private Sub chkColor_Click()
txtColor.Enabled = chkColor
If chkColor Then txtColor.SetFocus
End Sub

After any or all desired boxes have been checked, the user clicks the "Begin search" button and I begin to build an sql string based on the checked boxes. In the above example:

Dim strWhere As String

If chkColor And Not IsNull(txtColor) Then
strWhere = strWhere & " AND Color LIKE '" & txtColor & "'"
End If

Note that the string variable "strWhere" is being built by concatenating each search clause. After all possible checkboxes have been tested and the complete WHERE clause has been built, I strip off the initial " AND "

If Len(strWhere) > 0 Then
strSQL = Mid$(strWhere, 6)
End If

Now I have a WHERE clause that I can use to do the search. (I also put a "Reset all fields" box on the search form in case they want to start over.)

This entire process came from the book "Beginning Access 97 VBA Programming" (there is also a 2000 version) from Wrox Press.

[This message has been edited by jaxbuilder (edited 08-25-2000).]

[This message has been edited by jaxbuilder (edited 08-25-2000).]

Thank you very much, this helps a lot and makes it look a lot cleaner. However, I'm still encountering a problem.
When I run the code as follows,
DoCmd.OpenForm([CLIENT LIST], acNormal, , strSQL)
I end up with an 'expected: =' error.
I'm still learning, so it could be something very simple/obvious that I'm missing. Could you help?

Also, in the if statement:

If Len(strWhere) > 0 Then
strSQL = Mid$(strWhere, 6)
End If

is the strSQL the string I put in the where statement of the OpenForm, or is it strWhere?

(ie:
DoCmd.OpenForm([CLIENT LIST], acNormal, , strSQL)
or
DoCmd.OpenForm([CLIENT LIST], acNormal, , strWhere)
)

Thanks again,
Ben

Added part:

Okay, I've fixed the code to get rid of the 'expected: =' error, but now I get a datatype mismatch error. The new line is as follows:
DoCmd.OpenForm Forms![CLIENT LIST], , , strSQL
I don't know where the datatype mismatch is occuring.
Thanks

[This message has been edited by BenHarper (edited 08-28-2000).]

Are there any date fields involved? Those would have to use # characters, like this:

strWhere = strWhere & " AND datDate_of_disconnect >= #" & _
Format$(datDisconnected1, "mm/dd/yy") & "#"

And for any numeric data types you would not surround the search argument with single quotes.

[This message has been edited by jaxbuilder (edited 08-28-2000).]

Right now, I'm only working with normal text fields (first name and last name), however I might add a date field later, so thanks for the tip. I'm still coming up with the error 'An expression you entered is the wrong data type for one of the arguments'.

The entire sub is as follows:

Private Sub Find_Click()
On Error GoTo Err_Find_Click

Dim strWhere As String
Dim strSQL As String

If clastc And Not IsNull(clast) Then
strWhere = strWhere & " AND [CLIENT NAME (LAST)] LIKE '" & clast & "'"
End If

If Len(strWhere) > 0 Then
strSQL = Mid$(strWhere, 6)
End If

DoCmd.OpenForm Forms![CLIENT LIST], , , strWhere

DoCmd.Close acForm, Find, acSaveNo

Exit_Find_Click:
Exit Sub

Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click

End Sub

Note: I'm only trying to get the first field to work for now. The field's name on the table is [CLIENT NAME (LAST)]. The name of the checkbox on the find form is 'clastc' and the associated textbox is 'clast'.
There may be something wrong with the way I'm implementing your suggestions. I really appreciate your help.
Ben

When I open the form I use the syntax

DoCmd.OpenForm "frmDVD", acNormal, ,strSQL

It's not just the quotes causing your problem is it? Also note that you need to be using the string with the leading " AND " stripped off, which is strSQL

[This message has been edited by jaxbuilder (edited 08-29-2000).]

Okay. I made the changes you suggested, but now I get an even more confusing error.
This is the sub as it looks now. Not much different.

Private Sub Find_Click()
On Error GoTo Err_Find_Click

Dim strWhere As String
Dim strSQL As String

If clastc And Not IsNull(clastc) Then
strWhere = strWhere & "[CLIENT NAME (LAST)] LIKE '" & clast & "'"
End If

If Len(strWhere) > 0 Then
strSQL = Mid$(strWhere, 6)
End If

DoCmd.OpenForm "CLIENT LIST", acNormal, , strSQL

DoCmd.Close acForm, Find, acSaveNo

Exit_Find_Click:
Exit Sub

Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click

End Sub


The error I get is this:

Syntax Error (missing operator) in query expression 'NT NAME (LAST) LIKE 'Harper'

(I didn't use Harper, but a name that actually appears in the database)
I can't understand why the field name would be cleaved like that in the error message.
I know I didn't name my fields the most efficient way, but that isn't causing the problem - I tried replacing Client Name Last with another text field with a shorter one-word name and the error message also cleaved the first four letters.
This is quite frustrating. I even tried restarting Access, thinking the program was getting weird, but to no avail.
Help?
Thanks
Ben

You simply left off the initial " AND " from strWhere:

If clastc And Not IsNull(clastc) Then
strWhere = strWhere & " AND [CLIENT NAME (LAST)] LIKE '" & clast & "'"
End If

Thus when the mid$ function stripped off the initial five characters, it removed the first part of your field name.

Phew! Okay, everything works now. I must thank you very much, jax, for being so patient, helpful and punctual.
As it turns out, the main problem was the last DoCmd.Close statement that caused the datatype mismatch error.
Thanks again,
Ben