Report field base on another (1 Viewer)

[Design by Sue]

I have a report that displays training completed for an employee. Some of these classes have expiration dates and others do not. The query that this is based on has fields that are DateCompleted- the date the training was completed, Expiration - the number of months that the training is good for, sometimes there is a number and sometimes there is a 0, and I have created field called ExpDate that shows the actual date of the expiration for that training. The report was set up to display the Expiration (number of month til the expiration) but to make it more userfriendly I need to put the ExpDate on the report. That part I got but here is where I could use some help. How do I get the ExpDate to be blank if the training does not expire (Expiration = 0)? I am thinking I need to add an IF statement to the onload but maybe not. But at this point I really need a kick start to get this going.

I have not worked in access for a couple of years and my brother who was always there to help passed away so now I am on my own, and have forgotten a lot of what I knew. Please help - and thanks in advance for any assistance.

 

[theDBguy]

A calculated expression might go something like:

=IIf([Expiration=0,"",[ExpirationDate])

 

[plog]

You should fix this in a query. You say you have a calculated field, which implies this Report is based on a query. So in that query you should build a calculated field using theDbguys logic.

 

[Design by Sue]

Trying - I get the concept but when I tried to create a new field I get an error Circular reference by alias Expr1 in query's definition SELECT list".. I used the build function and entered this IIf([Expiration],=0,[ExpDate]) And when I entered IIf([Expiration],=0,"",[ExpDate]) I get the Expression you entered has a function containing the wrong number of arguments. Sorry I know this should be easy but...

Appreciate the replies

NOTE- corrected to IIf([Expiration]=0,"",[ExpDate]) and getthe circular reference message

 

[theDBguy]

Trying - I get the concept but when I tried to create a new field I get an error Circular reference by alias Expr1 in query's definition SELECT list".. I used the build function and entered this IIf([Expiration],=0,[ExpDate]) And when I entered IIf([Expiration],=0,"",[ExpDate]) I get the Expression you entered has a function containing the wrong number of arguments. Sorry I know this should be easy but...

Appreciate the replies

NOTE- corrected to IIf([Expiration]=0,"",[ExpDate]) and getthe circular reference message

If [Expiration] is a calculated column, try using the formula you used. For example:

Expiration: [ExpDate]-Date()
DueDate: IIf([ExpDate]-Date()=0,"",[ExpDate])

Hope that makes sense...

 

[Design by Sue]

Not sure I understand. Expiration is a field from a table and is not a calculated one, it is a number field that is entered through a user form. The field I created in the query for ExpDate is ExpDate: DateAdd("m",[Expiration],[DateCompleted]) which seems to give the same results as the second line of your code. So what I need to do is not show any of the dates that are the same as the DateCompleted field or that have an expiration of other than 0

 

[Design by Sue]

Any one else able to solve my problem? Please post if you can help. How do I show no record in a field on a report if what I described above is true?

 

[Gasman]

So what I need to do is not show any of the dates that are the same as the DateCompleted field or that have an expiration of other than 0

That does not make sense to me? I would have thought where Expiration is 0, so that ExpDate and DateCompleted are the same?

 

[plog]

So what I need to do is not show any of the dates...

Semantics time---do you want to not show any of the dates? Or any of the records? There's a difference:

FirstName, LastName, DateCompleted, Expiration, ExpDate
John, Smith, 4/24/2024, 0, 4/24/2024
Bob, Jones, 4/1/2024, 10, 4/11/2024
Sally, Metz, 4/2/2024, 2, 4/4/2024

Suppose the above data is for the report. John Smith is the only record with an Expiration that is 0. So in the report do you only want the John Smith record to appear? Or do you want all 3 records to appear but with the DateCompleted and ExpDate fields blank for Bob Jones and Sally Metz?

...any of the dates that are the same as the DateCompleted field or that have an expiration of other than 0

That doesn't make sense. Mostly because "any of the dates" is very ambigous. What date(s) are to be compared to DateCompleted? Just the ExpDate, because if so that contradicts your other criteria of only showing Expiration=0, because ExpDate is based on Expiration + DateComplete.

 

[Design by Sue]

Thanks so much for helping! I am sorry that I am being confusing. I am doing a report on right now on one employee to show all of his completed training. The training either expires in a specific number of months (In the Expiration field represented by a number) or does not expire at all (in the Expiration field represented by 0) On the report I need to diaplay all of the trainng classes the employee has completed but in the ExpDate I need to only display the dates that expire. Eventually I need to use this info to create reports for all employees but once I get this working I am sure I can figure it out on the other reports. I am attaching a screenshot showing the query I am working with. So if on this the Expiration is 0 I do not want a date to appear in the ExpDate on the report but if there is any number in the Expiration then the ExpDate needs to be shown on the report. I hope this makes it more clear.

 

[plog]

So if on this the Expiration is 0 I do not want a date to appear in the ExpDate on the report but if there is any number in the Expiration then the ExpDate needs to be shown on the report.

Perfect. You need to change your ExpDate field to catch those 0 values:

ExpDate: iif(Expiration<>0, DateAdd("m",[Expiration],[DateCompleted]))

ExpDate will populate for non-zero values of Expiration and remain blank for 0 values.

 

[Design by Sue]

YES!! Thank you - I knew it had to be simple but it has been so long since I worked on access. (and I am a graphic designer so therefore right brained and I have to switch to the left and it is not easy for me!)

I really appreciate the help here on the forum. Thank you plog and everyone else who tried to make sense of my nonsense!