Inspect record, replace null field on output

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 are: (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 are:
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....
 
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 said:
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 are: (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
are:
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....
Click to expand...
 
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 said:
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 said:
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 are: (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
are:
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....
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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 are: (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
are:
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....
Click to expand...
Click to expand...
Click to expand...
 
Back
Top