Search results

I believe that worked :eek:, thanks!

Let's say I have an Orders table (fields: customerID, productID, Date, revenue). Customers can have purchased multiple different products throughout time. How can I query what the most recent product each customer purchased is? THank you! I also have a products table and customer table with...

sub match() Dim whitebook As Workbook Set whitebook = Workbooks("whitespace (aggregated) jun 28.xlsx") For Each store In Workbooks("copy of NCG co-op.xlsx").Worksheets(1).Range("A1:A204") For x = 1 To 26917 If Left(store, 4) = Workbooks("whitespace (aggregated) jun 28.xlsx").Worksheets("existing...

unfortunately the tips in that link does not seem to retain formatting for pivotcharts

I have a pivotchart based on a pivot table that regularly gets updated. Whenever I update the pivot table the chart updates also, but the original colors are changed. Is there a way to retain formatting on pivot charts on refresh? Thanks!!

I have a table with a column ("A") that returns either a string or "" depending on whether another column is empty or not. I want to create a pivot table that counts the non "" cells in column A but the P table also counts the "" cells as well. Is there a way to get the pivot table to only...

what would the Instr + Mid function look like to get rid of a space in [f2]? if some records in [f2] didn't have spaces, would it still return the record?

The calculated field i am trying to add is part of a table and not a form, could that be why the proposed solutions don't work? When I try to revert back to datasheet view after entering the expression, the =[f1] & Replace(Nz([f2])," ","") reverts back to [f1]&[f2]

for some reason Access removes the Replace function when I try to return to datasheet view from Design View

Hi, I'm trying to create a new field based on two existing fields [f1], [f2]. [f2] are postal codes that sometimes has extra spaces between characters. How can I concatenate [f1] and [f2] where [f2] has no spaces? Thank you!

It seems to be working perfectly for all rows now, thanks NBVC. This website has been instrumental to my employment:eek:

On further inspection, the formula only seems to count the dates properly up to row 841. In the workbook attached, notice that on for A841 (Jun 6 2016), it counts 6 days in column A that precede Jun 6, 2016 within 8 days (May 29-Jun 6). Immediately in the next row A842 (Jun 6 2016), it then...

it's working now:banghead:

TRANSFORM Sum(Orders.Revenue) AS SumOfRevenue SELECT Stores.StoreName FROM Stores INNER JOIN Orders ON Stores.StoreName = Orders.Store WHERE (((Format([OrderMonth],"yyyy-mm"))>#1/1/2016#)) GROUP BY Stores.StoreName PIVOT Format([OrderMonth],"yyyy-mm"); The field is 'Date/Time', even without...

G'day ! In query design, whenever I try to pull specific data from a Date field under 'Criteria' (i.e. >#2016-01-01#, 'after January 1, 2016'), it seems to simply ignore my date criteria and return data for the entire range of dates in my table. What am I doing wrong?

with a ton of edits, i managed to figure it out using your code Arnelgp. Thank you!

since the records is comprised of data from different vendors, there are some unique productIDs for the same type and flavor i.e. Mint Lime vs MintLime

I have an Orders table and a Products table. Orders has the field StoreName, ProductID, Revenue, and Date. Product has ProductID, Type and Flavor. Type can be 'mint' or 'gum', flavor can be 'cherry', 'lime', 'peppermint'. Each Type of product has each of the three flavors. How can I write a...

Hi NBVC, I took the formula and used it as an array, but it is not returning the right output for some reason. I'd like for it to count the date and the number of preceding days for up to 3 preceding days for each respective date in Column A. I attached a screen capture of my workbook. The...

Can someone help me modify the formula proposed in the last solution of this link: http://stackoverflow.com/questions/27021006/countif-unique-dates-between-a-range...