Convert Excel function to Access either with VBA or function (1 Viewer)

naveensg

New member
Local time
Today, 02:25
Joined
Aug 3, 2012
Messages
7
Hello All,

I need a function or vba code count the serial number which are re-appeared within 30 days.

I have attached an excel sheet with 2 resolution one using Countifs function other one using arrays. I am not able upload the xlsx version hence i have uploaded xls version.

But I want use this MS Access, can you please help with conversion.

Advance Thanks...NG
 

Attachments

  • Book1.xls
    30 KB · Views: 119

pr2-eugin

Super Moderator
Local time
Today, 09:25
Joined
Nov 30, 2011
Messages
8,494
Hello naveensg, The sample you gave has the count value for L3AAB8Y - 350M, as 1, 2, 4 and 4. Should it not be 1, 2, 3 and 4? If you need it just as
Code:
Date        Combine            Month    CountOfField
6-Aug-13    L3AAB8Y - 350M    Aug-13    4
If thats the case, have you considered using GROUP BY?
 

naveensg

New member
Local time
Today, 02:25
Joined
Aug 3, 2012
Messages
7
pr2-eugin,

Thanks for looking into my request, If serial number re-appeared within 30 days irrespectve whether its same month or not?, we need consider the second one as duplicate.

Ex: L3AAB8Y - 350M, when it first appears it will be called origianal, the later appears within 30 days from first one will considered as Duplicate.

What i need is just replication code i used in Access either through function or by VBA code.

Regards...NG
 

namliam

The Mailman - AWF VIP
Local time
Today, 10:25
Joined
Aug 11, 2003
Messages
11,696
Something like that you do not resolve in access using a function or VBA, you use a query to do this.

Some query like:
Code:
SELECT T1.Combine, T2.Datum
FROM YourTable  T1
Left JOIN YourTable AS T2 ON T1.Combine = T2.Combine
WHERE T1.Datum<DateAdd("m",1,[T2].[datum])
FYI, never use DATE as a column name it is a reserved word, which is why I "renamed" it to datum.

This query will return all the dups which you then need to count to get to where you want to be.
 

Users who are viewing this thread

Top Bottom