If-Then Help! Seems Easy..

[Guest]

This question is hard to explain but basically im getting a runtime error#13
at the bottom where it says: "if strng then". Idk if thats proper syntax or
not but im trying to make it so that i can have a search and a search
criteria withuot doing ALL the different IF combonations. This is the best
way i could think of and it doesnt seem to like the whole string being the
criteria. Could anyone please help me.

dim strng As String

Set find = CurrentDb.OpenRecordset("Name", dbOpenDynaset)
find.MoveFirst

If chkJob.value = -1 Then
If Len(strng) = 0 Then
strng = "find!title = " & cboJobtitle
Else
strng = strng & " AND find!title = " & cboJobtitle
End If
End If
If chkyears.value = -1 Then
If Len(strng) = 0 Then
strng = "find!yrs = " & txtYrs
Else
strng = strng & " AND find!yrs = " & txtYrs
End If
End If
If chkdegree.value = -1 Then
If Len(strng) = 0 Then
strng = "find!degree = " & cboDegree
Else
strng = strng & " AND find!degree = " & cboDegree
End If
End If
If chkpmi.value = -1 Then
If Len(strng) = 0 Then
strng = "find!title = " & cboJobtitle
Else
strng = strng & " AND find!pmi = " & cboPMI
End If
End If
If chkmort.value = -1 Then
If Len(strng) = 0 Then
strng = "find!mortgage = " & cboMort
Else
strng = strng & " AND find!mortgage = " & cboMort
End If
End If

Do Until find.EOF
If strng Then
ReDim Preserve names(x)
names(x) = find!id
x = x + 1
End If
find.MoveNext
Loop

 

[Marshall Barton]

This question is hard to explain but basically im getting a runtime error#13
at the bottom where it says: "if strng then". Idk if thats proper syntax or
not but im trying to make it so that i can have a search and a search
criteria withuot doing ALL the different IF combonations. This is the best
way i could think of and it doesnt seem to like the whole string being the
criteria.

dim strng As String

Set find = CurrentDb.OpenRecordset("Name", dbOpenDynaset)
find.MoveFirst

If chkJob.value = -1 Then
If Len(strng) = 0 Then
strng = "find!title = " & cboJobtitle
Else
strng = strng & " AND find!title = " & cboJobtitle
End If
End If
If chkyears.value = -1 Then
If Len(strng) = 0 Then
strng = "find!yrs = " & txtYrs
Else
strng = strng & " AND find!yrs = " & txtYrs
End If
End If
If chkdegree.value = -1 Then
If Len(strng) = 0 Then
strng = "find!degree = " & cboDegree
Else
strng = strng & " AND find!degree = " & cboDegree
End If
End If
If chkpmi.value = -1 Then
If Len(strng) = 0 Then
strng = "find!title = " & cboJobtitle
Else
strng = strng & " AND find!pmi = " & cboPMI
End If
End If
If chkmort.value = -1 Then
If Len(strng) = 0 Then
strng = "find!mortgage = " & cboMort
Else
strng = strng & " AND find!mortgage = " & cboMort
End If
End If

Do Until find.EOF
If strng Then
ReDim Preserve names(x)
names(x) = find!id
x = x + 1
End If
find.MoveNext
Loop

The If condition needs to be a comparison that results in a
true or false. You are trying to use a string.

Aside from your question, the loop doesn't seem to make
sense to me, maybe there's more code that does something
unusual with the array???

Generally, you should use the criteria string in the
OpenRecordset's query The way you have it is backwards (or
missing a bunch of logic).

Note that "Name" and "Find" are terrible names for tables or
anything else.

Your use of ! in the criteria string is either woring or you
have somthing else wrong.

It also appears that you are using check boxes to indicate
that a criteria should be used. This is unecessary because
it is easy to check if the criteria control has a value or
not. I use code like this:

If Not IsNull(cboDegree) Then
strWhere = " AND fldDegree = """ & Me.cboDegree & """"
End If
If Not IsNull(txtYrs) Then
strWhere = " AND fldYrs = " & Me.txtYrs
End If
. . .
strSQL = SELECT & FROM Name WHERE " & Mid(strWhere, 6)
Set find = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)