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

[OMS]

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

 

[Steve Schapel]

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)+" " & ...

 

[OMS]

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,

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)+" " & ...

--
Steve Schapel, Microsoft Access MVP

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

 

[Steve Schapel]

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?

 

[OMS]

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,

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?

--
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
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

 

[Steve Schapel]

Very good, OMS, thanks for letting us know.