Richard Horne
Member
- Local time
- Today, 16:28
- 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.
Even though the / is in quotes and has spaces around it, it's still being processed as an operator.
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