Please help with Formula

[Guest]

Hello all,

I have a form which I design to filter the records. In the Form Footer
section, I have a text box to show a message "No records for selected
criteria" when there are no records shown on the form based on the criteria
input by the users.

Can someone help me with the formula for this text file like if there are
records on the form "null", else the message from above?

Thanks.

 

[Allen Browne]

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records", Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this expression with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section may go
completely blank. When it does, expressions in the Form Header and Form
Footer sections may not display correctly either. Details and example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

 

[Guest]

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when there
are records on the form. When there are no records (Blank in the Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form", "RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access 2003.

Please help. Thanks.

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records", Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this expression with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section may go
completely blank. When it does, expressions in the Form Header and Form
Footer sections may not display correctly either. Details and example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello all,

I have a form which I design to filter the records. In the Form Footer
section, I have a text box to show a message "No records for selected
criteria" when there are no records shown on the form based on the
criteria
input by the users.

Can someone help me with the formula for this text file like if there are
records on the form "null", else the message from above?

Thanks.

 

[Allen Browne]

The square brackets disappear? That's the opposite of what I obverve (i.e.
if I type the expression without the brackets, Access adds them.)

Make sure the Name of this text box is not the same as the name of any field
in the form's Recordset.

This won't work if the form is unbound (i.e. if there is nothing in the
RecordSource property of the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when there
are records on the form. When there are no records (Blank in the Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form", "RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access 2003.

Please help. Thanks.

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records", Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this expression
with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section may go
completely blank. When it does, expressions in the Form Header and Form
Footer sections may not display correctly either. Details and example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

Hello all,

I have a form which I design to filter the records. In the Form Footer
section, I have a text box to show a message "No records for selected
criteria" when there are no records shown on the form based on the
criteria
input by the users.

Can someone help me with the formula for this text file like if there
are
records on the form "null", else the message from above?

 

[Guest]

Good morning Allen,

Thanks for your help. The name of the text box is txtFormMessage, and it is
different from the names of any fields on the form. Yes, the form is bound
(the form RecordSource is bound to a table).

Do I just copy your code "=IIf([Form].[RecordsetClone].[RecordCount] = 0,
"No records", Null)" to the RecordSource of the text box? Do I need to
change anything to in-line with my form? For example, do I change "[Form]"
to my form name?

Thanks.

The square brackets disappear? That's the opposite of what I obverve (i.e.
if I type the expression without the brackets, Access adds them.)

Make sure the Name of this text box is not the same as the name of any field
in the form's Recordset.

This won't work if the form is unbound (i.e. if there is nothing in the
RecordSource property of the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when there
are records on the form. When there are no records (Blank in the Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form", "RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access 2003.

Please help. Thanks.

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records", Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this expression
with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section may go
completely blank. When it does, expressions in the Form Header and Form
Footer sections may not display correctly either. Details and example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

Hello all,

I have a form which I design to filter the records. In the Form Footer
section, I have a text box to show a message "No records for selected
criteria" when there are no records shown on the form based on the
criteria
input by the users.

Can someone help me with the formula for this text file like if there
are
records on the form "null", else the message from above?

 

[Allen Browne]

No changes: copy as-is.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good morning Allen,

Thanks for your help. The name of the text box is txtFormMessage, and it
is
different from the names of any fields on the form. Yes, the form is
bound
(the form RecordSource is bound to a table).

Do I just copy your code "=IIf([Form].[RecordsetClone].[RecordCount] = 0,
"No records", Null)" to the RecordSource of the text box? Do I need to
change anything to in-line with my form? For example, do I change
"[Form]"
to my form name?

Thanks.

The square brackets disappear? That's the opposite of what I obverve
(i.e.
if I type the expression without the brackets, Access adds them.)

Make sure the Name of this text box is not the same as the name of any
field
in the form's Recordset.

This won't work if the form is unbound (i.e. if there is nothing in the
RecordSource property of the form.)

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when
there
are records on the form. When there are no records (Blank in the
Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form",
"RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access
2003.

Please help. Thanks.

:

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records",
Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this expression
with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section may
go
completely blank. When it does, expressions in the Form Header and
Form
Footer sections may not display correctly either. Details and example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

Hello all,

I have a form which I design to filter the records. In the Form
Footer
section, I have a text box to show a message "No records for
selected
criteria" when there are no records shown on the form based on the
criteria
input by the users.

Can someone help me with the formula for this text file like if
there
are
records on the form "null", else the message from above?

 

[Guest]

Allen,

Thanks. I copied your code as-is. I am still getting the error "#Name".
Plus, I don't know it matters, the brackets disappear from the code.

Any idea what causes not to work?

Thanks.

No changes: copy as-is.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good morning Allen,

Thanks for your help. The name of the text box is txtFormMessage, and it
is
different from the names of any fields on the form. Yes, the form is
bound
(the form RecordSource is bound to a table).

Do I just copy your code "=IIf([Form].[RecordsetClone].[RecordCount] = 0,
"No records", Null)" to the RecordSource of the text box? Do I need to
change anything to in-line with my form? For example, do I change
"[Form]"
to my form name?

Thanks.

The square brackets disappear? That's the opposite of what I obverve
(i.e.
if I type the expression without the brackets, Access adds them.)

Make sure the Name of this text box is not the same as the name of any
field
in the form's Recordset.

This won't work if the form is unbound (i.e. if there is nothing in the
RecordSource property of the form.)

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when
there
are records on the form. When there are no records (Blank in the
Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form",
"RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access
2003.

Please help. Thanks.

:

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records",
Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this expression
with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section may
go
completely blank. When it does, expressions in the Form Header and
Form
Footer sections may not display correctly either. Details and example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

Hello all,

I have a form which I design to filter the records. In the Form
Footer
section, I have a text box to show a message "No records for
selected
criteria" when there are no records shown on the form based on the
criteria
input by the users.

Can someone help me with the formula for this text file like if
there
are
records on the form "null", else the message from above?

 

[Allen Browne]

From the code? Are you pasting this in the code window?
It would not work there, and Access would remove the brackets there.

The idea is to paste it into the ControlSource property of the text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks. I copied your code as-is. I am still getting the error "#Name".
Plus, I don't know it matters, the brackets disappear from the code.

Any idea what causes not to work?

Thanks.

No changes: copy as-is.

Good morning Allen,

Thanks for your help. The name of the text box is txtFormMessage, and
it
is
different from the names of any fields on the form. Yes, the form is
bound
(the form RecordSource is bound to a table).

Do I just copy your code "=IIf([Form].[RecordsetClone].[RecordCount] =
0,
"No records", Null)" to the RecordSource of the text box? Do I need to
change anything to in-line with my form? For example, do I change
"[Form]"
to my form name?

Thanks.

:

The square brackets disappear? That's the opposite of what I obverve
(i.e.
if I type the expression without the brackets, Access adds them.)

Make sure the Name of this text box is not the same as the name of any
field
in the form's Recordset.

This won't work if the form is unbound (i.e. if there is nothing in
the
RecordSource property of the form.)

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when
there
are records on the form. When there are no records (Blank in the
Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form",
"RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access
2003.

Please help. Thanks.

:

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records",
Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this
expression
with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section
may
go
completely blank. When it does, expressions in the Form Header and
Form
Footer sections may not display correctly either. Details and
example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

message
Hello all,

I have a form which I design to filter the records. In the Form
Footer
section, I have a text box to show a message "No records for
selected
criteria" when there are no records shown on the form based on
the
criteria
input by the users.

Can someone help me with the formula for this text file like if
there
are
records on the form "null", else the message from above?

 

[Guest]

Good morning Allen,

I pasted into the ControlSource property of the text box. Thanks.

From the code? Are you pasting this in the code window?
It would not work there, and Access would remove the brackets there.

The idea is to paste it into the ControlSource property of the text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks. I copied your code as-is. I am still getting the error "#Name".
Plus, I don't know it matters, the brackets disappear from the code.

Any idea what causes not to work?

Thanks.

No changes: copy as-is.

Good morning Allen,

Thanks for your help. The name of the text box is txtFormMessage, and
it
is
different from the names of any fields on the form. Yes, the form is
bound
(the form RecordSource is bound to a table).

Do I just copy your code "=IIf([Form].[RecordsetClone].[RecordCount] =
0,
"No records", Null)" to the RecordSource of the text box? Do I need to
change anything to in-line with my form? For example, do I change
"[Form]"
to my form name?

Thanks.

:

The square brackets disappear? That's the opposite of what I obverve
(i.e.
if I type the expression without the brackets, Access adds them.)

Make sure the Name of this text box is not the same as the name of any
field
in the form's Recordset.

This won't work if the form is unbound (i.e. if there is nothing in
the
RecordSource property of the form.)

Hi Allen,

Thanks for your help. For some reason, I get an error "#Name?" when
there
are records on the form. When there are no records (Blank in the
Detail
section) on the form, the error disappears.

I also notice that the brackets around the words "Form",
"RecordsetClone"
and "RecordCount" disappear when I save the form. I am using Access
2003.

Please help. Thanks.

:

Try this in the Control Source of your text box:
=IIf([Form].[RecordsetClone].[RecordCount] = 0, "No records",
Null)

There are a couple of cases where that may not work:
a) In Access 2007. For some reason, A2007 can't handle this
expression
with
the square brackets that Access itself adds around these words.

b) If no new records can be added to the form, its Detail section
may
go
completely blank. When it does, expressions in the Form Header and
Form
Footer sections may not display correctly either. Details and
example:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

message
Hello all,

I have a form which I design to filter the records. In the Form
Footer
section, I have a text box to show a message "No records for
selected
criteria" when there are no records shown on the form based on
the
criteria
input by the users.

Can someone help me with the formula for this text file like if
there
are
records on the form "null", else the message from above?