Concatenate and LOOKUP help…

[ludakrishna420]

Here’s my goal.
1.) I have one column and want to add a 633 in front of if.
a. Expr3: "633 " & [Grp] & ""
b. This only shows 633 and asks for an entry for the GRP? Why? I want the format to be 633 xxx. If I do add, lets say, 123 in the popup, all of the columns are 633 123.
2.) After combining the following, I want to take that number, and look it up in another query. If the number matches a number in the other query, than I want it to enter the number in column 1. If not, I want it write no number. I have gotten this part to work using an IIF function but I just wanted to let you know what I am after.

 

[Bilbo_Baggins_Esq]

Well, the first problem is that the query is not seeing the [Grp] column. Whatever table or query contains it, needs to be included in the query.
I think you may also need to make it this way:

a. Exp3: "633" & [table_name]![Grp]

I'll be back in a minute to look at the last part.

 

[Bilbo_Baggins_Esq]

As for the second part of your request:
There are two ways you can do this.
Both methods are through a query.
If you want the query results to actually show the unmatched values, then do a LEFT JOIN from the first query to your other record source. That will allow ALL RECORDS from the first query to be visible, and only those records from the other source where they match.

On the other hand, if you do not even want the unmatched records to be visible at all, do the same query, except make it a regualr JOIN instead of a left Join.

 

[ludakrishna420]

Thanks for the help, i got the 1st part done and you were right, i just needed to reference the group, however, the second part stopped to work now.

This is what i have entered. This query lookes into multiple tables and then gathers the data that is incomplete and presetns it to you. The expression below is supposed to match your code, with a code in another table, then spit out the buyer you are buying from.

IIf ( [AssemblyKit_Linked]![AssyKitDesc]= [Query1]![Expr4] , [AssemblyKit_Linked]![Supplier] , "No Data")

This asks me for the imput for the assembly kit and returns No Data for everything. Any Ideas?

 

[Bilbo_Baggins_Esq]

hmmmm, in order for the referenced IIF statement to work, other columns would have to be exactly joined, and that will exclude null fields.

If the first part works, you'll likely have to rebuild the second part.
If you're working with several tables as you mention, I would start off small and add to it as you complete small pieces of it. That way you know where you go wrong when it stops returning the desired result. Without seeing the tables and columns, i'd be in the dark as to your exact SQL.

 

[ludakrishna420]

hmmmm, in order for the referenced IIF statement to work, other columns would have to be exactly joined, and that will exclude null fields.

If the first part works, you'll likely have to rebuild the second part.
If you're working with several tables as you mention, I would start off small and add to it as you complete small pieces of it. That way you know where you go wrong when it stops returning the desired result. Without seeing the tables and columns, i'd be in the dark as to your exact SQL.

If i could, it would put up the tables but they are proprietary info. Bust essentially, i want to run a vlookup in access. I'll try your suggestion. Thanks.

 

[ludakrishna420]

Got it to work.