krazykasper
Registered User.
- Local time
- Today, 16:59
- Joined
- Feb 6, 2007
- Messages
- 35
I am a novice user of MS Access 2003. I’m trying to develop in MS Access 2003, a report I developed using Crystal Reports. I link via ODBC to several tables in a Data Warehouse.
In my Crystal report, I define several fields which are calculations based on fields in one or more of the tables or are based on another defined field. For example: I define several fields such as:
FieldName1 –
If [databasefieldname1] = “string1” Then FieldName1 = “string1a”
If [databasefieldname1] = “string2” Then FieldName1 = “string2a”
If [databasefieldname1] LIKE “string3usingwildcards*and?” Then FieldName1 = “string3a”
Note: There may be as many as 50 more lines like the above; each one using a different ‘string”
Else FieldName1 = “string4”
Following is a real life example:
TonerCategory (This categorizes specific Material Ids into their respective categories.)
If [InvoiceDetail.MaterialId] = “4511100003” Then TonerCategory = “Bndl”
If [InvoiceDetail.MaterialId] = “4511100004” Then TonerCategory = “Bndl”
If [InvoiceDetail.MaterialId] = “7015598” Then TonerCategory = “E1”
If [InvoiceDetail.MaterialId] LIKE “?8s78*” Then TonerCategory = “Ink”
If [InvoiceDetail.MaterialId] LIKE “*Bond*” Then TonerCategory = “Paper”
Note: There may be as many as 50 more lines like the above; each one using a different ‘string”
Else TonerCategory = “Other”
These defined fields are either used directly in my report or in defining another field. For example:
FieldName2 –
If FieldName1 = “string9” Then FieldName2 = $$$ * FieldName3
If FieldName1 = “string8” Then FieldName2 = $$$ * FieldName3
If FieldName1 = “string7” Then FieldName2 = $$ * FieldName3
Note: There may be as many as 5 or 6 more lines like the above; each one using a different ‘string”
Following is a real life example:
ExtendedCreditItem (This takes the specific TonerCategory and multiplies the quantity (CreditQty) by a dollar amount based on specific criteria)
If TonerCategory = “Bndl” Then ExtendedCreditItem = 155 * CreditQty
If TonerCategory = “E1” AND CreditItem = “0” Then ExtendedCreditItem = 146 * CreditQty ELSE [InvoiceDetail.TotalNetPrice] – [InvoiceDetail.FreightPrice]
If TonerCategory = “Paper” AND CreditItem = “0” Then ExtendedCreditItem = 20 * CreditQty
ELSE [InvoiceDetail.TotalNetPrice] – [InvoiceDetail.FreightPrice] * CreditQty
FieldName3 -
If [databasefieldname99] = “string33” AND [databasefieldname98] = “string66” Then FieldName3 * -2
If [databasefieldname99] = “string44” AND [databasefieldname98] = “string77” Then FieldName3 * -2
If [databasefieldname99] = “string55” AND [databasefieldname98] = “string88” Then FieldName3 * -2
ELSE [databasefieldname7]
Following is a real life example:
CreditQty (Based on a specific billing type (CreditMemo, Invoice, etc.) this takes the quantity and multiplies it times a negative or positive number)
If [InvoiceDetail.MaterialId] = “4511100015” AND [InvoiceHeader.BillingType] = “G2” Then CreditQty = [Invoicedetail.Quantity] * -2
If [InvoiceDetail.MaterialId] = “4511100015” AND [InvoiceHeader.BillingType] = “RE” Then CreditQty = [Invoicedetail.Quantity] * -2
If [InvoiceDetail.MaterialId] = “4511100020” AND [InvoiceHeader.BillingType] = “G2” Then CreditQty = [Invoicedetail.Quantity] * -1
If [InvoiceDetail.MaterialId] = “4511100020” AND [InvoiceHeader.BillingType] = “RE” Then CreditQty = [Invoicedetail.Quantity] * -1
ELSE [InvoiceDetail.Quantity]
I believe I can use CASE logic (combined with IF-THEN-ELSE statements) in a Module for some of this code (although I am really a novice Access user and don’t know much about CASE) and I can put some of the fields (e.g. MaterialId with Toner Category) into a Look-Up table. My question is:
How do I link the Module code to my report Query? Do set up a field using the name of the module? If so, how do I reference the source/location of the module? (With other fields it’s easy to pull the source tables into the query.)
In my Crystal report, I define several fields which are calculations based on fields in one or more of the tables or are based on another defined field. For example: I define several fields such as:
FieldName1 –
If [databasefieldname1] = “string1” Then FieldName1 = “string1a”
If [databasefieldname1] = “string2” Then FieldName1 = “string2a”
If [databasefieldname1] LIKE “string3usingwildcards*and?” Then FieldName1 = “string3a”
Note: There may be as many as 50 more lines like the above; each one using a different ‘string”
Else FieldName1 = “string4”
Following is a real life example:
TonerCategory (This categorizes specific Material Ids into their respective categories.)
If [InvoiceDetail.MaterialId] = “4511100003” Then TonerCategory = “Bndl”
If [InvoiceDetail.MaterialId] = “4511100004” Then TonerCategory = “Bndl”
If [InvoiceDetail.MaterialId] = “7015598” Then TonerCategory = “E1”
If [InvoiceDetail.MaterialId] LIKE “?8s78*” Then TonerCategory = “Ink”
If [InvoiceDetail.MaterialId] LIKE “*Bond*” Then TonerCategory = “Paper”
Note: There may be as many as 50 more lines like the above; each one using a different ‘string”
Else TonerCategory = “Other”
These defined fields are either used directly in my report or in defining another field. For example:
FieldName2 –
If FieldName1 = “string9” Then FieldName2 = $$$ * FieldName3
If FieldName1 = “string8” Then FieldName2 = $$$ * FieldName3
If FieldName1 = “string7” Then FieldName2 = $$ * FieldName3
Note: There may be as many as 5 or 6 more lines like the above; each one using a different ‘string”
Following is a real life example:
ExtendedCreditItem (This takes the specific TonerCategory and multiplies the quantity (CreditQty) by a dollar amount based on specific criteria)
If TonerCategory = “Bndl” Then ExtendedCreditItem = 155 * CreditQty
If TonerCategory = “E1” AND CreditItem = “0” Then ExtendedCreditItem = 146 * CreditQty ELSE [InvoiceDetail.TotalNetPrice] – [InvoiceDetail.FreightPrice]
If TonerCategory = “Paper” AND CreditItem = “0” Then ExtendedCreditItem = 20 * CreditQty
ELSE [InvoiceDetail.TotalNetPrice] – [InvoiceDetail.FreightPrice] * CreditQty
FieldName3 -
If [databasefieldname99] = “string33” AND [databasefieldname98] = “string66” Then FieldName3 * -2
If [databasefieldname99] = “string44” AND [databasefieldname98] = “string77” Then FieldName3 * -2
If [databasefieldname99] = “string55” AND [databasefieldname98] = “string88” Then FieldName3 * -2
ELSE [databasefieldname7]
Following is a real life example:
CreditQty (Based on a specific billing type (CreditMemo, Invoice, etc.) this takes the quantity and multiplies it times a negative or positive number)
If [InvoiceDetail.MaterialId] = “4511100015” AND [InvoiceHeader.BillingType] = “G2” Then CreditQty = [Invoicedetail.Quantity] * -2
If [InvoiceDetail.MaterialId] = “4511100015” AND [InvoiceHeader.BillingType] = “RE” Then CreditQty = [Invoicedetail.Quantity] * -2
If [InvoiceDetail.MaterialId] = “4511100020” AND [InvoiceHeader.BillingType] = “G2” Then CreditQty = [Invoicedetail.Quantity] * -1
If [InvoiceDetail.MaterialId] = “4511100020” AND [InvoiceHeader.BillingType] = “RE” Then CreditQty = [Invoicedetail.Quantity] * -1
ELSE [InvoiceDetail.Quantity]
I believe I can use CASE logic (combined with IF-THEN-ELSE statements) in a Module for some of this code (although I am really a novice Access user and don’t know much about CASE) and I can put some of the fields (e.g. MaterialId with Toner Category) into a Look-Up table. My question is:
How do I link the Module code to my report Query? Do set up a field using the name of the module? If so, how do I reference the source/location of the module? (With other fields it’s easy to pull the source tables into the query.)
