Qry Expression issue

[Sean75db]

I am working on some report query's, i have 2 fields that i need to make one if 1 one of them is not null.
Field 1 is: Date Sent
Field 2 is: Date Unpended

In my query i have this expression
Use Date: IIf(IsNull([Date Unpended]),[Date Sent],[Date Unpended])
So what i mean by all this is the 3rd field:
[Use Date] = [Date Sent] if [Date Unpended] is Null or = [Date Unpended] if [Date Unpended] is not null

i have tried a few variations but what i am trying to get is this:

If [Date Unpended] is null then use [Date Sent]
If [Date Unpended] is not null then use [Date Unpended]

This is just a step for another expression i am doing but i need to know what date to use before i can do the other one.

Thanks for any suggestions

 

[plog]

So what's happening? Error message? Invalid/Unexpected results? Home catches on fire when you run it?

Your expression looks correct:

Use Date: IIf(IsNull([Date Unpended]),[Date Sent],[Date Unpended])

 

[Sean75db]

Ok so what i am working with in the query i have 31 records, based on the expression i get 5 dates in [Use Date] and those are the fields in [Date Unpended] that are not null, if i change is around i get all 31 dates from [Date Sent]

What i am trying to so is figure out work time, The [Date Sent] is on all records. we calculate work time from [Date Sent] to a completed date]. In some cases the work will be pended so if it is pended then we have to figure work time from when it was [Date Unpended] to the completed date.

so if i can query the time into one field then run the calculation for the work time.

 

[plog]

Can you post data to illustrate your explanation? Include table and field names.

 

[Sean75db]

I figured it out, what i was do was right but i needed to do is a different way
thanks for responding to my post.

Use Date: IIf([Date Unpended]=" ",[Date Sent],[Date Unpended])

That was the way i need to do the expression

 

[vbaInet]

Sean75db, there's a problem with your code. Why are you needing to test for (what looks like) the empty string?

 

[Sean75db]

The reason is we track the incoming date and the completed date and is usefor different reasons thought out our reporting. Each record can be pended if we don't have what we need to complete. Once the record is Unpended and completed we calculate the work time. So our start time can either be the incoming date or the Unpended date. so i have to look and see if the record has an Unpended date if it doesn't then work time is calculated using incoming date and completed date. If the record has an Unpended date then work time is calculated by using Unpended date and completed date.

Does that explain my method?

 

[vbaInet]

Ok, what I'm highlighting is that you don't need to test for "", you can use the Nz() function if the data type of the field is a Date/Time field.

 

[Sean75db]

So something like;

Date Use: =(Nz([Unpended Date]),[Date Sent])

 

[vbaInet]

That's it! You don't need the extra parens:

Date Use: Nz([Unpended Date], [Date Sent])

 

[Sean75db]

Thanks for the idea's, it doesn't work for this need but it did resolve another issue i was having within a report.

I think the issue is that [Date Sent] and [Unpended Date] are fields and values generated in this query so i do have to test for Null values in order to reach the desired results, unless i am missing some, and that could be the case but the expression i had post does give me the values i need to complete the next step in calculating the work times.

 

[vbaInet]

I think the issue is that [Date Sent] and [Unpended Date] are fields and values generated in this query so i do have to test for Null values in order to reach the desired results,

You don't. Let's see how you derived at the two fields.