DLOOKUP - RuTime 13 - Type Mismatch

[Christopher770]

UPDATE:

Update: I have been dicking around with this for 2 day's and still stuck (I'm being reminded of one of the definitions of a moron; keep doing the same thing over and over and expecting the same results)

Code:
Me.P1DMIL = (DLookup("[SWD]", "tblDSP010", ("[SWFROM] = '" & Forms!frmLHTEMPLATE!P1OT & " And [SWTO] = '" & Forms!frmLHTEMPLATE!P1DTID)))

Returns: Syntax error (missing operator)in query expression '[tblDSP010]![SWFROM] = 'ATL And [tblDSP010]![SWTO] = 'JCY'.

a) Notice the missing single quote in [SWFROM = 'ATL. {a clue}

================================================
I cannot believe this is that f'ing difficult and/or that there is not a better way to do this.

Simply put, I'm trying to look up a value in an unrelated table by using two combo boxes. After I get the values from the combo box, use that to look up the distinct record from another table and return a specific column

I'm frustrated, but humbly asking for some advice here.



ORIGINAL POST
==================================================================
• Data Entry Form based on table: tblLHTEMPLATE
• The form [frmLHTEMPLATE] has two combo boxes that lookup and return values from table: tblAAP030
o A: frmLHTEMPLATE!P1OT
o B: frmLHTEMPLATE!P1DTID

Problem: Form has an unbound control named [P1DMIL] to display a numeric value. I need to display a value from table:tblDSP010 based on the matching association from the forms combo boxes.

To do this I'm using a DLOOKUP as shown below: The problem is I'm getting a RunTime Error – Type Mismatch error. I've validated every control on the form and field in the tables. I'm suspect that the problem is my ignorance combined with a syntax error.

The Statement in an Event Statement set on "after update" of combo box B: frmLHTEMPLATE!P1DTID
==================================
Dim P1DMIL As Variant
Me.P1DMIL = (DLookup("[SWD]", "tblDSP010", ("[SWFROM] = " & Forms!frmLHTEMPLATE!P1OT And "[SWTO] = " & Forms!frmLHTEMPLATE!P1DTID)))
End Sub
===================================

My mind is now mush after 5 hours of dicking with this and realized I need to turn to the geniuses and beg for help.
Thank you in advance for anyone taking the time to weigh in on this!!

Christopher

 

[Galaxiom]

Me.P1DMIL = DLookup("[SWD]", "tblDSP010", "[SWFROM] = " & Forms!frmLHTEMPLATE!P1OT & " And [SWTO] = " & Forms!frmLHTEMPLATE!P1DTID)

 

[Christopher770]

First thank you, but now a new error. (see attachment). The result it shows is one of the values that would have been selected from the first CBbox lookup. However, the desired result would/should be a numeric value.

 

[Christopher770]

Oh..one other thing and probably more importantly, can you suggest a link that specifically defines the demarkation of the syntax. I have yet to find something that clearly defines the use of the double quotes ("), single quote('), and the ampersand (&) and what exactly their function is.

 

[Galaxiom]

I have never seen that error before. Not really helpful is it?

The DLookup arguments are strings. Normally they are delimited by double quotes.

The ampersand is the string concatenator. It is used to concatenate values from variables or Access objects into the string arguments.

Single quotes are used as string delimiters when the string is nested inside a double quoted string. For example when comparing a text field to a string.
eg

"[textfield]= '" & stringvariable & "'"