I need to create a unique ID based on 4 fields in a table - all are strings, despite 2 that look like numbers. Two of those fields are determined by user input on a form. A 3rd is based on the 2 user inputs plus static data (the "Like") which is derived from a user selected option box on the same form. The 4th is based on the previous 3 bits of data. I am stuck in getting the 3rd bit of data. No errors show up with the code itself, but I am getting a "type mismatch" error when I run it. I have tried declaring the fields within the Sub, as well as creating hidden controls on the form to get this to work. I'm stuck and haven't been able to find anything that matches what I'm trying to do. Dlookup syntax is my absolute nemesis. Current code is as follows:
RequestedAnswer = DLookup("[RequestedField]", "AssociatedTable", "[Field1] LIKE '" & "StaticCriteria" & "'" And "[Field2] = '" & Control1/Dim1 & "'" And "[Field3] " & Control2/Dim2)
"RequestedField" is text in the table, although it looks like a number (000, 005, 006, etc)
I have tried numerous iterations of brackets, single quotes, double quotes, parentheses. I did create an Access query and was able to get the required data, so I thought of using SQL, but no luck - I still got errors. I've gotten null values (despite getting 2 of the 3 bits of data), so I am certain the problem lies with the "Like" section.
I don't think DLookup is your nemesis. Your real issue seems to be building strings correctly using constants and variables. You are not constructing the Where part of DLookup correctly. You are missing quotes, asterisks in your Like as well as an Equal sign after Field3. Are you aware that Control1/Dim1 is a constant (enclosed in quotes) and Control2/Dim2 is a formula? You need to ensure that Dim2 is not zero if you intend this to be a formula. One last thing, Field3 must be numeric otherwise you will need to follow the Field2 syntax and enclose it in quotes.
Without seeing the surrounding code, here is my interpretation of the corrected syntax:
RequestedAnswer = DLookup("[RequestedField]", "AssociatedTable", "[Field1] LIKE '*" & "StaticCriteria" & "*' And [Field2] = '" & Control1/Dim1 & "' And [Field3] = " & Control2/Dim2)
To assist in your debugging efforts, set a breakpoint at the DLookup line. When the code stops, copy and paste the entire Where clause into the Immediate window preceded by a question mark (eg. ?"[Field1] LIKE '*" & "StaticCriteria" & "'* And [Field2] = '" & Control1/Dim1 & "' And [Field3] = " & Control2/Dim2). Position the cursor on that line and press Enter.This will allow you to see the string that is being generated or it will throw an error if the syntax is incorrect. You can then manipulate that line until it produces the string you want. Copy and paste that back into your code.