Help! DLookup w/Variable

S

swngdncr

OK, this is driving me crazy. I'm going to pull all my hair out in a
moment. I've searched all the threads etc., and still cannot format
this code to work correctly. I've tried every version of single and
double quotation marks and ampersands that I've found in examples that
appear to be identical to what I'm trying to do. I've also tried to
dim the variables as String and Long instead of Variant. Nada works...
I would be ever so greatful if someone could assist me with this. The
relevant portions of the code are:

Dim varExistWO As Variant
Dim varExistWOLocation As Variant

varExistWOLocation = DLookup("[WorkOrderID]",
"tblWorkOrdersLocations", _
"[WorkOrderID]= '" & varNewWO & "'" & _
" And [LocationID] = '" & varNewLocation & "'")
 
S

swngdncr

Sorry, I left out :

Dim varNewWO As Variant
Dim varNewLocation As Variant

varNewWO = Forms!frmWorkOrders!WorkOrderID
varNewLocation =
Forms!frmWorkOrders!frmWorkOrdersLocationsSubform!cmbSite
 
S

swngdncr

Sorry, I left out :

Dim varNewWO As Variant
Dim varNewLocation As Variant

varNewWO = Forms!frmWorkOrders!WorkOrderID
varNewLocation =
Forms!frmWorkOrders!frmWorkOrdersLocationsSubform!cmbSite
 
J

John Vinson

OK, this is driving me crazy. I'm going to pull all my hair out in a
moment. I've searched all the threads etc., and still cannot format
this code to work correctly. I've tried every version of single and
double quotation marks and ampersands that I've found in examples that
appear to be identical to what I'm trying to do. I've also tried to
dim the variables as String and Long instead of Variant. Nada works...
I would be ever so greatful if someone could assist me with this. The
relevant portions of the code are:

Dim varExistWO As Variant
Dim varExistWOLocation As Variant

varExistWOLocation = DLookup("[WorkOrderID]",
"tblWorkOrdersLocations", _
"[WorkOrderID]= '" & varNewWO & "'" & _
" And [LocationID] = '" & varNewLocation & "'")
Click to expand...

If WorkOrderID and LocationID are Number fields (Long Integer or
Autonumber ID fields) in tblWorkOrderLocations, you should not use ANY
delimiter:

varExistWOLocation = DLookup("[WorkOrderID]",
"tblWorkOrdersLocations", _
"[WorkOrderID]= " & varNewWO & _
" And [LocationID] = " & varNewLocation)

Text fields need either ' or " as delimiter; Date/Time fields need #;
Number or Currency fields get no delimiter at all.

Note that if varNewW0 or varNewLocation might be NULL you will indeed
get an error. You may want to use NZ() to convert NULL values to 0 (or
to some other value which will not match any record in your table).

John W. Vinson[MVP]
 
Top