Calculated field expression help?

[towners]

Hi all,

I'm brand new to access and wondered if I could get some help creating a calculated field based on results of some logical tests?

I want to create a calculated “Target Install” date field using dates from 3 date fields in a table: “ProvStartDate”, “PIDate” & “Contract Date”.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The logic to populate the “TargetInstallDate” is:<o></o>
<o> </o>
If “ProvStartDate” is not null use that, if “ProvStartDate” is null and “PIDate” is not null use the “PIDate”, if both “ProvStartDate” and “PIDate” are null then use “ContractDate” + 35 days.<o></o>
<o> </o>
Can anyone help me build the expression?<o></o>

Many thanks

Paul

 

[Norie]

Paul

You can try something like this:

InstallDate:Nz([ProvStartDate], Nz([PIDate], DateAdd("d", 35,[Contract Date])))

 

[towners]

Norrie,

That's done the trick. Thank you very much.

Regards

Paul

 

[towners]

Hi Norrie,

Thanks again for the help. I've noticed though that the results are not formatted as date (even though I have selected this in the field properties). This gives me a data mismatch in a follow up query. When I sort the field ascending it sorts as a number. Any advice to what I'm doing wrong?

Thanks again

Paul

 

[Norie]

Paul

How exactly are you using the expression?

Also where are you using it and what's giving the error?

If you have dates in those 3 fields then it should return a date value?

It should be formatted as a date too.

How is it formatted? Just a 5 digit number like 40810.<sup>*

*</sup>That's supposed to be today, might be out a bit though.

 

[towners]

Hi Norrie,

I've created a calculated field within a query (PT_TargetInstallDate) that joins to my "Contracts" table. Then I join the query to another table that contains dates and corresponding weeknumbers, month and year. The date is calculated correctly and displayed as a date...

A way around this may be if access has the similar functionality as excel to seperate out the weeknumber, month and year i.e.:

weeknumber=weeknumber(serialnumber,returntype)
month=text(serialnumber,format)
year=year(serialnumber)

Regards

Paul

 

[Norie]

Paul

I don't understand, have you used the expression I posted?

By the way, Access has most (if not all) of the date functions that Excel has.

 

[towners]

Hi Norrie,

Yes, I cut and pasted it - the calculations work exactly.

Regards

Paul

 

[towners]

Sorted now, have found the Access functionality so no need to create a join to my lookup table.

Thanks for the help Norrie.

Paul