query question

  • Thread starter Thread starter Lauren B
  • Start date Start date
L

Lauren B

Is there a way to specify in a query that all blank values be populated with
a standard comment?

For example, I have a query pulling customer name and the date their
contract was signed. Not all customer's have signed their contract yet so,
their date field is blank. Is there a way to write the query so that the
blank dates will automatically be populated with the phrase "No Contract"

Thank you in advance for any assistance.

LB
 
Is there a way to specify in a query that all blank values be populated with
a standard comment?

For example, I have a query pulling customer name and the date their
contract was signed. Not all customer's have signed their contract yet so,
their date field is blank. Is there a way to write the query so that the
blank dates will automatically be populated with the phrase "No Contract"
Click to expand...

Yes: you can use the NZ() function.

NZ([SigningDate], "No Contract")

as the Control Source of a textbox will do the trick.

Note that this will make the date uneditable.

John W. Vinson[MVP]
 
Hi Lauren,

You can use something like this in the query, where ContractDate is replaced
with the field name you are using -

IIf(IsNull(ContractDate), "No Contract", ContractDate)

Jim
 
Yes, BUT...

You can use the NZ function to replace the null with a specified string value,
however that will turn all your dates into string values.

If you want to do this in a report, you can use the format property of the
control in the report to show whatever you wish when the date is null. Lookup
help on the format property of the control. Going from memory, it would be
something like

"mm/dd/yyyy";"No Contract"

If you do this in the query and want to sort or select by the date field you
will need to include it twice in your query. Once to show the format and once
to do the sorting and selecting.

Field: ShowPhrase: NZ(ContractDate,"No Contract")

and again

Field: ContractDate
Sort: <Your Choice>
Show: Unchecked
Where: <Your Criteria>
 
Back
Top