How to return date format in queries

[khwaja]

I have a column of data, which shows dates, and where there were no dates, I asked to return a null value . I used the following expression.

LeaseNotice: IIf(IsNull([OptionNotice]),Null,IIf([OptionNotice]=0,DateAdd("m",-8,[LeaseExpiry]),DateAdd("m",-[OptionNotice]-2,[LeaseExpiry])))

However, I noticed that the returned vales though looked like dates, these were not quite dates, as I was unable to format the values in medium date in my report. The question is when evaluating a column and returning the result in a column which is meant to hold dates, what should be returned in cases where there was a null value in the original column? I tried using zero but this returned 0:00:00 which is interpreted and read as 30 Dec 99.

Any insights please?

 

[BoxHead]

What's returned if you don't check for nulls? Would it be possible to set the criteria to Not Null?


John

Use what you have,
Learn what you can,
Create what you need.

 

[khwaja]

Thanks John. It returns #error if I don't apply the Null condition. I am not too sure if I can use Not Null as part of IIF condition unless you are meaning to restrict the column I am applyingthe test to be restricted to only Not Null value. This will not be acceptable as my report needs to have all records regardless of the condition they meet or not.

Cheers

 

[BoxHead]

khwaja,

Restricting the column is exactly what I meant, but if that won't work let's try some other things.

How about:

LeaseNotice: IIf([u]nz([/u][OptionNotice][u])[/u]=0,DateAdd("m",-8,[LeaseExpiry]),DateAdd("m",-[OptionNotice]-2,[LeaseExpiry])))

or

LeaseNotice: IIf(IsNull([OptionNotice]),[u]""[/u],IIf([OptionNotice]=0,DateAdd("m",-8,[LeaseExpiry]),DateAdd("m",-[OptionNotice]-2,[LeaseExpiry])))

These are the easiest things to try with what you already have, and you probably have already tried them.

Tell me, what kind of values are possible in OptionNotice. I had originally assumed it was a yes/no field with no check (null) as an option. Now I'm not so certain. If you're still having difficulty, could you write a sentence explaining exactly what value should be in LeaseNotice depending upon what condition.


Let me know,

John

Use what you have,
Learn what you can,
Create what you need.

 

[khwaja]

Thanks John. I tried using both expressions but there was an invalid text error associated with nz, so I could not get this to work. But I guess if I familiarise you with the field types and what I am trying to achieve, you might be able to have a better understanding.

This relates to maintaining lease info of properties and there is a requirement to know when the lease is expiring. However, since this decision is driven by options you can exercise, you need to take into account whether there are any options available. By options I mean another term of lease usually expressed as 1X5 which means you can re lease once for an additional 5 years. So to work out lease expiry for a property which is expiring on 23/11/2002 and if I can extend it once for 5 years more, my new lease expiry would be 23/11/2007. However, before this simple calculation takes place, I need to check if there is a current lease term available to make sure I am applying test to a property, which is currently leased. If this is true, then total number of years (options) is added to the end date to give me a new expiry date. In cases where LeaseTerm is blank, I would like this to return null. My calculation is as under:

Fields
Lease termTo (Date) 23-11-07
LeaseTerm (Number) 10
Options2 (Number) 5

LeaseExpiry: IIf(IsNull([LeaseTerm]),Null,(DateAdd("yyyy",[Options2],([LeaseTermTo]))))

I am able to calculate accurately but the resulting value is probably text and not date, as you cannot format it as a medium date, for example. Also there are #errors where condition was false.

Now going back to original problem, you will notice it refers to Lease Expiry as one of the condition to generate an alert date when someone should start talking to landlord. So I would like to know if there is certain notice period required, then apply that to lease expiry to give me an alert date, otherwise if there is no option notice available or there exists a zero, give me a null. Again resulting value should be in date format. You will agree that in real life you don’t always have full data and one needs to take into account the missing values as well as zeros.

I guess if we sort out lease expiry calculation, we might be able to fix lease notice too.

Sorry for the length of reply but I hope this clarifies.

Cheers

 

[khwaja]

John

Did you have a chance to see this?

Cheers

 

[BoxHead]

I'm sorry khwaja, somehow I had missed your reply. Thanks for the wake-up call.

First of all, in my previous post I had tried to use underlines for the text I was adding to your code. However, I had also applied the 'code' format to that part of the post and because of that, the tags and were included in the code I suggested. If you had copied and pasted my code with those extra characters, it may explain the text error you received. You can try that again without the tags.

I'll print this up and take it to work with me today to see if I can find something else.


Sorry for the delay,


John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Khwaja,

Looking at the last post, I realize that when I wanted to underline text, I got the

'[u]' and '[/u]' that screwed up the code.

When I wanted to explain that in the last post I ended up underlining the word 'and'.

Oy!

Hopefully, this clarifies. Never knew a typo to require so much correction.

Another thing, if you haven't already, check out the IsDate function in Access Help. This may be of use to you in some of your calculations.

John

Use what you have,
Learn what you can,
Create what you need.

 

[khwaja]

No worries mate. Thanks again for your help. Initially, I thought that was some sort of special Access codes only known to you and I just did not suspect these to be formatting error.

Anyway, I tried NZ on it own, but to no avail. I am still getting a non date format as a result of calculation. In fact, when I use NZ function, it returns me reverse results. I guess folllowing example can be used for your testing.
To Options1 Option From
30-Jan-09
30-Sep-10 1 1/10/2010
31-Dec-19 2 1/01/2020
05-Feb-01
12-Jan-03 1 13/01/2003
04-Feb-09
01-Dec-16 2 2/12/2016
31-May-06 1 1/06/2006
17-Aug-13 2 18/08/2013

The column "To" shows dates in date format (no issues) when lease is expirying and the column Options1 signifies whether we can extend the lease or not. If we can, a number shows there (again no issues with the format). The third column (option From) is the claculation which currently has the following formula.

Option From: IIf(IsNull([Options1]),Null,DateAdd("y",1,[LeaseTermTo]))

The test is if the middle coumn has a null value, give us a null, otherwise show one day more than the value in "To" column. As you can figure out from the result in the third column, calculation is correct but it is not in date format (there are no trailing zeros where single digit date). I tried using NZ function, it returns revese result.

If you can sort out what should be returned when there is a null without affecting the format when there is a value, I will be grateful.

Cheers

 

[tlbroadbent]

Add the format to the query.

LeaseNotice: Format(IIf(IsNull([OptionNotice]),Null,IIf([OptionNotice]=0,DateAdd("m",-8,[LeaseExpiry]),DateAdd("m",-[OptionNotice]-2,[LeaseExpiry]))),"Medium Date&quot

This works in Access 97 and 2000.

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Thanks but it merely changes the displayed format but does not change the data into dates. I test it by copying the data into Excel and checking the format.

Thanks