Flag in query doesn't get set correctly

  • Thread starter Thread starter bradjensmith
  • Start date Start date
B

bradjensmith

I have a query that I use to set a flag based on a left join. For some
reason, everyone is getting the LOAD_IND flag set to "Y", even though
their External_ID field is null (i.e. they don't exist on the
SP_PERS_TRNSLTN_T table. Thoughts?

Trns_Tbl = "SELECT in_recs.*, HCDSADM_SP_PERS_TRNSLTN_T.*,
IIf([EXTERNAL_ID] Is Not Null,'N','Y') AS LOAD_IND " & _
"FROM in_recs LEFT JOIN HCDSADM_SP_PERS_TRNSLTN_T ON in_recs.[C.EMPLID]
= HCDSADM_SP_PERS_TRNSLTN_T.EXTERNAL_ID ;"
 
One other thing to add, when I run the same SQL in a query (vs in the
VBA module), the flag is set correctly.
 
Your IIf statement says to turn it to "Y" if EXTERNAL_ID is null. It can be a
null within the table or return a null if there isn't a matching record due
to the left join. Seems to be working as written.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
One other thing to add, when I run the same SQL in a query (vs in the
VBA module), the flag is set correctly.

[email protected] said:
I have a query that I use to set a flag based on a left join. For some
reason, everyone is getting the LOAD_IND flag set to "Y", even though
their External_ID field is null (i.e. they don't exist on the
SP_PERS_TRNSLTN_T table. Thoughts?

Trns_Tbl = "SELECT in_recs.*, HCDSADM_SP_PERS_TRNSLTN_T.*,
IIf([EXTERNAL_ID] Is Not Null,'N','Y') AS LOAD_IND " & _
"FROM in_recs LEFT JOIN HCDSADM_SP_PERS_TRNSLTN_T ON in_recs.[C.EMPLID]
= HCDSADM_SP_PERS_TRNSLTN_T.EXTERNAL_ID ;"
Click to expand...
Click to expand...
 
Jerry - Thank you for the reply. You're right, there was an error in
the VB code that I discovered, the query was working fine.

Hate it when that happens.

Thanks again!

Brad
Jerry said:
Your IIf statement says to turn it to "Y" if EXTERNAL_ID is null. It can be a
null within the table or return a null if there isn't a matching record due
to the left join. Seems to be working as written.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
One other thing to add, when I run the same SQL in a query (vs in the
VBA module), the flag is set correctly.

[email protected] said:
I have a query that I use to set a flag based on a left join. For some
reason, everyone is getting the LOAD_IND flag set to "Y", even though
their External_ID field is null (i.e. they don't exist on the
SP_PERS_TRNSLTN_T table. Thoughts?

Trns_Tbl = "SELECT in_recs.*, HCDSADM_SP_PERS_TRNSLTN_T.*,
IIf([EXTERNAL_ID] Is Not Null,'N','Y') AS LOAD_IND " & _
"FROM in_recs LEFT JOIN HCDSADM_SP_PERS_TRNSLTN_T ON in_recs.[C.EMPLID]
= HCDSADM_SP_PERS_TRNSLTN_T.EXTERNAL_ID ;"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top