0

I have two tables that are related, I'm trying to get data from the "parent" table using information in the "child" table. I'm attempting to use DLookup but I am getting a mismatch data type error.

I have a username variable available in the sub I am calling from and have created the below code:

Dim sqlstr As String
Dim placehldr As Long

username = Me.OpenArgs
placehldr = DLookup("userFullName", "tblUsers", "username='" & username & "'")
userFullName = DLookup("employeeFullName", "tblEmployees", "[ID]='&placehldr&'")

placehldr looks up according to a username the userFullName field which was created with lookup wizard so that users can look up the employee. This returns the autoID number from the first table tblEmployees. I have tried this using placehldr as long, integer, and string, and I have tried placing apostrophes as if it were a string or integer, I have also tried mixing these and using string apostrophes with int/long etc. It always returns the same mismatch type error on the second DLookup. Any help would be appreciated.

Tyler Willke
  • 5
  • 4
  • Basic typo - use concatenate same as your first DLookup - you're leaving out the double quotes – dbmitch Apr 29 '22 at 19:07
  • As I said, I tried that. I've tried string and integer and it always returns type mismatch. – Tyler Willke Apr 29 '22 at 19:08
  • `"[ID]=" & placehldr` – dbmitch Apr 29 '22 at 19:09
  • just tried it as Dim sqlstr As String Dim placehldr As Long username = Me.OpenArgs placehldr = DLookup("userFullName", "tblUsers", "username='" & username & "'") userFullName = DLookup("employeeFullName", "tblEmployees", "[ID]='" & placehldr & "'") – Tyler Willke Apr 29 '22 at 19:10
  • If it's a number field don't wrap value in any quotes – dbmitch Apr 29 '22 at 19:11
  • Thank you @dbmitch, that worked. Didn't realize you could go without the quotation at the end. Do you know of anywhere that documents the quotation rules very well for this type of code? I haven't been able to find anything as of yet. – Tyler Willke Apr 29 '22 at 19:12
  • Delimiters: apostrophe (or doubled quotes) for text type, # for date/time type, nothing for number type. There is no reason to concatenate an empty string at the end. Or maybe use parameters or TempVars https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – June7 Apr 29 '22 at 19:19

1 Answers1

0
Dim sqlstr As String
Dim placehldr As Long

username = Me.OpenArgs
placehldr = DLookup("userFullName", "tblUsers", "username='" & username & "'")
userFullName = DLookup("employeeFullName", "tblEmployees", "[ID]=" & placehldr)

Per @dbmitch this worked correctly.

Tyler Willke
  • 5
  • 4
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 01 '22 at 16:25