Apr 11 2006, 02:23 PM
I have this code to find a record in a form. My problem arises when the user doesn't type in the complete EmployeeID.
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)
Apr 11 2006, 02:29 PM
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.
Apr 11 2006, 02:30 PM
Ahhh... ok. Thanks Frank!
Apr 11 2006, 02:33 PM
This is what I ended up with:
EmpCodeSearch = DLookup("[EmployeeCode]", "Employees", "[EmployeeID] Like '" & Searcher & "*'")
Apr 11 2006, 02:34 PM
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.
Apr 11 2006, 02:36 PM
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.
Apr 11 2006, 02:41 PM
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.
Apr 11 2006, 02:43 PM
You're welcome, Jen. I am glad I helped.
Apr 11 2006, 03:50 PM
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 & "*'")
Apr 11 2006, 05:35 PM
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... =(
Apr 13 2006, 09:30 AM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here