?
Solved

Using VBA to filter a field by first letter?

Posted on 2003-03-03
6
Medium Priority
?
375 Views
Last Modified: 2007-12-19
I'm working on a small inventory database that lists all of the software at my company.  I am attempting to make a form that allows a user to search for a software package by first letter of the application name.

I've tried to write some VBA code to do this, but I am new to A2k and VBA so I am sure I am missing something since I cannot get this to work.  I always get an error stating that I cannot assign a value to the Me.Filter action.  The following is the code I was trying to use:

Begin Code
----------------------------------
Private Sub Sort_By_First_Letter_Of_Program_Name_Click()
On Error GoTo Err_Sort_By_First_Letter_Of_Program_Name_Click
    MsgBox "Hello World"
    Dim strLetter As String, strInput As String
    strInput = "Please type in the first letter of the program name: "
    strLetter = InputBox(Prompt:=strInput)
    Me.Filter = "ProgramName = LIKE M*"
    Me.FilterOn = True


   

Exit_Sort_By_First_Letter_Of_Program_Name_Click:
    Exit Sub

Err_Sort_By_First_Letter_Of_Program_Name_Click:
    MsgBox Err.Description
    Resume Exit_Sort_By_First_Letter_Of_Program_Name_Click
   
End Sub
----------------------------------------
End Code

I am beginning to think I may need to use ADO or DAO to do this, but I am unfamiliar with how to use either one of them.  Any pointers or hints would be greatly appreciated.
0
Comment
Question by:cjweti
[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
  • 4
6 Comments
 
LVL 12

Accepted Solution

by:
nexusnation earned 300 total points
ID: 8059404
try the below.

Begin Code
----------------------------------
Private Sub Sort_By_First_Letter_Of_Program_Name_Click()
On Error GoTo Err_Sort_By_First_Letter_Of_Program_Name_Click
   MsgBox "Hello World"
   Dim strLetter As String, strInput As String
   strInput = "Please type in the first letter of the program name: "
   strLetter = InputBox(Prompt:=strInput)
   Me.Filter = "[ProgramName] = "LIKE" & strinput & "*"
   Me.FilterOn = True

Exit_Sort_By_First_Letter_Of_Program_Name_Click:
   Exit Sub

Err_Sort_By_First_Letter_Of_Program_Name_Click:
   MsgBox Err.Description
   Resume Exit_Sort_By_First_Letter_Of_Program_Name_Click
   
End Sub
----------------------------------------
End Code
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8059452
very very minor item--this line:

Me.Filter = "[ProgramName] = "LIKE" & strinput & "*"

will generate an error.


Change it to:

Me.Filter = "[ProgramName] LIKE '" & strinput & "*'"

1) notice the space after the work LIKE, and

2) the strInput needs to be enclosed in '....' s

AW
0
 

Author Comment

by:cjweti
ID: 8059484
I tried the code you posted nexusnation, and now I am getting a Type Mismatch error.  

At first I thought it might just be a missing quotation mark or the use of strInput in the Me.Filter line instead of strLetter, but I get the same error regardless of which variable I use.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:cjweti
ID: 8059491
I tried the code you posted nexusnation, and now I am getting a Type Mismatch error.  

At first I thought it might just be a missing quotation mark or the use of strInput in the Me.Filter line instead of strLetter, but I get the same error regardless of which variable I use.
0
 

Author Comment

by:cjweti
ID: 8059503
Arthur Wood,

Your line does work, however I had to use strLetter instead of strInput as the variable.

Thanks for the fast response nexusnation and Arthur.  I'll award points here shortly.
0
 

Author Comment

by:cjweti
ID: 8059592
Fast answer, minor issues with line format and incorrect variable use but other than that right on.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 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