[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1068
  • Last Modified:

Filter listbox displayed in form

I have a list-box in a form (Access 2003) that is displaying records in a table.

That listbox is being correctly filtered and working PERFECTLY (if I might add)  :)  - It contains a bunch of user names that I have listed as firstname.lastname in the same single cell.

Now, I know I should have made my database with firstname and lastname separated, but it's WAY too late for that, at this point in the game.  :)

BUT, due to security reasons, I need to filter the listbox as it's displayed in the form to display ONLY the firstname up to the period, and then first initial of the last name.

Would anybody happen to know the formula I can apply to the code of the list box to make this happen?

Thanks in advance.
0
usslindstrom
Asked:
usslindstrom
  • 5
  • 4
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If your names are all entered as you indicate - i.e. scott.mcdaniel or peter.smith - then you can do this:

FirstName:Left(YourNameField, InStr(1,YourNameField, ".") +1)

PUt this in the first row of a query column, and your query will contain a new "field" named FirstName.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
FirstName:Left(YourNameField, InStr(1,YourNameField, ".") +1) & ", " Mid(YourNameField, InStr(1,YourNameField, ".") +1,1)
0
 
usslindstromAuthor Commented:
Please forgive me for not quite understanding where to put this code....

Should I be looking at this on the listbox within the form, as a "before update" code?

LMSCons - you mention put it in the first row of a query column...  Do I have to make a query that pulls data from the original table, or can't I just add it to the listbox as is?

Please forgive me, learning Access as I go along - most everything to this point has been Googled.  :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd be better off using a query to fill your listbox; in Design view, select the .RowSource property of your listbox, and click the Build button (the small button immediately to the right of the box), and build a query to drive your listbox. From here you can add FirstName column as indicated. You can set the .ColumnCount and .ColumnWidths property to show/hide the various columns in your listbox.
0
 
usslindstromAuthor Commented:
Thanks for everything up to this point - you guys are great.

Unfortunately, when I build the query to the suggested fornula, Access pings me on "Enter a Paremeter Value" for the FirstName field I created in the Query.

I noticed that when I type the word "FirstName" in the column title, that it changes to "Expr1: [FirstName]" - without the quotes of course...  Meaning, it'll be looking for a cell in the table called "FirstName" and it can't find it (since it doesn't exist)...

Sorry if I missed something.       Access isn't very friendly if ya' ask me.  :)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First, build your query without the calculate field (the Expr1: field - just delete that column). Once you have it returning the correct values, then add your calculcated field. Note that it must be in this format:

FirstName:

With the colon in place (this is the Column name). Now add the other items, so it'd look like this:

FirstName:Left(YourNameField, InStr(1,YourNameField, ".") +1)

Obviously you'll need to change YourNameField to match the name of the table column containing your Names.
0
 
usslindstromAuthor Commented:
Please forgive me, I really did try with this code.

Still having a hard time understanding where it goes.  I've attached a snapshot of the listbox sql query...  Can you please elaborate on where to drop in that line?

Thanks for being patient with me.
SQL-Expression-Builder.JPG
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd add the expression to the "Field" row, in the first empty column (immediately to the right of the TEacher column). Assuming you want to do this with the Student field:

FirstName:Left(Student, InStr(1,Student, ".") +1)

Now open your query in Datasheet view, and you should see a new column named FirstName. Use this query for your listbox.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Snapshot of the Query:
QueryImage.png
0
 
usslindstromAuthor Commented:
You sir, are nothing short of pure genious!

:)

Thank you VERY much for being patient in getting me through this!

After I saw your arrow, I realized what I had been doing wrong this whole time.  I had originally kept trying to put that formula in the "criteria" block of the new column.  Instead, it was the title itself that needed the formula.

Thank you very much!  The formula works like a champ!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now