sorting on a dlookup field in a query

[Ptero]

I have a query which uses dlookup to looks up a value for one of the fields
in another table if it would otherwise be null. It works fine for displaying
the data, but when I try to sort or filter on the dlookup field I get the
following error:

The expression you entered as a query parameter produced this error: 'The
object doesn't contain the automation object 'Int."

For some reason it repeats this message twice before complying with the
filter or sort.

The query is:

SELECT T_Seg_OBM.SO_OBM_Code,
IIf(IsNull([SG_Most_Popular_Name]),DLookUp("[SA_Account_Name]","T_Details_Account","[SA_Account_Number]
= " & Left([SO_OBM_Code],Len([SO_OBM_Code])-5)),[SG_Most_Popular_Name]) AS
[Most Popular Name]
FROM T_Seg_OBM LEFT JOIN T_Details_GroupID ON T_Seg_OBM.SO_OBM_Code =
T_Details_GroupID.SG_OBM_Code;

The lookup can fail and produce a null result if the value being looked up
is not present.

If anyone has any suggestions it will be much appreciated.

Many thanks

Ptero

 

[Ptero]

Resolved it - a data error!

 

[louisjohnphillips]

Resolved it  - a data error!

I have a query which uses dlookup to looks up a value for one of the fields
in another table if it would otherwise be null. It  works fine for displaying
the data, but when I try to sort or filter on the dlookup field I get the
following error:

The expression you entered as a query parameter produced this error: 'The
object doesn't contain the automation object 'Int."

For some reason it repeats this message twice before complying with the
filter or sort.

The query is:

SELECT T_Seg_OBM.SO_OBM_Code,
IIf(IsNull([SG_Most_Popular_Name]),DLookUp("[SA_Account_Name]","T_Details_A­ccount","[SA_Account_Number]
= " & Left([SO_OBM_Code],Len([SO_OBM_Code])-5)),[SG_Most_Popular_Name]) AS
[Most Popular Name]
FROM T_Seg_OBM LEFT JOIN T_Details_GroupID ON T_Seg_OBM.SO_OBM_Code =
T_Details_GroupID.SG_OBM_Code;

The lookup can fail and produce a null result if the value being lookedup
is not present.

If anyone has any suggestions it will be much appreciated.

Many thanks

Ptero- Hide quoted text -

- Show quoted text -

Even though this problem was isolated to a data error, consider using
this method.

The query should project at least one row for every row in T_Seg_OBM.
It will join those rows with the matching rows in T_Details_GroupID.
Some may not join on A.SO_OMB_Code = B.SG_OBM_Code. Those that do
not join A to B will join A to C. The "Nz" function call says if
B.SG_Most_Popular_Name is null, substitute B.SA_Account_Name value.
An error will occur if the "Left" function is called with an
A.SO_OBM_Code shorter than six characters. Therefore, those rows are
excluded so that the second argument to Left is a positive integer.

I am using table alias names because these tablename are so long and
cumbersome.


SELECT A.SO_OBM_CODE,
Nz( B.SG_Most_Popular_Name, C.SA_Account_Name ) as [Most
Popular Name]
FROM ( T_Seg_OBM as A
LEFT JOIN T_Details_GroupID as B
ON A.SO_OBM_Code = B.SG_OBM_Code )
LEFT JOIN T_Details_A-ccount as C
ON A.SO_OBM_Code = Left( C.[SA_Account_Number],
Len( A.SO_OBM_Code ) - 5 )
WHERE Len( A.SO_OBM_Code ) > 6;