Solved Building a query in code - escaped characters being process as operators (1 Viewer)

Richard Horne

Member
Local time
Today, 02:33
Joined
Oct 15, 2020
Messages
55
Morning everyone.

I have a complex query that's being generated by code in VBA. It contains various fields that are supposed to be output to show progress, for example:

Despatched: 50/50
Delivered: 50/50
Approved: 40/50

The problem is, and even though I'm escaping the characters, the slashes are being processed as divide by operators and instead of outputting 40/50 I'm getting 0.8.

The full query is massive and I will post it at the bottom of this post but essentially I'm just doing the following and can't understand why it's not working.

Code:
"Nz([sales_order_status_inbound_consignments_despatched].[SumOfquantity],0)" & " / " & "[sales_order_status_purchase_orders].[purchase_quantity] AS despatch_from_supplier"

Even though the / is in quotes and has spaces around it, it's still being processed as an operator.

Code:
'Set our main query which includes all of the additional WHERE clauses generated above
Dim str_base_qry As String

str_base_qry = "SELECT sales_order_status_sales_orders.sales_order_id, sales_order_status_sales_orders.company_id, sales_order_status_sales_orders.company_name, sales_order_status_sales_orders.item_number, sales_order_status_sales_orders.nsn, sales_order_status_sales_orders.tl_number,  " _
& " sales_order_status_sales_orders.product_code, sales_order_status_sales_orders.item_type_name, sales_order_status_sales_orders.secondary_item_type_name, sales_order_status_sales_orders.design, sales_order_status_sales_orders.sort_colour, sales_order_status_sales_orders.width, " _
& " sales_order_status_sales_orders.width_denomination, sales_order_status_sales_orders.contract_id, sales_order_status_sales_orders.customers_ref, sales_order_status_sales_orders.despatch_date, sales_order_status_sales_orders.promised_date, sales_order_status_sales_orders.quantity, " _
& " sales_order_status_sales_orders.quantity_remaining, sales_order_status_sales_orders.contract_reference, sales_order_status_sales_orders.sales_order_detail_id, sales_order_status_sales_orders.order_complete, sales_order_status_cws.LastOfapproval_submission_date,  " _
& " sales_order_status_cws.LastOfsample_approved, sales_order_status_cws.LastOfapproval_date, sales_order_status_cws.LastOfsample_rejected, sales_order_status_cws.LastOfsample_comments, sales_order_status_cws.LastOfsample_rejected_date, sales_order_status_cws.CWS_Status,  " _
& " sales_order_status_sales_orders.demand_comment, sales_order_status_purchase_orders.purchase_order_id, sales_order_status_purchase_orders.purchase_quantity, sales_order_status_purchase_orders.company_name AS supplier, sales_order_status_purchase_orders.order_date AS po_date,  " _
& " sales_order_status_purchase_orders.revised_delivery_date AS po_revised_date, sales_order_status_purchase_orders.quantity_remaining AS po_qty_remaining, Nz([sales_order_status_po_mark_offs].[SumOfqty_approved],0)" & " / " & "[sales_order_status_purchase_orders].[purchase_quantity] AS total_inspected,  " _
& " sales_order_status_po_mark_offs.SumOfqty_rejected, sales_order_status_purchase_orders.Purchase_Order_Detail_ID, sales_order_status_sales_orders.promised_date, sales_order_status_purchase_orders.revised_delivery_date, sales_order_status_sales_orders.customer_item_reference,  " _
& " Nz([sales_order_status_inbound_consignments_despatched].[SumOfquantity],0)" & " / " & "[sales_order_status_purchase_orders].[purchase_quantity] AS despatch_from_supplier, Nz([sales_order_status_po_mark_offs].[SumOfdel_qty],0)" & " / " & "[sales_order_status_purchase_orders].[purchase_quantity] AS arrived_at_ww " _
& " FROM sales_order_status_inbound_consignments_despatched RIGHT JOIN (sales_order_status_po_mark_offs RIGHT JOIN (sales_order_status_purchase_orders RIGHT JOIN (sales_order_status_cws RIGHT JOIN sales_order_status_sales_orders ON (sales_order_status_cws.contract_id =  " _
& " sales_order_status_sales_orders.contract_id) AND (sales_order_status_cws.product_code = sales_order_status_sales_orders.product_code)) ON sales_order_status_purchase_orders.sales_order_detail_id = sales_order_status_sales_orders.sales_order_detail_id) ON  " _
& " sales_order_status_po_mark_offs.item_id = sales_order_status_purchase_orders.Purchase_Order_Detail_ID) ON sales_order_status_inbound_consignments_despatched.purchase_order_detail_id = sales_order_status_purchase_orders.Purchase_Order_Detail_ID " _
& " WHERE tbl_orders.sales_order_id > 0 AND tbl_orders.order_complete = 0 " & str_customer_qry & str_contract_qry & str_supplier_qry & str_sales_order_qry & str_purchase_order_qry & str_contract_item_qry & str_tl_number_qry & str_status_qry _
& " ORDER by tbl_orders.sales_order_id ASC, tbl_order_details.item_number ASC"

Me.RecordSource = str_base_qry
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:33
Joined
Oct 29, 2018
Messages
21,358
Hi. Try adding single quotes around them as well. For example:
Code:
... & "' / '" & ...
Just a thought...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:33
Joined
May 7, 2009
Messages
19,169
try:

"Nz([sales_order_status_inbound_consignments_despatched].[SumOfquantity],0) & ' / ' & [sales_order_status_purchase_orders].[purchase_quantity] AS despatch_from_supplier"
 

Richard Horne

Member
Local time
Today, 02:33
Joined
Oct 15, 2020
Messages
55
try:

"Nz([sales_order_status_inbound_consignments_despatched].[SumOfquantity],0) & ' / ' & [sales_order_status_purchase_orders].[purchase_quantity] AS despatch_from_supplier"

That solved it, thank you. :D

It makes little sense to me that it should have worked, but somehow it did.
 

Users who are viewing this thread

Top Bottom