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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Mike EghtebasDatabase and Application DeveloperCommented:
FirstName:Left(YourNameField, InStr(1,YourNameField, ".") +1) & ", " Mid(YourNameField, InStr(1,YourNameField, ".") +1,1)
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.  :)
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
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.  :)
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:


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.
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Snapshot of the Query:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.