Solved

field type property in VB

Posted on 2000-02-18
8
430 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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