I have this code to find a record in a form. My problem arises when the user doesn't type in the complete EmployeeID.

For example:

My employee ID is JenniferD and the user doesnt know the whole ID, so they just type in Jenn:

Check out the last part of the dlookup statement, that's what I don't know the syntax for...


Dim Searcher As String, EmpCodeSearch As String

Searcher = InputBox("Employee to search for (any part of field):", "Find")
EmpCodeSearch = DLookup("[EmployeeCode]", "Employees", Left([EmployeeID], Len(Searcher)) = Searcher)

A wild card might be better here Jen:

EmpCodeSearch = DLookup("[EmployeeCode]", "Employees", "[EmployeeID] Like '" & Searcher & '" & "*")

I think I have it right, but it might take some fiddling to get it to work.

Ahhh... ok. Thanks Frank!

This is what I ended up with:


EmpCodeSearch = DLookup("[EmployeeCode]", "Employees", "[EmployeeID] Like '" & Searcher & "*'")

Just curious Jen why not the combobox wizard option number 3 ? "Find A record on my form based on the value I selected in my combo box"

Edited by: jmcwk on Tue Apr 11 15:34:52 EDT 2006.

I have the combobox locked so the user cant change the records once they're already entered. It's unlocked on a new record, but locks after that.

Your combo box you have locked can be set to limit to list. The combo box wizard using option 3 is an unbound combo and is limited to the control you select in the Find Process. Cerainly am not arguing with your methods or reasoning nor am I critisizing just trying to understand so please do not take it as critisism.

You're welcome, Jen. I am glad I helped.

The code you ended up with will only find matches where the code entered is the beginning part of the field. If you truly want it to find a match ANYWHERE in the field, you need wildcards on both sides:

EmpCodeSearch = DLookup("[EmployeeCode]", "Employees", "[EmployeeID] Like '*" & Searcher & "*'")

OK...

now I want to sum values from a table using DSum, yet I only want the first part of the recordID.

The recordID is formatted like this:

EmployeeCode - BillingPeriodCode - AutoNumber

I want to Dsum the MISC Expenses based on just the EmployeeCode & BillingPeriodCode within that field. I did have two separate fields for that, but my superior thought we should just combine it all into one... =(

Well, if you know the lengths of each part, you can use the Left$ function to compute the EmployeeCode & BillingPeriodCode. Or if there is a definite character separating the fields you could use InStr to find the position where the separator character occurred.

You could create TWO queries, one for each table, that did this, then join the queries together on the calculated fields to fetch the relevant data.