SQL statement question

  • Thread starter Thread starter Kevin Bruce
  • Start date Start date
K

Kevin Bruce

In my work, I annually print several hundred contracts. Within the database,
new contracts are those for which a date of issue has not yet been recorded
in a textbox called 'txtContractedToPresenter'.

I have a form with one button that I want to have either print the new
contract or preview an existing one. The SQL statement for the report reads
the date in the text box and brings up the appropriate set of records. The
following statement in the 'ContractedToPresenter' field (a date field)
works just fine:

[Forms]![frmBookingPresenterContracts]![txtContractedToPresenter]

If I change this statement to Is Null, I get all those items not yet
contracted. That, too, is fine.

Where I am having trouble is in putting the two together so that the
Contract shows either those items corresponding to the contracted date
(ContractedToPresenter = txtContractedToPresenter) OR those yet to be
contracted (ContractedToPresenter = Null).

To my mind, the following statement should accomplish this, but it does not.
It works fine for those items that already have date, but returns an empty
record set for those that are still null.

IIf([Forms]![frmBookingPresenterContracts]![txtContractedToPresenter] Is Not
Null,[Forms]![frmBookingPresenterContracts]![txtContractedToPresenter],Is
Null)

Any suggestions?

Thanks.

_Kevin
 
Kevin:

Use two conditions lines in your query. On one line, put the date field
condition, on the other put the IsNull condition. Then add a field to your
query something like this: SelectedContract:
IsNull(Forms!YourForm!txtContractDateControl), and in the conditions rows
for that field, set it to true (-1) on the same line where you have the
IsNull condition for the date field and false (0) on the same line where the
form specific field date.

HTH
 
Kevin said:
In my work, I annually print several hundred contracts. Within the database,
new contracts are those for which a date of issue has not yet been recorded
in a textbox called 'txtContractedToPresenter'.

I have a form with one button that I want to have either print the new
contract or preview an existing one. The SQL statement for the report reads
the date in the text box and brings up the appropriate set of records. The
following statement in the 'ContractedToPresenter' field (a date field)
works just fine:

[Forms]![frmBookingPresenterContracts]![txtContractedToPresenter]

If I change this statement to Is Null, I get all those items not yet
contracted. That, too, is fine.

Where I am having trouble is in putting the two together so that the
Contract shows either those items corresponding to the contracted date
(ContractedToPresenter = txtContractedToPresenter) OR those yet to be
contracted (ContractedToPresenter = Null).

To my mind, the following statement should accomplish this, but it does not.
It works fine for those items that already have date, but returns an empty
record set for those that are still null.

IIf([Forms]![frmBookingPresenterContracts]![txtContractedToPresenter] Is Not
Null,[Forms]![frmBookingPresenterContracts]![txtContractedToPresenter],Is
Null)
Click to expand...


The problem with your idea is that the QBE criteria assumes
the operator = between the field and your expression, but Is
Null is also an operator, so the end result is nonsense.
The way around it is to add a calculated field to the query
(probably garbled by line wrapping):

ContractedToPresenter =
Forms!frmBookingPresenterContracts!txtContractedToPresenter
OR (ContractedToPresenter Is Null AND
Forms!frmBookingPresenterContracts!txtContractedToPresenter
Is Null)

with the criteria for this calculated field set to True
 
Back
Top