Search results

I hope I am interpreting your questions correctly in my answers here. 1. The average price is both stored and calculated each time. It is stored in the Average_Cost_Price field and calculated using the 'Previous ACP' and 'ACP' calculated fields in the query. The Average_Cost_Price field is...

There must be an issue somewhere. As shown in the below screenshot, I have taken away some of the data from the Average_Cost_Price column so the SQL only returns the rows where the Product_Price value is £7.52. This means the Average_Cost_Price value should be £7.52 because the average price of...

Thanks, I added the format out of desperation to get the code to work. I have taken the format away but the code isn't doing what I want. It looks like the ORDER BY isn't working or there is something else incorrect. As you can see in the screenshot below, Average_Cost_Price on the highlighted...

Thanks very much for your feedback on this subject, it has been very helpful :) . I'll speak with my managers regarding some of the key points you have raised.

Thanks for your suggestion, I have added ORDER BY to the statement but I'm still not getting the desired results, maybe I haven't added it correctly. My code is now: Dim dbs As DAO.Database Dim rstDeliveries As DAO.Recordset Dim strSQL As String On Error GoTo ErrorHandler Set dbs = CurrentDb...

I see what you are asking but I'm still not 100% sure how to word the answer. I believe ACP is a variable. As for your "Where is it from" question, the only way I know to answer this is that ACP is a calculated field in the Deliveries_Query query. Sorry for making this difficult 😕

I'm a little confused by this as my understanding of AVCO is that the current AVCO is calculated from the cost and quantities of all deliveries of that item, depending on sales and purchase quantities. Doesn't that mean if there is a amendment to a delivery record this could affect the current...

I am still learning code, so I have taken this from https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb243789(v=office.12)?redirectedfrom=MSDN and modified it. I don't fully understand it, hence why I said: and Please can you reword the question :)

strSQL = "SELECT * FROM Deliveries_Query 'Deliveries_Query' is the query in question

ACP is a field in the query, as shown in the screenshot of the query.

There is a chance this scenario never occurs but through testing this AVCO feature it seemed a possibility. Another possibility is if a purchase needs to be retrospectively entered, i.e. if a purchase delivery was not recorded on the day it arrived. Like I say, this might never happen but when...

Thanks very much for this info, I was definitely over complicating it thinking I needed a function. I have been able to use your calculation to get the AVCO via a calculation field in a query. It works great when a new purchase record is added as the last record in the query but not so well when...

Thanks, I definitely want to stay away from storing the AVCO. I have been instructed by management to introduce the average cost for each product in such a way that reduces the requirement of updating values manually. We have a history of our prices being inputted incorrectly and therefore...

Hi all, I'm hoping for some assistance to help me build a function which calculates the average cost price (ACP) of our stocked products. I have had a look online to see if anything already exists but couldn't find anything suitable. I have attached 3 examples taken from an Excel workbook to...

Haha, I was missing something with the code - it was as simple as that! Thanks for your help all the same :)

Hi everyone, I have been using Allen Browne's Inventory Control code for quite some time now and it has been working perfectly. However, there is now a requirement for one of the stocked product's quantity on hand to be displayed as a decimal number. I have changed the data types of the table...

Thanks! And is it possible to have multiple subforms of crosstab queries on this form controlled by the command button? All the subforms would display similar results but with slight changes to the query where condition.

Perfect, thank you for your assistance :) Just a couple of questions regarding this setup: Is it possible to add a Sum totals for each column on the crosstab? Is it possible to add multiple subforms which are controlled by the one command button on the form?

02/11/2021

I've got the where condition working as below but I am still unable to figure out where the date formatting goes in the function, I either get an error or no formatting at all. @arnelgp can you point me in the right direction? "TRANSFORM Max(Customer_Orders_Items.Order_Quantity) " & _ "SELECT...