Help Writing a Formula

[TClark14]

I have two fields , actual date, and proposed date. I have two columns in a report that show both of these dates. Proposed Date is always populated, actual date remains blank until entered by user.

Is there a formula I can put into the report that says if actual is blank, then put proposed date in actual?

Thanks
Terry

 

[Newman]

You create another field in your query. Let's call it DateShown. So it goes like this:
DateShown:IIf([actual date]="";[proposed date];[actual date])
And you call [DateShown] instead of [actual date] in your report.

 

[Mile-O]

If you put this in your reports open/activate/load (I never use reports so don't know what the event is!)


txtActualDate = IIF(txtActualDate = Null, txtProposedDate, txtActualDate)


Just remember to change the textbox names to those in your database.

 

[Robert Dunstan]

What I would do is in the report's recordsource (assuming it's a query) is create a calculated field that uses the IIF statement

e.g. IIF([Actual_Date]=Null,[Proposed_Date],[Actual_Date])

Then in the report, for the Actual Date control, change the source to this calculated control.

HTH
Rob

 

[TClark14]

More

It probably would help to mention that the query is pulling the proposed date from table 1, and the actual date from table 2. I can't seem to get the formula to work in the query.

Thanks Again.

 

[Robert Dunstan]

Assuming that when you run the query it's retrieving the correct data, then all you have to do is create a new field in the design of your query and enter the IIF formula in the 'Field' section of the grid.

 

[neileg]

Where are you putting the formula? The formulae suggested above are intended to create a new calculated field in the query.

So what you do is add a new field in the query that does not come from your tables, eg DateShown

You then define this fields using the : and then the formula, all in the Field box for your new field. As Newman said
DateShown:IIf([actual date]="";[proposed date];[actual date])

Mr Dunstan's example is the same, but tests for Null rather than a zero length string ie ""

The fields used to make the calculation should exist in you query, so provided you use exactly the name shown in the Field box in the query grid, it does not matter that they come from different tables.

Mile-O-Phile is suggesting that you do this calculation in vba within the report. Either should work, but if you do it in the query, it's easier for a beginner to troubleshoot.

Sorry if this sounds patronising, but I don't know how much of this is new to you. No offence meant!

 

[TClark14]

Still Problems With Formula

Thanks for the help so far - and Yes I am pretty slow learning when it comes to Access - I don't use it often enough in my job.

Here is the formula I put into the field area of the query
DateToDisplay: IIf([ActualSpudDate]=Null,([tbl2002DrillingProgram]![ProposedSpudDateCalc]),[ActualSpudDate])

The part that IS working is the the fields that are NOT blank - they do bring the dates in that were entered manually, but the blanks are not displaying the proposed dates. Also, if I do not specify which table to retreive the proposed date from, I get an error message that this data can come from more than one table (one is a linked excel table the other is not) and I have chosen to pull the proposed date from the table that is NOT the linked file.

The above formula will run without errors, but leaves fields empty except where an actual date was entered, does not replace blank fields with the proposed date.

Make sense?

 

[Mile-O]

Try this slight change:

DateToDisplay: IIf([ActualSpudDate]="",([tbl2002DrillingProgram
]![ProposedSpudDateCalc]),[ActualSpudDate])

 

[Rich]

'Course you could leave the calculated field out of the query, just add an unbound textbox to the report, set its control source to
=IIf(Not IsDate([ActualSpudDate]),[ProposedSpudDateCalc],[ActualSpudDate])
much better than a calculated field in a query

 

[TClark14]

Still Not Working

I copied and pasted the code from Mile-O-Phile above and this is what happens when I run the query. A window comes up that says:

Enter Parameter Value
tbl2002DrillingProgram!ProposedSpudDateCalc

I have checked the spelling on the table and field names and they are correct.

I'm lost.

Thanks

 

[TClark14]

No Errors on running query, but...

It still will not bring in the proposed date for those records where the actual date is blank.

Is it possible that it is not the the empty cell as actually being "null"? It is set up as a date field with an input mask.

The query runs, but the only dates that display are those that DO have an actual date (not a blank cell).

Thanks Again

 

[neileg]

Yeah, I thought this wasn't spelled out for you. Nulls are not the same as zero length strings ie ""

I'm not sure what empty date fields are but you can use the Nz (null to zero) function in your formula. This will convert nulls to zero length strings, so you can use the comparison ="" and it will work.

Bear in mind I'm not a developer, just a dirty hacker!

 

[TClark14]

Nz Function

Can you tell me how/where to place that in the formula? I am not familiar with it.

Sorry - I am just a Lousy Hacker.

Thanks.

 

[neileg]

DateToDisplay: IIf(Nz([ActualSpudDate])="",([tbl2002DrillingProgram
]![ProposedSpudDateCalc]),[ActualSpudDate])

Hope this helps

 

[TClark14]

Thanks

Thanks for all the help - everything working like it needs to.

I appreciate it.

Terry

 

[neileg]

I bow to Pat's superior wisdom, and, on reflection, agree with the action of Nz. As I said in my posting, I was unsure whether the value of an empty date field was a null or a zero length string. I should have thought this through a little more.

However, provided the valueifnul is set, I don't see why this approach should not work, which would make the relevant statement:
DateToDisplay: IIf(Nz([ActualSpudDate],0)=0,([tbl2002DrillingProgram
]![ProposedSpudDateCalc]),[ActualSpudDate])

Again, I agree with Pat that testing Null=Null will never produce a true condition, but testing 0=0 works perfectly well.

But Pat's solution is more elegant, anyway!

(It's OK, I'll shut up now!)

 

[Rich]

Isn't IsDate an acceptable solution then Pat?