Help - Formula

FelipeSenna · #1 (**permalink**) 10-13-09, 12:46

Guys, I wonder if I can help make a formula. I asked in my service, but it is not my area ... .. haha ...would be the following:

Exists in the database variables Closing Date (DataEncerramento) and the Opening Date (DataInicio).

He stated that when the closing date was greater than two months of opening date will appear FL.

If the closing date is less than two months after the opening date appeared FI.

Could anyone help me? Thank you very much!

(and sorry by English)

brucevde · #2 (**permalink**) 10-13-09, 13:38

Code:

If DateAdd("m",2, {DataInicio.OpeningDate}) > {DataEncerramento.ClosingDate} Then
   "FI"
Else 
   "FL"

FelipeSenna · #3 (**permalink**) 10-13-09, 14:08

Hi...thanks!

But, the system show this message: "A date-time is required here."

brucevde · #4 (**permalink**) 10-13-09, 14:17

Your database fields are not true Date fields, they are probably String fields that happen to contain date values.

You will need to convert the database fields to Dates before you can use the DateAdd function.

The DateValue function might work for you, assuming the date values are all stored using the same format.

FelipeSenna · #5 (**permalink**) 10-14-09, 07:51

Thanks by the help...

Anyone other sugestion?

Thank very much!

gvee · #6 (**permalink**) 10-14-09, 08:23

What is your database backend? SQL Server, MySQL, Access..?

FelipeSenna · #7 (**permalink**) 10-14-09, 08:53

Access...

thanks

gvee · #8 (**permalink**) 10-14-09, 12:02

I suggest you change your query in access: add an extra column:

Code:

SELECT ...
     , Iif(DateAdd("m",2, OpeningDate) > ClosingDate, "FI", "FL")
FROM   ...

FelipeSenna · #9 (**permalink**) 10-14-09, 12:47

Unfortunately, I can not do that.

This is a report that will be generated by a system developed for the company that I work.

Thanks!

brucevde · #10 (**permalink**) 10-14-09, 15:49

Quote:

Anyone other sugestion?

Did you try

Code:

If DateAdd("m",2, DateValue({DataInicio.OpeningDate})) > DateValue({DataEncerramento.ClosingDate}) Then
   "FI"
Else 
   "FL"

If that doesn't work give us some more information! How are the dates stored in the database? What format?

Internet Services

Web Development

Digital Marketing

Consumer Tech