G
ggc
Hi!!!!
Let me present data first. I'm using Access 95.
I extract transaction data from a mainframe in a table like this:
DATE_TIME_STAMP CODE_PFX CODE_SFX BASE_DATA
RUN_DATE USER_ID
20030921061714068760 PRT ADD 60054355400501 MPNY 3/16"
WASHER 21-Sep-03 PJONES
20030921061714074040 PRT ADD 60132221100303 MDLF 3/16"
BOLT 21-Sep-03 MSMITH
20030921061714086520 PRT ADD 60132221100201 MALB 5/8"
BOLT 21-Sep-03 LALTMAN
20030921224620008760 BLL DLS 60076645500202 MDLF
21-Sep-03 DBUTLER
20030922003651008760 PRT CHG 60013221100303 CSIT=PL1
22-Sep-03 RPECK
20030922003651021720 PRT CHG 60054355400101 DESC=1.5"
SHAFT 22-Sep-03 SFAULK
Then, using a query (let's call it QUERY1), I split the data for a given
transaction into more usable data like this:
DATE_STAMP CODE_PFX CODE_SFX PART_NBR DATA_ID
DATA_ELEM RUN_DATE USER_ID
20030922003651 PRT CHG 60013221100303 CSIT PL1
22-Sep-03 RPECK
20030922003651 PRT CHG 60054355400101 DESC 1.5"
SHAFT 22-Sep-03 SFAULK
For the PRT CHG combination, DATA_ID is variable, and DATA_ELEM is variable
depending on the value of DATA_ID on each record. I'm creating a report, and
my desired output would be like this:
PART CHANGES
Part Number Data changed Date changed Changed by
user
----------------------------------------------------------------------------
-----
60013221100303 Customer Site: PL1 22-Sep-03 RPECK
60054355400101 Description: 1.5" SHAFT 22-Sep-03 SFAULK
The "Data changed" field is a calculated field, so I coded a function to
"decypher" the DATA_ELEM info depending on DATA_ID, like this:
Function Decypher(ElemID, ElemData As String) As String
If ElemID = "CSIT" Then Decypher = "Customer Site: " & Trim(ElemData)
If ElemID = "DESC" Then Decypher = "Description: " & Trim(ElemData)
End Function
I set the DataChanged textbox control source in the report to
"=Decypher([DATA_ID],[DATA_ELEM])", but I get "#Error" as a result. If I
bring up the code window in the module and ask "? Decypher("CSIT","PL1")" I
get the correct output. I set a breakpoint in the first statement of the
function, but it never gets in.
To make a long story short, I've tried all my known possibilities, and
referencing the function from within the report gives "#Error" output, even
though the function is recognized by the Expression Builder, and works from
within the debug window.
Does anybody know what can be wrong?
Thank you,
GG.
Let me present data first. I'm using Access 95.
I extract transaction data from a mainframe in a table like this:
DATE_TIME_STAMP CODE_PFX CODE_SFX BASE_DATA
RUN_DATE USER_ID
20030921061714068760 PRT ADD 60054355400501 MPNY 3/16"
WASHER 21-Sep-03 PJONES
20030921061714074040 PRT ADD 60132221100303 MDLF 3/16"
BOLT 21-Sep-03 MSMITH
20030921061714086520 PRT ADD 60132221100201 MALB 5/8"
BOLT 21-Sep-03 LALTMAN
20030921224620008760 BLL DLS 60076645500202 MDLF
21-Sep-03 DBUTLER
20030922003651008760 PRT CHG 60013221100303 CSIT=PL1
22-Sep-03 RPECK
20030922003651021720 PRT CHG 60054355400101 DESC=1.5"
SHAFT 22-Sep-03 SFAULK
Then, using a query (let's call it QUERY1), I split the data for a given
transaction into more usable data like this:
DATE_STAMP CODE_PFX CODE_SFX PART_NBR DATA_ID
DATA_ELEM RUN_DATE USER_ID
20030922003651 PRT CHG 60013221100303 CSIT PL1
22-Sep-03 RPECK
20030922003651 PRT CHG 60054355400101 DESC 1.5"
SHAFT 22-Sep-03 SFAULK
For the PRT CHG combination, DATA_ID is variable, and DATA_ELEM is variable
depending on the value of DATA_ID on each record. I'm creating a report, and
my desired output would be like this:
PART CHANGES
Part Number Data changed Date changed Changed by
user
----------------------------------------------------------------------------
-----
60013221100303 Customer Site: PL1 22-Sep-03 RPECK
60054355400101 Description: 1.5" SHAFT 22-Sep-03 SFAULK
The "Data changed" field is a calculated field, so I coded a function to
"decypher" the DATA_ELEM info depending on DATA_ID, like this:
Function Decypher(ElemID, ElemData As String) As String
If ElemID = "CSIT" Then Decypher = "Customer Site: " & Trim(ElemData)
If ElemID = "DESC" Then Decypher = "Description: " & Trim(ElemData)
End Function
I set the DataChanged textbox control source in the report to
"=Decypher([DATA_ID],[DATA_ELEM])", but I get "#Error" as a result. If I
bring up the code window in the module and ask "? Decypher("CSIT","PL1")" I
get the correct output. I set a breakpoint in the first statement of the
function, but it never gets in.
To make a long story short, I've tried all my known possibilities, and
referencing the function from within the report gives "#Error" output, even
though the function is recognized by the Expression Builder, and works from
within the debug window.
Does anybody know what can be wrong?
Thank you,
GG.