SeanGraflund
asked on
field type property in VB
I'm running a keyword search on a form that works like this ..
Dim strSearch as String
Dim fld as Field
strSearch = ""
For each fld in Me.recordsetclone.fields
If (fld.Type = dbText or fld.Type = dbmemo) Then
strSearch = strSearch & fld.Name & " Like " & Chr(34) & "*" & me.keywordsearch & "*" & Chr(34) & " OR "
End If
If (fld.Type = dbInteger) Then
strSearch = strSearch & fld.Name & " = " & me.keywordsearch & " OR "
End If
Next fld
strSearch = Left(strSearch, Len(strSearch) - 4)
me.Filter = strSearch
me.FilterOn = True
Now, this all works, except it doesn't recognize the integer fields. If generates the strSearch for memo or text fields, but doesn't for integer fields.
Is there something I'm missing in the fld.Type property?
Thanks,
Dim strSearch as String
Dim fld as Field
strSearch = ""
For each fld in Me.recordsetclone.fields
If (fld.Type = dbText or fld.Type = dbmemo) Then
strSearch = strSearch & fld.Name & " Like " & Chr(34) & "*" & me.keywordsearch & "*" & Chr(34) & " OR "
End If
If (fld.Type = dbInteger) Then
strSearch = strSearch & fld.Name & " = " & me.keywordsearch & " OR "
End If
Next fld
strSearch = Left(strSearch, Len(strSearch) - 4)
me.Filter = strSearch
me.FilterOn = True
Now, this all works, except it doesn't recognize the integer fields. If generates the strSearch for memo or text fields, but doesn't for integer fields.
Is there something I'm missing in the fld.Type property?
Thanks,
Sean:
Shouldn't me.keywordsearch be bracketed by single quotes (or chr(34)) in the second if clause?
Richard
Shouldn't me.keywordsearch be bracketed by single quotes (or chr(34)) in the second if clause?
Richard
SeanGraflund,
You could debug the field types adding the following line into your code (loop):
Debug.Print fld.Name & ", Type=" & fld.Type
3 -> Integer
4 -> Long Integer
Hope this helps,
Paasky
You could debug the field types adding the following line into your code (loop):
Debug.Print fld.Name & ", Type=" & fld.Type
3 -> Integer
4 -> Long Integer
Hope this helps,
Paasky
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, Pasky was right, you need to check all different types of Integers .. dbInteger won't work if the field is dbLongInteger, etc. So, what I ended up doing was just removing that value for the Integers, and am querying off of all fields. If I use the Like keyword instead of "=", it doesn't matter if the field is type text or integer, it uses the hard quotes "*" & value & "*" no matter what .. even in Date fields.
I'll take Pasky's answer.
I'll take Pasky's answer.
ASKER
Doh, I ended up accidentally clicking Repstein's answer instead of Papsky's. However, I realized this problem before an answer was really proposed. Papsky's would be the way I would've figured out my problem if I had read it. There's no way unfortunately to take back points I don't think ...
To: Sean & paasky
I posted a request to Community Support to reassign the points. Hope it works.
Richard
I posted a request to Community Support to reassign the points. Hope it works.
Richard
paasky,
Please see the question I have posted for you in this topic area...
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10298589
darinw
Customer Service
Please see the question I have posted for you in this topic area...
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10298589
darinw
Customer Service
just a guess, could it be Long integer (dbLong)?
regards,
Paasky