Create an Access Query using VBA...?

[badddger]

Forgive me if this is very straightforward, but I'm new to the whole area of VBA and programming.

I'd like to know how to create a query (that will appear in the 'Queries' window in Access) to concatenate four values from a table for every row in that table, using VBA behind a command button on a form. :| make sense?

I've searched the internet and have been thoroughly confused with 'QueryDef', 'RecordSet's and the like. I want to pull four fields from a table called 'newTP', then display the concatenated values in a query called 'concatnewTP'.

Can anyone help?!

 

[GenieTR]

Press the Query tab in the database window and select the table you want. Select the fields you want into the columns and the last column you type the expression to concatenated the four fields.

example: exp1:[field1]& &[field2]& &[field3]& &[field4]

Now on the toolbar you will see view icon with a down arrow, click on the down arrow and select SQL view to see the syntax.

you can copy the syntax into your vba modular

hope this helps

 

[DonQuichote]

(Assuming DAO or Access 97 If you want to create a query from VBA, you can create a querydef object:

set qdf=createQueryDef("qryNameInTheDatabaseWindow","some SQL code"

if you leave the first parameter empty, the query will not be appended to the database (temporary query).

Best regards