Dates and Nulls

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

Guest

I have a table of bank accounts that includes both open and closed accounts.
Closed accounts can be identified because there is a date in the CloseDate
field.

Now I want to open a form, Account, based on this ACCOUNTS table. When the
form opens--when called from a switchboard--I want the form to open on and
opened account. I then use a combo box on the Account form to move between
open accounts. The combo box works fine. The problem is when the form first
opens. The first account in the ACCOUNTS table is a closed account and the
form always opens on that account. [BTW I can't base the form on a query that
excludes CloseDate accounts because people want to type in the account number
on the form to "fool" the form into showing specific closed accounts.]

I have tried a wide variety of approaches on the switchboard button, but
nothing works. Here is an example of what I tried so that the form will open
on a open account. Everything I have tried is based on the Null constant.

stDocName = "Account"
stCriteria = "[CloseDate] = '" & Null & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
 
Glen said:
I have a table of bank accounts that includes both open and closed
accounts. Closed accounts can be identified because there is a date
in the CloseDate field.

Now I want to open a form, Account, based on this ACCOUNTS table.
When the form opens--when called from a switchboard--I want the form
to open on and opened account. I then use a combo box on the Account
form to move between open accounts. The combo box works fine. The
problem is when the form first opens. The first account in the
ACCOUNTS table is a closed account and the form always opens on that
account. [BTW I can't base the form on a query that excludes
CloseDate accounts because people want to type in the account number
on the form to "fool" the form into showing specific closed
accounts.]

I have tried a wide variety of approaches on the switchboard button,
but nothing works. Here is an example of what I tried so that the
form will open on a open account. Everything I have tried is based on
the Null constant.

stDocName = "Account"
stCriteria = "[CloseDate] = '" & Null & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
Click to expand...

stCriteria = "[CloseDate] Is Null"

You can never use "=" with Null (nor "<>").

It's "Is Null" in queries and filters and the IsNull() function in VBA code.
 
Neat! It worked. Thanks.

Rick Brandt said:
Glen said:
I have a table of bank accounts that includes both open and closed
accounts. Closed accounts can be identified because there is a date
in the CloseDate field.

Now I want to open a form, Account, based on this ACCOUNTS table.
When the form opens--when called from a switchboard--I want the form
to open on and opened account. I then use a combo box on the Account
form to move between open accounts. The combo box works fine. The
problem is when the form first opens. The first account in the
ACCOUNTS table is a closed account and the form always opens on that
account. [BTW I can't base the form on a query that excludes
CloseDate accounts because people want to type in the account number
on the form to "fool" the form into showing specific closed
accounts.]

I have tried a wide variety of approaches on the switchboard button,
but nothing works. Here is an example of what I tried so that the
form will open on a open account. Everything I have tried is based on
the Null constant.

stDocName = "Account"
stCriteria = "[CloseDate] = '" & Null & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
Click to expand...

stCriteria = "[CloseDate] Is Null"

You can never use "=" with Null (nor "<>").

It's "Is Null" in queries and filters and the IsNull() function in VBA code.
Click to expand...
 
Back
Top