?
Solved

field type property in VB

Posted on 2000-02-18
8
Medium Priority
?
433 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Accepted Solution

by:
repstein earned 150 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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