NUMERIC AND TEXT FIELDS IN COMBOBOX

[laman]

I am having trouble with the following code. I have a form to search for records based on two combo boxes, one for the field " & field & " and the other for the operand "&_ operand &" with a text box for criteria "& criteria &". The fields are both numeric and text so I am trying to use two different statements If,Else. One of the numeric fields is VOLTS, so I tryed this when VOLTS was selected in the combobox and does not return any values. If I choose a text field it runs the second statement fine. May be a better way to do this.

Private Sub Command12_Click()

If " & field & " = VOLTS Then
strSQL = " Select * from master WHERE " & field & "" &_ operand & "" & criteria & ";"
Else
strSQL = " Select * from master WHERE " & field & "" &_ operand & "'" & criteria & "';"
End If
Me.result.RowSource = strSQL

End Sub

 

[TerpFan2001]

Laman:
Here is a function you can use to determine if the field that was selected in the combobox is a data type of Text. Just pass the values of the fieldname and tablename to it and it will return true if the field is a text field.

Public Function IsFieldText(sFieldName As String, sTableName As String) As Boolean

Dim tbl As TableDefs
Dim fld As Fields

Set tbl = CurrentDb.TableDefs
Set fld = tbl(sTableName).Fields

If fld(sFieldName).Type = dbText Then

IsFieldText = True

Else

IsFieldText = False

End If

End Function

In your command button, place this code like this:

If IsFieldText(Me.cboFieldName, "Master&quot = True Then...

else...

end if

As far as debugging the SQL statement, try placing the SQL string in a msgbox so that you can actually see what is being created before you pass it to the recordsource.

Msgbox strSQL

 

[laman]

Thanks very much! After I read your post I had it working within minutes. I would be lost without all the help I have gotten from this forum.

Thanks again for your time,
Laman