Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

field type property in VB

Posted on 2000-02-18
8
Medium Priority
?
434 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

876 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