Help with query please...checking for data in another table

[jimday1982]

This seems pretty simple, but is giving me trouble - I am just trying to get my query to compare each record to see if [Teledynamics Inventory].[item_number] = [Teledynamics Errors].[sku] and if it is the same, use "ASIN" as the data in the query, and if not, use [CE Template - Teledynamics]![product-id-type]. When I run my query, it comes up with a prompt for me to input a value for [Teledynamics Errors].[sku]...I don't understand why because it's populated with skus...am I missing something? The code I'm using is below:

Expr1: IIf([Teledynamics Inventory].[item_number]=[Teledynamics Errors].[sku],"ASIN",[CE Template - Teledynamics]![product-id-type])

Thanks!

 

[CC214700]

Make sure all the names are correct and you mite have to query your query a couple of time because assess is crap and it gets confused to easily.

 

[jimday1982]

Yes, everything is spelled correctly. I'm not sure what you mean about query my query...if you know of any other way to do this, I'd appreciate the tip.

 

[CC214700]

if you do a qry that makes a calculation or alters a field, access can trip up be a pain in the arse. what i've had to do a lot of times is do a qry to make one calculation, save it then do another qry but instead in inserting the original table you insert the query you've just done and make further calculation on it that way.

explanations aren't always my strong poin so let me know if this doen't make any sense

 

[jimday1982]

Thanks, that does make sense...I've had to do similar things before...but I don't see why that would work and what I'm trying to do wouldn't. Surely there's got to be a way to check to see if data exists in a table. If I could do that, I could wrap this thing up.

 

[jimday1982]

Ok, I finally got it to work using the following:

Expr1: IIF(IsNull(DLookup("[sku]","TeleDynamics Errors","[sku] = '" & [Item_number] & "'")),[product-id-type],"ASIN")

Now my next question is...if the above works fine, why wouldn't this:

standard-product-id: IIf(IsNull(DLookUp("[sku]","TeleDynamics Errors","[sku] = '" & [Item_number] & "'")),[Teledynamics Inventory]![upc],[Teledynamics Errors]![ASIN])

It keeps prompting me for a value for ASIN even though it is spelled correctly and has values in it!