SQL Statement

[mattc66 via AccessMonster.com]

I need some help with how to format an SQL statement in VB. I copied it from
a Query, but its long and I need help with how to handle the multiple lines.
I would guess it doesn't need to reference the main table over and and over.
Can anyone help?

strSQL = "SELECT ICSTOCK_C1.ITEM, ICSTOCK_C1.DESCRIPTION, ICSTOCK_C1.AP_CODE,
ICSTOCK_C1.AR_CODE , ICSTOCK_C1.PLANNER, ICSTOCK_C1.BUYER, ICSTOCK_C1.
CLASS_GROUP, ICSTOCK_C1.COM_CODE, ICSTOCK_C1.DRAWING, ICSTOCK_C1.ITEM_TYPE,
ICSTOCK_C1.P_LOC, ICSTOCK_C1.P_WHSE, ICSTOCK_C1.MATRIX_CODE, ICSTOCK_C1.
PROD_FAM, ICSTOCK_C1.REVISION

FROM ICSTOCK_C1

GROUP BY ICSTOCK_C1.ITEM, ICSTOCK_C1.DESCRIPTION, ICSTOCK_C1.AP_CODE,
ICSTOCK_C1.AR_CODE, ICSTOCK_C1.PLANNER, ICSTOCK_C1.BUYER, ICSTOCK_C1.
CLASS_GROUP,ICSTOCK_C1.COM_CODE, ICSTOCK_C1.DRAWING, ICSTOCK_C1.ITEM_TYPE,
ICSTOCK_C1.P_LOC, ICSTOCK_C1.P_WHSE, ICSTOCK_C1.MATRIX_CODE, ICSTOCK_C1.
PROD_FAM, ICSTOCK_C1.REVISION
HAVING (((ICSTOCK_C1.AP_CODE)=stCode));"

 

[Beetle]

Liberal use of the continuation (_) and ampersand (&) characters.
Below is an example. For this example I have assumed that stCode
is a string value that comes form either a variable or form control, etc.
in which case it would need to be outside the quotes;

strSQL = "SELECT ITEM, DESCRIPTION, AP_CODE, AR_CODE," _
& " PLANNER, BUYER, CLASS_GROUP, COM_CODE, DRAWING," _
& " ITEM_TYPE, P_LOC, P_WHSE, MATRIX_CODE, PROD_FAM," _
& " Revision FROM ICSTOCK_C1 GROUP BY ITEM," _
& " DESCRIPTION, AP_CODE, AR_CODE, PLANNER," _
& " BUYER, CLASS_GROUP, COM_CODE, DRAWING, ITEM_TYPE," _
& " P_LOC, P_WHSE, MATRIX_CODE, PROD_FAM ," _
& " Revision HAVING ICSTOCK_C1.AP_CODE = """ & stCode & """;"

 

[mattc66 via AccessMonster.com]

You assumed correct. So I would then run the SQL statement as follows.

DoCmd.RunSQL strSQL

However I am not sure I will get what I want. I want to display the data from
the statement. Can I display it using an SQL statement?

Liberal use of the continuation (_) and ampersand (&) characters.
Below is an example. For this example I have assumed that stCode
is a string value that comes form either a variable or form control, etc.
in which case it would need to be outside the quotes;

strSQL = "SELECT ITEM, DESCRIPTION, AP_CODE, AR_CODE," _
& " PLANNER, BUYER, CLASS_GROUP, COM_CODE, DRAWING," _
& " ITEM_TYPE, P_LOC, P_WHSE, MATRIX_CODE, PROD_FAM," _
& " Revision FROM ICSTOCK_C1 GROUP BY ITEM," _
& " DESCRIPTION, AP_CODE, AR_CODE, PLANNER," _
& " BUYER, CLASS_GROUP, COM_CODE, DRAWING, ITEM_TYPE," _
& " P_LOC, P_WHSE, MATRIX_CODE, PROD_FAM ," _
& " Revision HAVING ICSTOCK_C1.AP_CODE = """ & stCode & """;"

I need some help with how to format an SQL statement in VB. I copied it from
a Query, but its long and I need help with how to handle the multiple lines.

[quoted text clipped - 15 lines]

PROD_FAM, ICSTOCK_C1.REVISION
HAVING (((ICSTOCK_C1.AP_CODE)=stCode));"

 

[fredg]

You assumed correct. So I would then run the SQL statement as follows.

DoCmd.RunSQL strSQL

However I am not sure I will get what I want. I want to display the data from
the statement. Can I display it using an SQL statement?

Liberal use of the continuation (_) and ampersand (&) characters.
Below is an example. For this example I have assumed that stCode
is a string value that comes form either a variable or form control, etc.
in which case it would need to be outside the quotes;

strSQL = "SELECT ITEM, DESCRIPTION, AP_CODE, AR_CODE," _
& " PLANNER, BUYER, CLASS_GROUP, COM_CODE, DRAWING," _
& " ITEM_TYPE, P_LOC, P_WHSE, MATRIX_CODE, PROD_FAM," _
& " Revision FROM ICSTOCK_C1 GROUP BY ITEM," _
& " DESCRIPTION, AP_CODE, AR_CODE, PLANNER," _
& " BUYER, CLASS_GROUP, COM_CODE, DRAWING, ITEM_TYPE," _
& " P_LOC, P_WHSE, MATRIX_CODE, PROD_FAM ," _
& " Revision HAVING ICSTOCK_C1.AP_CODE = """ & stCode & """;"

I need some help with how to format an SQL statement in VB. I copied it from
a Query, but its long and I need help with how to handle the multiple lines.

[quoted text clipped - 15 lines]

PROD_FAM, ICSTOCK_C1.REVISION
HAVING (((ICSTOCK_C1.AP_CODE)=stCode));"

See VBA help regarding RunSQL.
You can only run Action queries, i.e. Delete, Update, Append, etc.,
using RunSQL, not a Select query.

Just create a regular query, then, using code, open it.
DoCmd.OpenQuery, "QueryName"

 

[mattc66 via AccessMonster.com]

I would I run my query based on a user variable?

You assumed correct. So I would then run the SQL statement as follows.

[quoted text clipped - 22 lines]
See VBA help regarding RunSQL.
You can only run Action queries, i.e. Delete, Update, Append, etc.,
using RunSQL, not a Select query.

Just create a regular query, then, using code, open it.
DoCmd.OpenQuery, "QueryName"