Solved

Convert Excel Formula to Access =FLOOR(SUM(NOW()-P2),1), =Current(I2:N2), =age(O2,G2, J2:M2), =SUM(H19+K19+M19)

Posted on 2006-07-20
5
400 Views
Last Modified: 2008-03-06
Hi I inherited this spreadsheet and I need to have the same functionality in MS Access.  It is used to determine vehicle pricing and dates in inventory.  The pricing person quit yesterday and I know nothing about formulas but here they are:

Age
=FLOOR(SUM(NOW()-P2),1)

Current
=Current(I2:N2)

Status
=age(O2,G2, J2:M2)

Basic Sum
=SUM(H19+K19+M19)

Column Headings are A through X
Franchise      Stock      Desc      Certified      Color      Mileage      S.L.      Cost      Initial      1st      2nd      3rd      4th      Last      Age      Entered      Leaving      Current      Status      Blue Book      AcutalDesc      Year      Sold      Payment
Toyota      11223648      01 CENTURY            SILVER      62      60      $5,673      $7,233                                    48      6/2/06      1-Aug      #NAME?      #NAME?      $7,925      BUICK CENTURY      2001            148-158
Volvo      12036943      01 S60            ASH GOLD      44      60      $14,161      $16,780      $16,550      $16,056                        308      9/15/05      14-Nov      #NAME?      #NAME?            VOLVO S60      2001            
Ford      12314462      01 C2500            RED      86      60      $10,195      $13,826                                    23      6/27/06      26-Aug      #NAME?      #NAME?      $15,310      CHEVY C2500      2001            297-307

I'm trying to look this up and figure it out myself but if ANYONE CAN HELP PLEASE!
0
Comment
Question by:lorijgreen
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 17147485
Hi,
I believe the equivalents are:
1. =Int(Now-[Entered])
2. Current is not a built-in worksheet function - there must be some code in your workbook.
3. The same applies for Age - not an Excel function.
4. =[Cost]+[2nd]+[4th]
HTH
Rory
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 17147509
PS:
I suspect that Current would be something like:
=Nz([Last],Nz([4th],Nz([3rd],Nz([2nd],Nz([1st],Nz([Initial],0))))))
Regards,
Rory
0
 

Author Comment

by:lorijgreen
ID: 17148388
Hi Rory,
I have to go kick my user out of the database so I can try =Nz([Last],Nz([4th],Nz([3rd],Nz([2nd],Nz([1st],Nz([Initial],0))))))
out.
From my very limited understanding all I have to do is create  new module with that correct?
I will also check the spreadsheet to see where he has hidden any other coding.
I am so kicking myself for not taking any access classes.  I feel so stupid.
thank you so much.
lori
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 17148771
Lori,
No, you would just enter that in a query grid. Nz is a built-in function so no code required.
HTH
Rory
0
 

Author Comment

by:lorijgreen
ID: 17203901
GOT IT! THANKS SO MUCH.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now