If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Inspect record, replace null field on output
I have a record that contains multiple positions for names of individuals on
this record. This record describes names of people involved in car accidents. The record contains 3 name fields, they a (1) a three part Licensee name field with LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME, (2) DOA_NME and (3) OTHER_NME fields. There are also 2 record types, they a record type "D" for Driver of vehicle record (which would be LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME fields), record type "DO" for Driver/Owner of vehicle (which would be the DOA_NME field). If the record type is a "D" record, there will always be a name in the Driver Name field plus a name in the DOA name field. If the record type is a "DO" record, there will always be a name in the Driver Name field, but never a name in the DOA name field. This creates an issue when printing form letters about crash investigations, as the form letter requires the Driver/Owner (DO) name to be printed in a specific position in the form letter, and as sometimes possible, this field could be blank. What I need to do is to come up with some form of Visual Basic code snippet that is called when this form is printed, that, inspects the record field named DOA_NME, and if this field of the report record is blank or null, then would use the the combined fields of (-[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] as a replacement for DOA_NME null field on this report....... Problem is that I'm not quite sure how to do this with Visual Basic and the automated form capability that Access has. Is there someone in our network of Access group that could assist in a VB code snippet and where it would be placed in the form. Does this have to be done via a Basic Module? At the time that this form letter is printed, the process is to read the table, and on certain types of crash records, print a form letter. The code for the paragraph in question follows: =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME]+" " & [LIC_SUBT_TXT] & ", while operating a vehicle owned by " & [DOA_NME] & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." I've thought of just automating the system when the record was first written to check the record type, and if "DO" then having the application duplicate LIC_FIRST_NME, LIC_MIDDLE_NME, and LIC_LAST_NME field into the DOA_NME field, but this creates a problem on automation of other reports as well as creates a 'Disgruntled' user issue as the "OLD" system did not do that....problem. Any assistance would be greatly appreciated. Thanks in advance.... -- Robert Nusz Sr. Programmer Analyst II |
#2
|
|||
|
|||
You can use the Nz() function to replace a null with any other string.
="..." & Nz([DOA_NME], [LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] & " ..." -- Duane Hookom MS Access MVP -- "RNUSZ@OKDPS" wrote in message ... I have a record that contains multiple positions for names of individuals on this record. This record describes names of people involved in car accidents. The record contains 3 name fields, they a (1) a three part Licensee name field with LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME, (2) DOA_NME and (3) OTHER_NME fields. There are also 2 record types, they a record type "D" for Driver of vehicle record (which would be LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME fields), record type "DO" for Driver/Owner of vehicle (which would be the DOA_NME field). If the record type is a "D" record, there will always be a name in the Driver Name field plus a name in the DOA name field. If the record type is a "DO" record, there will always be a name in the Driver Name field, but never a name in the DOA name field. This creates an issue when printing form letters about crash investigations, as the form letter requires the Driver/Owner (DO) name to be printed in a specific position in the form letter, and as sometimes possible, this field could be blank. What I need to do is to come up with some form of Visual Basic code snippet that is called when this form is printed, that, inspects the record field named DOA_NME, and if this field of the report record is blank or null, then would use the the combined fields of (-[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] as a replacement for DOA_NME null field on this report....... Problem is that I'm not quite sure how to do this with Visual Basic and the automated form capability that Access has. Is there someone in our network of Access group that could assist in a VB code snippet and where it would be placed in the form. Does this have to be done via a Basic Module? At the time that this form letter is printed, the process is to read the table, and on certain types of crash records, print a form letter. The code for the paragraph in question follows: =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME]+" " & [LIC_SUBT_TXT] & ", while operating a vehicle owned by " & [DOA_NME] & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." I've thought of just automating the system when the record was first written to check the record type, and if "DO" then having the application duplicate LIC_FIRST_NME, LIC_MIDDLE_NME, and LIC_LAST_NME field into the DOA_NME field, but this creates a problem on automation of other reports as well as creates a 'Disgruntled' user issue as the "OLD" system did not do that....problem. Any assistance would be greatly appreciated. Thanks in advance.... -- Robert Nusz Sr. Programmer Analyst II |
#3
|
|||
|
|||
Duane,
You are without question, one of the sharpest Access coders and so willing to assist in questions without hestitation. Thank you so much. Your response worked wonders, and resolved my problem without further question or issues. I've included the modified unbound text area below so others may benefit with form letter requirements. Hope this helps others as it has helped me. ------------------------------------------------------------------------------------------------------- =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME]+" " & [LIC_SUBT_TXT] & ", while operating a vehicle owned by " & Nz([DOA_NME],[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME]) & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." -------------------------------------------------------------------------------------------------- Thanks again Duane, your assistance is so much appreciated. "Duane Hookom" wrote: You can use the Nz() function to replace a null with any other string. ="..." & Nz([DOA_NME], [LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] & " ..." -- Duane Hookom MS Access MVP -- "RNUSZ@OKDPS" wrote in message ... I have a record that contains multiple positions for names of individuals on this record. This record describes names of people involved in car accidents. The record contains 3 name fields, they a (1) a three part Licensee name field with LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME, (2) DOA_NME and (3) OTHER_NME fields. There are also 2 record types, they a record type "D" for Driver of vehicle record (which would be LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME fields), record type "DO" for Driver/Owner of vehicle (which would be the DOA_NME field). If the record type is a "D" record, there will always be a name in the Driver Name field plus a name in the DOA name field. If the record type is a "DO" record, there will always be a name in the Driver Name field, but never a name in the DOA name field. This creates an issue when printing form letters about crash investigations, as the form letter requires the Driver/Owner (DO) name to be printed in a specific position in the form letter, and as sometimes possible, this field could be blank. What I need to do is to come up with some form of Visual Basic code snippet that is called when this form is printed, that, inspects the record field named DOA_NME, and if this field of the report record is blank or null, then would use the the combined fields of (-[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] as a replacement for DOA_NME null field on this report....... Problem is that I'm not quite sure how to do this with Visual Basic and the automated form capability that Access has. Is there someone in our network of Access group that could assist in a VB code snippet and where it would be placed in the form. Does this have to be done via a Basic Module? At the time that this form letter is printed, the process is to read the table, and on certain types of crash records, print a form letter. The code for the paragraph in question follows: =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME]+" " & [LIC_SUBT_TXT] & ", while operating a vehicle owned by " & [DOA_NME] & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." I've thought of just automating the system when the record was first written to check the record type, and if "DO" then having the application duplicate LIC_FIRST_NME, LIC_MIDDLE_NME, and LIC_LAST_NME field into the DOA_NME field, but this creates a problem on automation of other reports as well as creates a 'Disgruntled' user issue as the "OLD" system did not do that....problem. Any assistance would be greatly appreciated. Thanks in advance.... -- Robert Nusz Sr. Programmer Analyst II |
#4
|
|||
|
|||
The following code will also eliminate any additional blank spaces in the
unbound text field IF and WHEN the field LIC_SUBT_TXT is null value. This print example would be helpful I feel to those wanting to use Access Tables and Form letter type reports. Hope this helps others as it has helped me. Thanks again Duane and others for all your support and help,,,,, Carry on. --------------------------------------------------------------------------------------------------------- =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT]) & ", while operating a vehicle owned by " & Nz([DOA_NME],[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT])) & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." --------------------------------------------------------------------------------------------------------- "RNUSZ@OKDPS" wrote: Duane, You are without question, one of the sharpest Access coders and so willing to assist in questions without hestitation. Thank you so much. Your response worked wonders, and resolved my problem without further question or issues. I've included the modified unbound text area below so others may benefit with form letter requirements. Hope this helps others as it has helped me. ------------------------------------------------------------------------------------------------------- =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME]+" " & [LIC_SUBT_TXT] & ", while operating a vehicle owned by " & Nz([DOA_NME],[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME]) & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." -------------------------------------------------------------------------------------------------- Thanks again Duane, your assistance is so much appreciated. "Duane Hookom" wrote: You can use the Nz() function to replace a null with any other string. ="..." & Nz([DOA_NME], [LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] & " ..." -- Duane Hookom MS Access MVP -- "RNUSZ@OKDPS" wrote in message ... I have a record that contains multiple positions for names of individuals on this record. This record describes names of people involved in car accidents. The record contains 3 name fields, they a (1) a three part Licensee name field with LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME, (2) DOA_NME and (3) OTHER_NME fields. There are also 2 record types, they a record type "D" for Driver of vehicle record (which would be LIC_FIRST_NME, LIC_MIDDLE_NME, LIC_LAST_NME fields), record type "DO" for Driver/Owner of vehicle (which would be the DOA_NME field). If the record type is a "D" record, there will always be a name in the Driver Name field plus a name in the DOA name field. If the record type is a "DO" record, there will always be a name in the Driver Name field, but never a name in the DOA name field. This creates an issue when printing form letters about crash investigations, as the form letter requires the Driver/Owner (DO) name to be printed in a specific position in the form letter, and as sometimes possible, this field could be blank. What I need to do is to come up with some form of Visual Basic code snippet that is called when this form is printed, that, inspects the record field named DOA_NME, and if this field of the report record is blank or null, then would use the the combined fields of (-[LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " & [LIC_LAST_NME] as a replacement for DOA_NME null field on this report....... Problem is that I'm not quite sure how to do this with Visual Basic and the automated form capability that Access has. Is there someone in our network of Access group that could assist in a VB code snippet and where it would be placed in the form. Does this have to be done via a Basic Module? At the time that this form letter is printed, the process is to read the table, and on certain types of crash records, print a form letter. The code for the paragraph in question follows: =" That " & [LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME]+" " & [LIC_SUBT_TXT] & ", while operating a vehicle owned by " & [DOA_NME] & ", was involved in an accident on " & Format([ACC_DATE],"dd mmmm"", ""yyyy") & ", and as a result thereof, damages in excess of $300.00 were sustained." I've thought of just automating the system when the record was first written to check the record type, and if "DO" then having the application duplicate LIC_FIRST_NME, LIC_MIDDLE_NME, and LIC_LAST_NME field into the DOA_NME field, but this creates a problem on automation of other reports as well as creates a 'Disgruntled' user issue as the "OLD" system did not do that....problem. Any assistance would be greatly appreciated. Thanks in advance.... -- Robert Nusz Sr. Programmer Analyst II |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Replace a null field | Kez | New Users | 2 | February 8th, 2005 12:45 AM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |
Values not equal but not returning in Query | Lynn Arlington | Running & Setting Up Queries | 7 | October 1st, 2004 06:23 PM |
Need someone to look at SQL statement | Dkline | Running & Setting Up Queries | 4 | August 3rd, 2004 05:12 PM |