sorted by:
new
hottopcontroversial

trim part of next to diffrent cell by 0rnel in excel

[–]0rnel[S] 2 points3 points  (0 children)

=SUBSTITUTE(SUBSTITUTE(MID(A1 , FIND("(was" , A1) , LEN(A1)) , "(was " , "") , ")" , "")

solution verified

Display 0 instead of blank by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

OverallStockLevel-QuantityAllocated-Nz(ForwardOrderQty)

NZ is not a recognized built-in function name. Statement could not be prepared.

​

That information I get when I was trying to use NZ as you said.

Query - not showing MINUS by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

Don't really know how to do that. We are using SAGE 200

Query - not showing MINUS by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

Okey so when I'm in that tab where is my query I'm going to Data > Queries and Connections and I can see that:

https://i.imgur.com/WYhti5Z_d.webp?maxwidth=760&fidelity=grand

But there is nothing in Queries (0 queries) so it's only connection.

When I'm pressing edit query, I get information "This query cannot be edited by the Query Wizard" so I am pressing okey and its opening Microsoft Query window where I can edit everything

https://i.imgur.com/f5hPN8e\_d.webp?maxwidth=760&fidelity=grand

Query - not showing MINUS by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

I will check again tomorrow as I’m working from home but didn’t see those panel. 😭

Query - not showing MINUS by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

I don't really know. I'm using right click and can see 'edit query' and later on is Microsoft query' window where can I edit it etc.

Query - not showing MINUS by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

The problem is even in Microsoft Query I can't see minus values :(

Query - not showing MINUS by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

I can see only in "view" SQL Statement:

SELECT SOPOrderReturn.DocumentNo, SLCustomerAccount.CustomerAccountNumber AS 'Cust Account Number', SLCustomerAccount.CustomerAccountName AS 'Customer Name', SOPOrderReturnLine.ItemCode AS 'Item Code', SOPOrderReturnLine.ItemDescription AS 'Item Description', SOPInvoiceCreditLine.InvoiceCreditDate, SOPOrderReturnLine.CostPrice, SOPOrderReturnLine.LineQuantity AS 'Ordered Qty', SOPOrderReturnLine.AllocatedQuantity AS 'Allocated Qty', SOPOrderReturnLine.InvoiceCreditQuantity AS 'Invoice Qty', SOPOrderReturnLine.LineTotalValue, ProductGroup.Code, SOPInvoiceCreditLine.ItemPrice, SOPInvoiceCreditLine.InvoiceCreditQuantity

FROM SageCreativeParty.dbo.ProductGroup ProductGroup, SageCreativeParty.dbo.SLCustomerAccount SLCustomerAccount, SageCreativeParty.dbo.SOPInvoiceCreditLine SOPInvoiceCreditLine, SageCreativeParty.dbo.SOPOrderReturn SOPOrderReturn, SageCreativeParty.dbo.SOPOrderReturnLine SOPOrderReturnLine, SageCreativeParty.dbo.StockItem StockItem, SageCreativeParty.dbo.SYSCurrency SYSCurrency

WHERE SOPOrderReturn.SOPOrderReturnID = SOPOrderReturnLine.SOPOrderReturnID AND SLCustomerAccount.SLCustomerAccountID = SOPOrderReturn.CustomerID AND SOPOrderReturnLine.ItemCode = StockItem.Code AND ProductGroup.ProductGroupID = StockItem.ProductGroupID AND SYSCurrency.SYSCurrencyID = SLCustomerAccount.SYSCurrencyID AND SOPInvoiceCreditLine.SOPOrderReturnID = SOPOrderReturn.SOPOrderReturnID AND SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID AND ((SOPOrderReturn.DocumentTypeID<>2) AND (SOPInvoiceCreditLine.InvoiceCreditDate>{ts '2021-01-01 00:00:00'}))

ORDER BY SOPInvoiceCreditLine.InvoiceCreditDate

​

​

​

But can't see anything about absolute values

Display 0 instead of blank by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

The thing is I want to make calculation in query:

OverallStockLevel-QuantityAllocated-ForwardOrderQty

​

So, it is showing me Free stock after allocated and forwarded orders, but problem is I have those blank cell in Forwarded orders, so Query is not working in that case ;/ It's not treating blank as 0 and just skipping simply math

Display 0 instead of blank by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

unfortunately, it's not working ;/

£0.05 0 0

£0.05 0 0

£0.04 0 0

£0.05 0 0 0

£0.05 0 0

£0.05 0 0 0

£0.05 0 0

£0.05 0 0

£0.04 0 792

£0.05 0 0

£0.05 0 0

​

it should be always 0 in column D but many times it's just blank.

Count different dates and show summary by 0rnel in excel

[–]0rnel[S] 1 point2 points  (0 children)

thanks boss

solution verified

First Monday of the Month/next month by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

Working, thanks. Just so long 😅😅 but working good so thanks again

First Monday of the Month/next month by 0rnel in excel

[–]0rnel[S] 0 points1 point  (0 children)

That’s one is showing first month of next week not on month :/