"Hiding" field if another field is referenced in another table.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OMS - 22 Dec 2006 20:18 GMT

Hi,

I hope this makes sense. I have a report that I want to "hide" the
[DOSE] from [tbl_ORDERS] if the drug is listed on a different table,
[tbl_PCA]. There are only about ten so I put them on a separate reference
table as I thought it would be faster. The code below is in the detail of
the report. Any help would be greatly appreciated.

=Trim([GENERIC_NAME]) & IIf([BRAND_NAME]="","","(" &
Trim([BRAND_NAME]) & ")") & " " & Trim(fDose([DOSE])) & " " &
IIf(Trim([SOL_VOLUME])<>"",(Trim([SOL_VOLUME]) & " " & Trim([PACKAGE_UNIT])
& " " & Trim([modSIG])),Trim([modSIG])) & " " & IIf(Trim([RATE])="" Or
[RATE]="TITRATE",Trim([RATE]),Trim([RATE]) & " ml/hr") & " " & [RTEDESC]

Thanks,
OMS

Reply to this Message

Steve Schapel - 24 Dec 2006 03:02 GMT

OMS,

First of all, if I can make a comment not directly related to your
question... The Trim() function removes leading and trailing spaces from
the data. It seems very unlikely to me that this is necessary. Also,
would your BRAND_NAME and RATE and SOL_VOLUME fields really ever contain
"" a zero-length string? In my experience, this would be very unusual.

If it's not already, add the tbl_PCA table to the query that the report
is based on, joined with a Left Join from the drug field in the
tbl_ORDERS table. I am not sure what data type DOSE is, nor the purpose
of the fDose function. But I think that section of your expression
could be like this:
... & IIf(IsNull([tbl_PCA].[Drug]),fDose([DOSE]),Null)+" " & ...

Signature

Steve Schapel, Microsoft Access MVP

> Hi,
>
[quoted text clipped - 12 lines]
> Thanks,
> OMS

Reply to this Message

OMS - 02 Jan 2007 21:29 GMT

Sorry for the delay, I was away for holidays.
I ran this pretty much as written but Access kept dropping the brackets
around [Drug] ie. [tbl_PCA].Drug then would ask for tblPCA parameter. When I
used [Drug] alone instead of [tbl_PCA].[Drug] it would give the correct
results but took 20+ minutes to run, normal is less than a minute. It has
been suggested that I use a Case mod. What do you think?
OMS

> OMS,
>
[quoted text clipped - 27 lines]
>> Thanks,
>> OMS

Reply to this Message

Steve Schapel - 03 Jan 2007 08:51 GMT

OMS,

Hmmm, I'm not really sure what is going on here. First of all, if you
followed my original idea, you would have two [Drug] fields in the
query, one form each of the tables, so it seems to me that you would
need to identify the source table via syntax such as [tbl_PCA].Drug.
Probably if it was mine, I would try putting that expression into a
calculated field in the query itself, rather than in the control source
of a report textbox. Why not try that. But even then, I can't see how
or why it would take 20 minutes. What is the fDose() function? Can you
post back with the SQL view of the query?

Signature

Steve Schapel, Microsoft Access MVP

> Sorry for the delay, I was away for holidays.
> I ran this pretty much as written but Access kept dropping the brackets
[quoted text clipped - 3 lines]
> been suggested that I use a Case mod. What do you think?
> OMS

Reply to this Message

OMS - 10 Jan 2007 20:05 GMT

Sorry I haven't gotten back to you Steve. Very poor etiquette on my part, I
apologize.
I was told (by my superior) to go another avenue, so I decided to write a
mod so I wouldn't have to use another table and another function in the
report detail (I put the fDose in this mod).
Here's what I did, it seems to work too!:

Public Function fPCA(ByVal DRUG_CODE As String, ByVal DOSE As String)
On Error GoTo error
Dim strCode As String
Dim strDose As String

strCode = Trim(DRUG_CODE)
strDose = DOSE
DOSE = Trim(DOSE)

Select Case strCode
Case "1782", "2419", "2666", "2667", "3305", "3306", "3644",
"3690", "3841", "4158"
strDose = ""
Case Else
strDose = fDose(DOSE)
End Select
fPCA = strDose

exit_sub:
On Error GoTo 0
Exit Function
error:
Select Case Err.number
Case 2427 'expression has no value
Exit Function
Case Else
MsgBox Err.Description
Exit Function
End Select

Again, my apologies and thanks.

OMS

> OMS,
>
[quoted text clipped - 15 lines]
>> minute. It has been suggested that I use a Case mod. What do you think?
>> OMS

Reply to this Message

Steve Schapel - 12 Jan 2007 18:04 GMT

Very good, OMS, thanks for letting us know.

Signature

Steve Schapel, Microsoft Access MVP

> Sorry I haven't gotten back to you Steve. Very poor etiquette on my part, I
> apologize.
[quoted text clipped - 36 lines]
>
> OMS

Reply to this Message