Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-24-2013, 12:06 AM   #1
naveensg
Newly Registered User
 
Join Date: Aug 2012
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
naveensg is an unknown quantity at this point
Post Convert Excel function to Access either with VBA or function

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
Attached Files
File Type: xls Book1.xls (30.0 KB, 73 views)

naveensg is offline   Reply With Quote
Old 09-24-2013, 01:03 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Convert Excel function to Access either with VBA or function

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?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 09-24-2013, 01:30 AM   #3
naveensg
Newly Registered User
 
Join Date: Aug 2012
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
naveensg is an unknown quantity at this point
Re: Convert Excel function to Access either with VBA or function

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

naveensg is offline   Reply With Quote
Old 09-24-2013, 01:45 AM   #4
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,408
Thanks: 0
Thanked 801 Times in 788 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Convert Excel function to Access either with VBA or function

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.

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Excel to Access MIN() function help larrynew Queries 29 05-02-2018 10:05 AM
Macro function to convert excel to access and replaces old data with new one ewong Macros 3 09-26-2012 06:00 AM
Excel VBA Worksheet Change function - more than 1 function Ginny2222 Excel 6 08-01-2010 03:40 AM
excel function in access mfuada Modules & VBA 0 02-24-2009 04:37 PM
Use an MS Access function in MS Excel Uncle Gizmo Sample Databases 0 11-29-2008 03:48 PM




All times are GMT -8. The time now is 02:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World