If statement

[Sham]

Hi,

Is there anyone here who can help me with writing a formula in query to return results.
Basically i have hire and rehire columns and i want my new column to have a formula to give me the most recent hire/rehire date.

Please respond if u can help
thx

 

[KeithG]

IIF([HireDate]>[ReHireDate],[HireDate],[ReHireDate])


the above should return the greater of the two dates

 

[rainman89]

i believe if u search the dmax function you will find your answer

 

[KeithG]

i believe if u search the dmax function you will find your answer

I believe these are two different elements in the same record.

 

[rainman89]

I believe these are two different elements in the same record.

you are correct. i need to slow down and read......

 

[Sham]

thanks Keith, however i get 'data type mismatch in criteria expression' error
i have pasted in your formula (after amending the column headings) into the field column of my query.

not sure where i've gone wrong?!

 

[KeithG]

Sounds like you fields are not of the same data type, are they?

 

[Sham]

its working! thx Keith
now my next q
what is the formula to get the length of service between dates in yy-mm format
i.e 01/01/2006 - 31/05/2007

 

[KeithG]

What do you mean? Number of years?

 

[Sham]

yes i want the results to show in years and months

 

[Ripley]

create two colums called YearPart and MonthPart.

In the year part type:

YearPart1: DatePart("yyyy", [YOUR FIELD NAME])

In the month part type:

MonthPart1: DatePart("m", [YOUR FIELD NAME])

Duplicate this for the other field so you get the year and month part from it, and call the fields YearPart2 and MonthPart2

Then create a new field called YearMonth and type:

YearMonth: "Year: " & ([YearPart1] - [YearPart2]) & " Month: " ([MonthPart1] - [MonthPart2])

That should be about right, just have a play around with it.

 

[Sham]

hi ripley, when i enter my field name it becomes a parameter