Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Filtering
Michael Albert 
      
3 years ago
I'm currently using a button to filter records. The field I'm filtering on happens to be a text field.  Now I want to filter a number (Single) field and cannot get it to work. Can you help?
Scott Axton  @Reply  
        
3 years ago
What is your table structure?  Field property.
What have you tried?

Need more info please.
Kevin Robertson  @Reply  
          
3 years ago
What have you tried?
Screenshots?

Filter Records
Michael Albert OP  @Reply  
      
3 years ago

Michael Albert OP  @Reply  
      
3 years ago


Look for course number.       (This is working fine)

Course Number:
  Field Name: Tbl_Trn_1_CourseNumber
   Data Type:  Short Text

Private Sub FindCourse_Click()
Dim S As String
    S = InputBox("Please Enter Value", "Find Course")   '(i.e. Field to use as default)
    If S = " " Then Exit Sub
    
    Me.Filter = "Tbl_Trn_1_CourseNumber  = """ & S & """"
    Me.FilterOn = True
End Sub
--------------------------------------------------------------------------------------------
Look for Group Code.       (This is not working)

Group Number:
   Field Name: Tbl_Trn_1_CourseGroup
    Data Type: Number (Single)


Private Sub FindGroup_Click()
    Dim S As Single
   S = InputBox("Please Enter Value", "Find Course Group")   '(i.e. Field to use as default)
  If IsNull(S) Then MsgBox ("Fill in a number")
   Exit Sub
MsgBox ("Passed")

    Me.Filter = "Tbl_Trn_1_CourseGroup  =  S "
    Me.FilterOn = True
  End Sub

Kevin Robertson  @Reply  
          
3 years ago
Input Boxes always return a String. I would store the value in a String and then convert to a Single (storing the value in another variable). Also, in the Me.Filter command you had the variable inside the quotes. The filter method was trying to set your field name equal to the character "S" instead of the value of the variable.

Here is how I would write this.

    Dim S As String, Num As Single
    
    S = InputBox("Please Enter Value", "Find Course Group")   '(i.e. Field to use as default)
    If StrPtr(S) = 0 Then Exit Sub 'the user clicked cancel
    If S <> "" Then
        Num = CSng(S)   'convert S to single, store in Num
    Else
        MsgBox ("Fill in a number")
        Exit Sub
    End If
    MsgBox ("Passed")

    Me.Filter = "Tbl_Trn_1_CourseGroup=" & Num
    Me.FilterOn = True
Scott Axton  @Reply  
        
3 years ago
In addition to what Kevin said, I also recommend that you discard the use of Single.  While it will work it really can confuse the issue trying to remember the data types in your code.
As you progress through the courses you will see that Richard only uses 5.
Long Integer, Short Text (string), Double (for decimals), Long Text, and logical or boolean (Yes / No).

Space and speed are not the issue that they once were so there really isn't a need to use Single. The advantage is if you ever do go over the limit you don't have to go back and change code.

I don't recall if there is a video specifically about Data Types but I'm sure Richard  covered this early on in the Beginner series.

Take a look at the Type Conversion video for how to convert the text to differing data types.

Michael Albert OP  @Reply  
      
3 years ago
Thanks very much guys.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/1/2026 11:43:14 PM. PLT: 1s