Lookup in two tables (2003)

[robm]

In the following code:-
Dim varIWO, varDesc As Variant
Dim Cancel As Integer
varIWO = DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]")
varDesc = DLookup("[Descline1]", "[Stock1]", "[Code] = [Part No]")


If (Not IsNull(varIWO)) Then
Me.ALLOCATED_X_RAY_No_S = varIWO
Me.DESCRIPTION = varDesc


ALLOCATED_X_RAY_No_s_BeforeUpdate Cancel
End If

the data, is retrieved from tblXrayImport. How would I be able to also check tblXayImportAli, for the same information? As, if the imformation is not in the first table, it will be in the other.

Regards,

Rob

[HansV]

Something like this?

varIWO = DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]")
If IsNull(varIWO) Then
varIWO = DLookup("[Xray No]", "[tblXrayImportAli]", "[BatchNo] = [Batch No]")
End If

[robm]

Thanks Hans. Brilliant.

[HansV]

Another option is

varIWO = Nz(DLookup("[Xray No]", "[tblXrayImport]", "[BatchNo] = [Batch No]"), _
DLookup("[Xray No]", "[tblXrayImportAli]", "[BatchNo] = [Batch No]"))