Simple Basic Formula Not Working

[Mav3000]

Hi all,

I've the following formula which compares two date fields:

' Evalulate Forecast & Actual Dates

dim DateForecast as date

dim DateActual as date

dim Result as string

' Comparision

DateActual = DateValue({dtblDMC2163Options.D_MSV_A})

DateForecast = DateValue({dtblDMC2163Options.D_MSV_F})

if DateActual = dateserial(1901,12,31) then Result = "N/A"

if DateActual < dateserial(1990,01,01) and DateForecast = dateserial(1901,12,31) then Result = "N/A"

if DateActual > dateserial(1990,01,01) then Result = cstr(DateActual)

if DateActual < dateserial(1990,01,01) and DateForecast > dateserial(1990,01,01) then Result = cstr(DateForecast)

Result = cstr(DateForecast)

' Result

formula = Result


Basically it all works ok, except that the 'DateForcast' value is not shown. I've commented out all lines of code except 'formula = DateForecast' and this does output the value, however as soon as I uncomment 'DateActual = DateActual = DateValue({dtblDMC2163Options.D_MSV_A})' the Forecast Date doesn't show up.

Any idea why this may be? The formula says there's no Errors.

 

[Mav3000]

To explain further, this works:

dim DateForecast as date

DateForecast = DateValue({dtblDMC2163Options.D_MSV_F})

formula = DateForecast


However this does not work:


dim DateForecast as date

dim DateActual as date

DateActual = DateValue({dtblDMC2163Options.D_MSV_A})

DateForecast = DateValue({dtblDMC2163Options.D_MSV_F})

formula = DateForecast

 

[lbass]

What does your data look like if you place the two database fields next to each other in the detail section? It sounds like the forecast field may only be populated on alternate rows and is otherwise null.

-LB

 

[Mav3000]

Hi LB,

The date fields are sometimes populated independently, sometimes together.

e.g. some records have a Forecast only, others have Forecast and Actual.

The formula works fine for records which have both a Forecast and an Actual (and the 'N/A' check works for these).

It's only where there is a Forecast and No Actual that the Forecast Date isn't showing.

 

[lbass]

Are report options set to "convert nulls to default values"? Or could one or both fields be null? Can you show a sample of how the fields appear (not the formula) in the detail section?

-LB

 

[Mav3000]

Hi LB, When I set the 'Convert Database NULL Values to Default' and 'Convert Other NULL Values to Default' to Null, the formula works!

Does this have any negative effect in keeping these Report Options checked?

Thanks for your help in solving this problem for me so quickly

 

[Mav3000]

Hi LB,

When I enable the above two settings in Report Options, it seems to prevent me from filtering the Records using the Select Expert.

I wish to filter the Reports to show all records where:

IsNull({dtblDMC2163Options.D_Access_Obtained_A})

However, this formula in the Select Expert returns no results - there should be around 50.

How can I get this working and keep the Report Options as they are?

 

[Mav3000]

Please ignore the above - I believe I've sorted the problem. It was to do with other Report Options and not those mentioned above.

 

[lbass]

If you convert nulls to default values then your null selection wouldn't work--you would have to convert it to the default equivalent. If the field is a string, the selection formula would be:

{dtblDMC2163Options.D_Access_Obtained_A}=""

If you are using any counts or distinctcounts on fields that can be null, using the convert nulls option will mean that fields converted to "" would now be counted--if that is a factor.

-LB

 

[Mav3000]

I think the Distinct counts were causing the problem.

When you set CR to use Default Values, what is the default value for a date?

Thanks again for your help,

Mav3000

 

[lbass]

The distinct counts were causing WHAT problem?

I think the default is usually date(0,0,0), but check by placing the field on the report--if it is date(0,0,0), the field will appear empty. But sometimes other out-of-range dates appear.

-LB