Improved Find Dialog

[BenHarper]

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

 

[BenHarper]

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).]

 

[BenHarper]

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

 

[BenHarper]

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

 

[BenHarper]

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