use concatenated expression in Instr function

[DonLduk]

Hello.

I have been looking and searching but havent found an example for my case so I decided to post here in the hope of help.

I have "table1" which contains a, b, c fields. I have "table2" which contains d,e fields. Im interested in one whole field composed by a and e from tables1 and tables2 respectly, hence, I concatenate those 2 fields.

so far so good,

but then problems come when I want to search the concatenated expression on a table3 which contains fields "ae" and "location", using the Instr so I can have partial records. The objective is see the location of the field "ae" partialed.. Here is what I have:

SELECT table1.a & table2.e AS conc, *
FROM table1, table2 INNER JOIN table3 ON Instr( 'conc' , table3.ae);

Im new to Access and SQL so im learning, im not sure if this is right or VERY wrong. I hope somebody can guide me/help me.


thank you very much in advance.

 

[wazz]

see if this will do it:
- create your concat query and save it;
- create a new query and add the concat query and table3;
- join the concat-field to table3.ac.
experiment with joins. view the sql (view->sql).

 

[DonLduk]

thanks!! I got it working. great ideas there to create different Querys