BETWEEN DATES

[Bronson]

hi asked this question before then had to go on a trip so i will start over.
i have this equation in excell but need it to work in access.

=IF(COUNT(C3,J3)=2,DAYS360(C3,J3),"")

the fields in access are in the table
c3=DOS
j3=Paid Date

second equation is:

=IF(COUNT(D3,J3)=2,DAYS360(D3,J3),"")

the fields in access are in the table
c3=Briefed Datej3=Paid Date

the third equation is:

=MIN(IF(W3:X3>0,W3:X3))

w3=first equation
x3=second equation

i need to put this on the table in design view

 

[Bronson]

Okay, could you help me put this in a query.

Thank you

 

[KARL DEWEY]

Post your table structure and we will see what can be done.

 

[KARL DEWEY]

Access tables store data but not formulas. This would need to be processed
in a set of queries.

 

[Bronson]

what exactly do you mean table structure. I put the excell formula and the
table structure is DOS....Paid Date.....Briefed Date as stated below.

 

[KARL DEWEY]

This is an Access newsgroup, a relational database. This is not Excel. I
suggest you post to an Excel nrewsgroup.

 

[Bill Johnson]

Karl,

I'm trying to perform something very similar to what Bronson is asking.
How can you perform the same function in an Access Query that Bronson
currently has Excel doing. Is there an equivilent method in Access to get
the DAYS360 function to work?

 

[KARL DEWEY]

I do not know. What are you trying to do? Can you use DateDiff?

 

[Bill Johnson]

Karl,

I've tried the DateDiff function, it works (just like it's supposed to) to
calculate the number of days between two given dates etc.... the Days360
function is used in accounting to do depreciation calculations. It
calculates the difference between dates using a year calendar with twelve 30
day months, (thus 360 days). I don't know why they do it, but that's why
the function is built into Excel. Since Access throws an error when I try
using the function, either it's not there at all, or is used differently than
in Excel.

 

[KARL DEWEY]

Sorry, I do not know where you go from here.

Karl,

I've tried the DateDiff function, it works (just like it's supposed to) to
calculate the number of days between two given dates etc.... the Days360
function is used in accounting to do depreciation calculations. It
calculates the difference between dates using a year calendar with twelve 30
day months, (thus 360 days). I don't know why they do it, but that's why
the function is built into Excel. Since Access throws an error when I try
using the function, either it's not there at all, or is used differently than
in Excel.

 

[Tim Ferguson]

Sorry, I do not know where you go from here.

Set a reference to an Excel library in the VBA References then access the
function via the WorksheetFunction method vis:

? worksheetfunction.Days360( #2000-02-11#, date())
2248


To use this in a form or a report, you'll need to create a VBA wrapper so
that Access can find it:

Public Function Days360Wrapper(MyDate As Date) As Long

Days360Wrapper = WorksheetFunction.Days360(MyDate, Date, True)

End Function

and then put this in the controlsource of a text box

=Days360Wrapper(someDate)

Hope that helps


Tim F