IIF statement not working

[xyba]

Can anyone please tell me if there is an issue with the following IIF statement that I have in a query?

For some reason it's not working though it did before. It's only stopped working since I renamed a couple of the fields that are stated in the statement though I renamed these in the statement too.

IIf(InStrRev([Referral1] & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]) & ("/"+[Referral6]),"/")=0,[Referral1],Mid([Referral1] & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]) & ("/"+[Referral6]),InStrRev([Referral1] & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]) & ("/"+[Referral6]),"/")+1))

Do the fields stated in the query have to be in a specific order in the table? Do they have to be adjacent?

 

[arnelgp]

im afraid if the expression is too long and you use it in query, access will complain.
try wrapping in a function:

Public Function fnLastRef(ParamArray ref() As Variant) As String
Dim v As Variant
Dim i As Integer
Dim pos As Long
For i = UBound(ref) - 1 To 0 Step -1
v = v & (ref(i) + "/")
Next
pos = InStr(v, "/")
If (pos <> 0) Then
fnLastRef = Left(v, pos - 1)
Else
fnLastRef = ref(0)
End If
End Function

 

[marlan]

Do the fields stated in the query have to be in a specific order in the table? Do they have to be adjacent?

No. You can query in any field order you want.

What is your goal in this section:

InStrRev([Referral1] & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]) & ("/"+[Referral6]),"/")

What error message do you get?

 

[xyba]

No. You can query in any field order you want.

What is your goal in this section:

InStrRev([Referral1] & ("/"+[Referral2]) & ("/"+[Referral3]) & ("/"+[Referral4]) & ("/"+[Referral5]) & ("/"+[Referral6]),"/")

What error message do you get?

My goal is to return the value of the last of those fields that has a value, for each record.

I don't get an error message. It simply just returns the entry in field Referral6, or a blank if the record doesn't have an entry in Referall6.

 

[marlan]

How about

Nz(Referral6,Nz(Referral5,Nz(Referral4,Nz(Referral3,Nz(Referral2,Nz(Referral1))))))

if Referral6 is not null, return it, otherwize, return Referral5, etc...

BTW, just for the record, this seems to me not normalized DB structure.

ATB

 

[CJ_London]

looks like your tables are not properly constructed leading to the requlrement for a complex solution

However I think your belief as to how instrrev works is wrong - it returns the last position of the occurrence of the character - from your description of what you are trying to achieve,

look at using the isnull function instead with switch - something like

switch(not isnull(Referral6),Referral6,not isnull(Referral5),Referral5,not isnull(Referral4),Referral4,etc

 

[CJ_London]

marlans solution is much better

 

[xyba]

For some reason I can't multi quote (nothing happens after I select the multi quote icons).

CJ_London and Marlan - I've tried both of your suggestions but both are returning exactly the same as my original expression.

What's confusing me is this worked fine before, until I renamed some fields.

 

[CJ_London]

Personally I would get your table structure right first rather than spending hours trying to patch a bad design - what happens if you have a 7th referrer? or someone completes referall6 and then later referral5

 

[xyba]

Personally I would get your table structure right first rather than spending hours trying to patch a bad design - what happens if you have a 7th referrer? or someone completes referall6 and then later referral5

To give some context, the database tracks documents through their process within our company. So a document will be received and will be referred to a department (referral1). This department may need to then to refer it to another department, which would be referral2. There would be no more than 6 referrals.

 

[marlan]

Well, I mite have had a syntax error:

Nz(Referral1,)

is missing a parameter. I fixed it in the earlier post.

But I think you should do yourself a favour, go with CJ_London's and reconstruct you DB

Personally I would get your table structure right first rather than spending hours trying to patch a bad design - what happens if you have a 7th referrer? or someone completes referall6 and then later referral5

 

[CJ_London]

fair enough, but still doesn't change the fact it is a bad design.

Your table design should be something like

tblDocuments
DocumentPK autonumber
DocDesc text
....
....

tblReferrals
ReferralPK autonumber
DocumentFK long
ReferalDate date
ReferalTo text
...
...

 

[xyba]

fair enough, but still doesn't change the fact it is a bad design.

Your table design should be something like

tblDocuments
DocumentPK autonumber
DocDesc text
....
....

tblReferrals
ReferralPK autonumber
DocumentFK long
ReferalDate date
ReferalTo text
...
...

I take on board what you say but how will the above allow me to report on the current location of the document? Should I have separate tables for each referral or separate fields on the Referrals table?

 

[arnelgp]

have you tried making the function i gave you.
you can call it in query or control.
in query:

LastRoute: fnLastRef([Referal1],[Referal2],[Referal3],[Referal4],[Referal5],[Referal6])

in control (controlsource):
=fnLastRef([txtReferal1],[txtReferal2],[txtReferal3],[txtReferal4],[txtReferal5],[txtReferal6])


you dont, remember i gave that code to you.

 

[xyba]

have you tried making the function i gave you.
you can call it in query or control.
in query:

LastRoute: fnLastRef([Referal1],[Referal2],[Referal3],[Referal4],[Referal5],[Referal6])

in control (controlsource):
=fnLastRef([txtReferal1],[txtReferal2],[txtReferal3],[txtReferal4],[txtReferal5],[txtReferal6])


you dont, remember i gave that code to you.

I wasn't ignoring your suggestion, I was just confused as to how to implement it (where to input it).

 

[xyba]

have you tried making the function i gave you.
you can call it in query or control.
in query:

LastRoute: fnLastRef([Referal1],[Referal2],[Referal3],[Referal4],[Referal5],[Referal6])

in control (controlsource):
=fnLastRef([txtReferal1],[txtReferal2],[txtReferal3],[txtReferal4],[txtReferal5],[txtReferal6])


you dont, remember i gave that code to you.

Managed to figure how to implement the function, but I'm still getting the same result as all the other above suggestions.

 

[arnelgp]

can you give us sample output.

 

[CJ_London]

Should I have separate tables for each referral or separate fields on the Referrals table?

No the data is stored vertically rather than horizontally- the documentFK provides the link back to the document table

To find the latest referral you would have a query to find the latest date - something like

 SELECT *
 FROM tblReferrals INNER JOIN
  (SELECT DocumentFK, max(ReferralDate) AS Latest FROM tblReferrals GROUP BY DocumentFK) AS LR 
 ON tblReferrals.DocumentFK=LR.DocumentFK AND tblReferrals.ReferralDate=LR.Latest

which can be saved as a separate query if you want, which you can then link back to tblDocuments

 SELECT *
 FROM tblDocuments LEFT JOIN [COLOR=red]( SELECT *
 FROM tblReferrals INNER JOIN
  (SELECT DocumentFK, max(ReferralDate) AS Latest FROM tblReferrals GROUP BY DocumentFK) AS LR 
 ON tblReferrals.DocumentFK=LR.DocumentFK AND tblReferrals.ReferralDate=LR.Latest
) AS[/COLOR] LatestReferral 
 ON tblDocuments.DocumentPK=LatestReferral.DocumentFK

If you have saved the first query as a query called LatestReferral then you can delete everything in red

It may seem like more complex code but it will be easier in the long run - you can easily change the first query to show all referrals for example

 

[xyba]

can you give us sample output.

Unfortunately I can't because of the data contained in it. However, I've had a look to see if there's a common denominator between the records but there isn't. Out of 3011 records that have a referral, only 132 show a result using this query. I thought it might only be picking up a value from one referral field but it wasn't because, of the 132, their last referral was in different fields. I've even deleted the query and rebuilt it and get the same results.

 

[xyba]

No the data is stored vertically rather than horizontally- the documentFK provides the link back to the document table

To find the latest referral you would have a query to find the latest date - something like

 SELECT *
 FROM tblReferrals INNER JOIN
  (SELECT DocumentFK, max(ReferralDate) AS Latest FROM tblReferrals GROUP BY DocumentFK) AS LR 
 ON tblReferrals.DocumentFK=LR.DocumentFK AND tblReferrals.ReferralDate=LR.Latest

which can be saved as a separate query if you want, which you can then link back to tblDocuments

 SELECT *
 FROM tblDocuments LEFT JOIN (
 SELECT *
 FROM tblReferrals INNER JOIN
  [COLOR=red]( SELECT *
 FROM tblReferrals INNER JOIN
  (SELECT DocumentFK, max(ReferralDate) AS Latest FROM tblReferrals GROUP BY DocumentFK) AS LR 
 ON tblReferrals.DocumentFK=LR.DocumentFK AND tblReferrals.ReferralDate=LR.Latest
) AS[/COLOR] LatestReferral 
 ON tblDocuments.DocumentPK=LatestReferral.DocumentFK

If you have saved the first query as a query called LatestReferral then you can delete everything in red

It may seem like more complex code but it will be easier in the long run - you can easily change the first query to show all referrals for example

Rebuilding the tables is going to be a big task but, as you say, may be the only option for the long term. Not looking forward to this...lol.