Long Query Being Broken by Line Breaks (1 Viewer)

[HairyArse]

Hello,

I'm trying to run a long query through VBA code. The query itself works just fine, but when run through VBA is being broken because Access is adding line breaks in the middle of words and thus breaking the query.

For example, it's trying to run:

SELECT * FROM MY_TA
BLE WHERE id = 1 AND pr
ice = "1.00"

instead of:

SELECT * FROM MY_TABLE WHERE id = 1 AND price = "1.00"

Obviously my query is much, much longer. How can I stop this?

 

[Minty]

How long is the query and can you show us the actual code that generates it ?

 

[HairyArse]

The full query is:

SELECT tbl_orders.sales_order_id AS [order], tbl_companies.company_name, tbl_order_details.product_code, tbl_order_details.item_number AS item,  tbl_order_details.quantity AS qty, [tbl_orders].[currency_rate]*[tbl_order_details].[price] AS price_sterling,  Sum(Nz([quantity_delivered],'0')) AS Delivered, [quantity]-[Delivered]+[qty_returned] AS Outstanding,  [outstanding]*([tbl_orders].[currency_rate]*[tbl_order_details].[price]) AS outstanding_value, (([tbl_orders].[currency_rate]*[tbl_order_details].[price])*[quantity])  AS total_order_value, ([outstanding_value]/[total_order_value]) AS percentage_remaining,  tbl_item_types.item_type_name , tbl_secondary_item_type.secondary_item_type_name, tbl_primary_colours.primary_colour_name, tbl_orders.order_date,  tbl_denominations.denomination_name, tbl_order_details.item_complete, tbl_orders.order_cancelled, tbl_order_details.detail_id,  tbl_order_details.Despatch_Date, tbl_orders.proforma, tbl_orders.ccp_id, tbl_order_details.price, tbl_currencies.currency_name,  t
bl_order_details.customer_item_reference, tbl_staff.staff_initials, tbl_orders.customers_ref, tbl_deliveries.entry_complete, Nz([SumOfquantity_returned],0) AS qty_returned  FROM value_of_orders_all_returns RIGHT JOIN (((tbl_currencies INNER JOIN (tbl_denominations RIGHT JOIN (tbl_secondary_item_type INNER JOIN (tbl_primary_colours INNER JOIN (tbl_item_types INNER JOIN (((tbl_delivery_details RIGHT JOIN (tbl_order_details INNER JOIN tbl_orders ON tbl_order_details.sales_order_id =  tbl_orders.sales_order_id) ON tbl_delivery_details.item_id = tbl_order_details.detail_id) INNER JOIN tbl_companies ON tbl_orders.company_id = tbl_companies.company_id) INNER JOIN tbl_products ON tbl_order_details.product_code = tbl_products.product_code)  ON tbl_item_types.item_type_id = tbl_products.item_type_id) ON tbl_primary_colours.primary_colour_id = tbl_products.primary_colour_id) ON tbl_secondary_item_type.secondary_item_type_id = tbl_products.secondary_item_type_id) ON tbl_denominations.denomination_id = tbl_products.min_s
ales_quantity_denom_id)  ON tbl_currencies.currency_id = tbl_orders.sales_order_currency_id) INNER JOIN tbl_staff ON tbl_orders.ccp_id = tbl_staff.staff_id) LEFT JOIN tbl_deliveries ON tbl_delivery_details.delivery_note_id = tbl_deliveries.delivery_note_id) ON value_of_orders_all_returns.sales_order_item_id = tbl_order_details.detail_id  GROUP BY tbl_orders.sales_order_id, tbl_companies.company_name, tbl_order_details.product_code, tbl_order_details.item_number, tbl_order_details.quantity, [tbl_orders].[currency_rate]*[tbl_order_details].[price], tbl_item_types.item_type_name, tbl_secondary_item_type.secondary_item_type_name,  tbl_primary_colours.primary_colour_name, tbl_orders.order_date, tbl_denominations.denomination_name, tbl_order_details.item_complete, tbl_orders.order_cancelled, tbl_order_details.detail_id, tbl_order_details.despatch_date, tbl_orders.proforma, tbl_orders.ccp_id, tbl_order_details.price, tbl_currencies.currency_name,  tbl_order_details.customer_item_reference , tbl_staff.staff_initials
, tbl_orders.customers_ref, tbl_deliveries.entry_complete, Nz([SumOfquantity_returned], 0), tbl_orders.currency_rate, tbl_order_details.price, tbl_orders.ccp_id, tbl_orders.order_complete, tbl_orders.order_entry_complete  HAVING (((tbl_order_details.item_complete) = 0) And ((tbl_orders.order_cancelled) = 0) And ((tbl_deliveries.entry_complete) Is Null Or (tbl_deliveries.entry_complete) = -1) And ((tbl_orders.order_complete) = 0))  AND (tbl_orders.ccp_id = 5) ORDER BY tbl_orders.sales_order_id, tbl_order_details.item_number;

I save the query string to a variable and when I view the variable using the immediate window and paste the contents into a new query it looks like this:

If I repair the broken lines the query works just fine.

 

[HairyArse]

The query is generated by the following code. As you can see I even tried reducing the length of the lines in the str_sql_select part of the string but that makes no difference as they're all concatenated.

Dim str_sql_select As String
Dim str_sql_from As String
Dim str_sql_where As String
Dim str_sql_group_by As String

str_sql_select = "SELECT tbl_orders.sales_order_id AS [order], tbl_companies.company_name, tbl_order_details.product_code, tbl_order_details.item_number AS item, " _
& " tbl_order_details.quantity AS qty, [tbl_orders].[currency_rate]*[tbl_order_details].[price] AS price_sterling, " _
& " Sum(Nz([quantity_delivered],'0')) AS Delivered, [quantity]-[Delivered]+[qty_returned] AS Outstanding, " _
& " [outstanding]*([tbl_orders].[currency_rate]*[tbl_order_details].[price]) AS outstanding_value, (([tbl_orders].[currency_rate]*[tbl_order_details].[price])*[quantity]) " _
& " AS total_order_value, ([outstanding_value]/[total_order_value]) AS percentage_remaining, " _
& " tbl_item_types.item_type_name , tbl_secondary_item_type.secondary_item_type_name, tbl_primary_colours.primary_colour_name, tbl_orders.order_date, " _
& " tbl_denominations.denomination_name, tbl_order_details.item_complete, tbl_orders.order_cancelled, tbl_order_details.detail_id, " _
& " tbl_order_details.Despatch_Date, tbl_orders.proforma, tbl_orders.ccp_id, tbl_order_details.price, tbl_currencies.currency_name, " _
& " tbl_order_details.customer_item_reference, tbl_staff.staff_initials, tbl_orders.customers_ref, tbl_deliveries.entry_complete, Nz([SumOfquantity_returned],0) AS qty_returned "

str_sql_from = " FROM value_of_orders_all_returns RIGHT JOIN (((tbl_currencies INNER JOIN (tbl_denominations RIGHT JOIN (tbl_secondary_item_type INNER JOIN (tbl_primary_colours INNER JOIN (tbl_item_types INNER JOIN (((tbl_delivery_details RIGHT JOIN (tbl_order_details INNER JOIN tbl_orders ON tbl_order_details.sales_order_id = " _
& " tbl_orders.sales_order_id) ON tbl_delivery_details.item_id = tbl_order_details.detail_id) INNER JOIN tbl_companies ON tbl_orders.company_id = tbl_companies.company_id) INNER JOIN tbl_products ON tbl_order_details.product_code = tbl_products.product_code) " _
& " ON tbl_item_types.item_type_id = tbl_products.item_type_id) ON tbl_primary_colours.primary_colour_id = tbl_products.primary_colour_id) ON tbl_secondary_item_type.secondary_item_type_id = tbl_products.secondary_item_type_id) ON tbl_denominations.denomination_id = tbl_products.min_sales_quantity_denom_id) " _
& " ON tbl_currencies.currency_id = tbl_orders.sales_order_currency_id) INNER JOIN tbl_staff ON tbl_orders.ccp_id = tbl_staff.staff_id) LEFT JOIN tbl_deliveries ON tbl_delivery_details.delivery_note_id = tbl_deliveries.delivery_note_id) ON value_of_orders_all_returns.sales_order_item_id = tbl_order_details.detail_id "

str_sql_group_by = " GROUP BY tbl_orders.sales_order_id, tbl_companies.company_name, tbl_order_details.product_code, tbl_order_details.item_number, tbl_order_details.quantity, [tbl_orders].[currency_rate]*[tbl_order_details].[price], tbl_item_types.item_type_name, tbl_secondary_item_type.secondary_item_type_name, " _
& " tbl_primary_colours.primary_colour_name, tbl_orders.order_date, tbl_denominations.denomination_name, tbl_order_details.item_complete, tbl_orders.order_cancelled, tbl_order_details.detail_id, tbl_order_details.despatch_date, tbl_orders.proforma, tbl_orders.ccp_id, tbl_order_details.price, tbl_currencies.currency_name, " _
& " tbl_order_details.customer_item_reference , tbl_staff.staff_initials, tbl_orders.customers_ref, tbl_deliveries.entry_complete, Nz([SumOfquantity_returned], 0), tbl_orders.currency_rate, tbl_order_details.price, tbl_orders.ccp_id, tbl_orders.order_complete, tbl_orders.order_entry_complete "

str_sql_where = " HAVING (((tbl_order_details.item_complete) = 0) And ((tbl_orders.order_cancelled) = 0) And ((tbl_deliveries.entry_complete) Is Null Or (tbl_deliveries.entry_complete) = -1) And ((tbl_orders.order_complete) = 0)) " _
& " AND (tbl_orders.ccp_id = " & str_current_employee & ")" _
& " ORDER BY tbl_orders.sales_order_id, tbl_order_details.item_number; "

str_sql = str_sql_select & str_sql_from & str_sql_group_by & str_sql_where

 

[Minty]

There is a limit to the number of characters the immediate window will return. I've also experienced the same problem trying to paste really long queries into the query designer.

Once the string is concatenated, try saving it to a memo field in a temporary table, then copy and paste that to notepad and see what it looks like without access trying to "present" it.

 

[MarkK]

You can alias your table names to make your queries more readable, and shorten the overall character count like . . .

SELECT LongTableName.Field1, LongTableName.Field2
FROM LongTableName

...is the same as...

SELECT t1.Field1, t1.Field2
FROM LongTableName As t1