ACCESS formula problems

[Leadparsons]

Hi All,

I have and excel formula that i'm trying to convert to access. In excel I have two date columns and the formula will always enters the later of the two dates. If there is a date missing in either it will not display a date. the formula im using is: =IF(K160="","",IF(L160="","",MAX(K160,L160)))

How do I convert this to access calculated expression ?

 

[Joe4]

Try this:

MaxDate: IIf(IsNull([DateField1]) Or IsNull([DateField2]),"",IIF([DateField1]>[DateField2],[DateField1],[DateField2]))

 

[Leadparsons]

Comes up with an error - The expression you entered has an invalid. (dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or typed parentheses following the Null constant.

The code im using is MaxDate: IIf(IsNull([LOI Received]) Or IsNull([ECR3 Date]),"",IIF([LOI Received] >[ECR3 Date],[LOI Received],[ECR3 Date]))

 

[Leadparsons]

Tried taking out the words Max Date: and now comes up with the error The expression could not be saved because its result type, such as binary or NULL, is not supported by the server.

 

[Joe4]

Where exactly are you trying to place this formula in Access?

 

[Leadparsons]

In a sheet calculated type column

 

[Joe4]

Access doesn't have sheets. The calculation should be in a Calculated Field in a Query, or the Control Source of a Text Box on a Form.

 

[Leadparsons]

Sorry in the table from which you make your queries and forms etc

 

[Joe4]

I assume that you must be using a new version of Access, which allows for calculations on the Table level. Though that is now possible, it really isn't a good practice to get into (as no other database program allows that). Calculations are best done in queries. See: Access Tips: Calculating in Queries

 

[Leadparsons]

Thank you, have inputted in a query and the code works