Solved

field type property in VB

Posted on 2000-02-18
8
425 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now