Hi there,

In Access Reports if you have a field which may contain a blank field is 
there anyway of getting Access to fill this space with another field if it 
is null.

My specific case is that of customers and their addresses, not all 
customers have a "County" field filled in and when the report is being run 
or printed there are blank spaces apperaring amongst most records.

Thanks in advance
James

James,

<< My specific case is that of customers and their addresses, not all 
customers have a "County" field filled in and when the report is being run 
or printed there are blank spaces apperaring amongst most records. >>

You can set the "Can Grow" and "Can Shrink" properties of that field to 
YES.  Then set its height to zero.  If a value exists, the field will 
show; if not, the field will shrink down to nothing.  However, if another 
field is present immediately to the left or right of that county field, 
then it will always show.

Greg

You can designate a default value for any field in your database.  Keep
in mind that you setting up default value does not stop the user from
deleting the value and repl;acing it with "empty" when the user comes
back to edit the record. 

To set up defaul values.

1) Open required table in in design mode.
2) Click the required field.
3) Type the required default value in Default Value located on General
tab. 

Example, to set a default value of "Australia" on Country field:
a) click County field
a) click General tab to select it
b) type "Australia" in Default Value 

If the user leaves the Country field empty the database will fill it
with "Australia" value.

NOTE: If the string expression that you type includes commas or other
punctuation, be sure to enclose the value in quotes. For example, to use
"New York, N.Y." as the default value, type "New York, N.Y." 

As I mentioned before with this method the user can delete the value and
you are left with blank field once again.  The better method, as far as
I am concerned is to use the Default Value method and also validate the
record before saving it by using Form_BeforeUpdate event or only use
Form_BeforeUpdate event to check each field.

In this example I mixed few different checking methods to give you some
idea how to do it...


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    'How to check text values
    'Check Country 
    If IsNull(Me!Country) Or Me!Country= "" Then
        beep
        MsgBox "Every customer must have a country." & vbCrLf & "Please
enter customer country to continue.", vbCritical, "DATA ERROR: Customer
Country Not Detected!"
        DoCmd.CancelEvent 'Cancel updating this record
        Me!Country.SetFocus 
        Exit Sub
    End If

    'How to check numeric values
    'Check Status
    If Me!MyOtherField < 1 Or Me!MyOtherField Then
        beep
        MsgBox "Every customer must have a status between 1 and 10." &
vbCrLf & "Please enter valid customer status to continue.", vbCritical,
"DATA ERROR: Invalid Customer Status Detected!"
        DoCmd.CancelEvent 'Cancel updating this record
        Me!Status.SetFocus 'Get back and allow the user to fill-in
correct data
        Exit Sub
    End If

    'How to check valid format 
    'Check Customer ID
    If IsNull(Me!CustomerID) Or Me!CustomerID = "" Then
        beep
        MsgBox "Every customer must be allocated a unique Centre
Customer ID." & vbCrLf & "Please enter customer ID to continue.",
vbCritical, "DATA ERROR: Customer ID Not Detected!"
        DoCmd.CancelEvent 'Cancel updating this record
        Me!CenterGrantID.SetFocus 'Get back and allow the user to
fill-in correct data
        Exit Sub
    Else 'Check format 
        If Not ValidatedFormat(Me!CenterGrantID) Then 'Call another
procedure to check ID format
            strMsg = "Centre Grant ID must be entered in X??-0000-00
format. @"
            strMsg = strMsg & "The first part consists of 1 to 3
applicant"
            strMsg = strMsg & " identification code characters(H - HCHA,
C - CERP, "
            strMsg = strMsg & "U - Uni) followed by dash (for example,
H- refer to "
            strMsg = strMsg & "HCHA grant, HU- refers to HCHA/Uni grant
and HCU- "
            strMsg = strMsg & "would refer to HCHA/CERP/Uni grant). " &
vbCrLf & vbCrLf & "The second part "
            strMsg = strMsg & "consists of a 4 digit number in ddmm
format "
            strMsg = strMsg & "(two digit grant year plus two digit
grant month) "
            strMsg = strMsg & "followed by dash (for example, 0109 would
refer to "
            strMsg = strMsg & "2001 September)." & vbCrLf & vbCrLf &
"The third part consists of a "
            strMsg = strMsg & "2-digit number in the nn format
(two-digit grant "
            strMsg = strMsg & "order received (for example, 21 "
            strMsg = strMsg & "would refer to 21st grant received in the
month). "
            strMsg = strMsg & "The full ID would look like this
HU-0109-21.@"
            strMsg = strMsg & "Please enter valid grant ID to
continue..."
            beep
            MsgBox strMsg, vbCritical, "DATA ERROR: Invalid Centre Grant
ID Detected!"
            DoCmd.CancelEvent 'Cancel updating this record
            Me!CenterGrantID.SetFocus 'Get back and allow the user to
fill-in correct data
            Exit Sub
        End If
    End If
    
Exit_Form_BeforeUpdate:
    Exit Sub
    
Err_Form_BeforeUpdate:
    If err = conUnableToSaveWarning Then
        'Do nothing
        Resume Exit_Form_BeforeUpdate
    Else
        MsgBox err & ":" & Error$, vbCritical, "Error Updating Grants
Record!"
        Resume Exit_Form_BeforeUpdate
    End If
End Sub

I hope that was of some help...


Use a default valu

____________________________________________________


John Fejsa
Systems Analyst/Computer Programmer
Hunter Centre for Health Advancement
Locked Bag 10, WALLSEND NSW 2287
Phone: (02) 4924 6336 Fax: (02) 4924 6209
www.hcha.org.au
____________________________________________________


The doors we open and close each day decide the lives we live

____________________________________________________


CONFIDENTIALITY & PRIVILEGE NOTICEe >>> questionaccess@h...
21/01/2003 23:29:29 >>>
Hi there,

In Access Reports if you have a field which may contain a blank field is

there anyway of getting Access to fill this space with another field if
it 
is null.

My specific case is that of customers and their addresses, not all 
customers have a "County" field filled in and when the report is being
run 
or printed there are blank spaces apperaring amongst most records.

Thanks in advance
James

This message is intended for the addressee named
and may contain confidential information.

If you are not the intended recipient, please
delete it and notify the sender.

Views expressed in this message are those of the
individual sender, and are not necessarily the
views of Hunter Health.

Try this: (if coding in VBA)
'=======================================
IF IsNull([field1]) or [field1]= "" THEN
	[field1] = [field2]
END IF

'or

IF LEN([field1]) < 1 THEN
	[field1] = [field2]
END IF
'=======================================

Try this: (if you are using the control source row of the field on a form)
'=======================================
IIF(IsNull([field1]), [field2],[field1]) OR IIF(LEN([field1]) < 1,
[field2],[field1])
'=======================================

later,
b.ware



-----Original Message-----
From: James Potter [mailto:questionaccess@h...]
Sent: Tuesday, January 21, 2003 12:29 PM
To: Access
Subject: [access] Blank Fields on Reports


Hi there,

In Access Reports if you have a field which may contain a blank field is
there anyway of getting Access to fill this space with another field if it
is null.

My specific case is that of customers and their addresses, not all
customers have a "County" field filled in and when the report is being run
or printed there are blank spaces apperaring amongst most records.

Thanks in advance
James