Solved

field type property in VB

Posted on 2000-02-18
8
428 Views
Last Modified: 2012-06-21
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,
0
Comment
Question by:SeanGraflund
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2535894
Hello SeanGraflund,

just a guess, could it be Long integer (dbLong)?

regards,
Paasky
0
 
LVL 2

Expert Comment

by:repstein
ID: 2537479
Sean:

Shouldn't me.keywordsearch  be bracketed by single quotes (or chr(34)) in the second if clause?

Richard
0
 
LVL 10

Expert Comment

by:paasky
ID: 2537913
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
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Accepted Solution

by:
repstein earned 50 total points
ID: 2538173
Sean:
  Forget about my last suggestion. Moral: never answer any questions after the second martini on a Friday night :Q

  There is a problem when the keywordsearch is a string, e.g. "Joe"

  When the code gets to an integer field, say AGE, the search string is going to evaluate to "AGE = Joe", which calls up an Enter Parameter Value box for the value of Joe.

  I am assuming that you want to restrict your keyword search to fields in the table that have the appropriate data type. (i.e., don't search for strings in numeric fields)
 
  If this assumption is correct, then you can use the IsNumeric function to evaluate the keywordsearch value and avoid searching numeric fields for text values. e.g:

....=dbInteger) then
If IsNumeric(me.keywordsearch) then
  strSearch = ...
end if
else
....

(IsNumeric evaluates to true only if the entire string is a number.)

Note that the text and memo fields will still be searched when keywordsearch is a number, since the number could really be a string.

Richard
0
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2542493
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.
0
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2542508
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 ...
0
 
LVL 2

Expert Comment

by:repstein
ID: 2542632
To: Sean & paasky

I posted a request to Community Support to reassign the points. Hope it works.

Richard
0
 
LVL 3

Expert Comment

by:darinw
ID: 2542899
paasky,

Please see the question I have posted for you in this topic area...

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10298589 

darinw
Customer Service
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question