Nested IIF Statements

[znibk]

I am trying to convert a Sales Journal into an Access Database. It has four
columns: sales, long, short, and deposit. Each day, the sales deposit will
either have a deposit where money in the cash drawer equals the amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit]; some days
there is more money in the cash drawer than recorded on the sales ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there is less
money in the cash drawer than recorded on the sales ticket, short, therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I received
#Name? So I tried other calculations and got nowhere. I decided to try the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid Control
Property:control Source and Expression must begin with = sign. So I tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[BruceM]

If you have a form bound to a record source (table or query), and if Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value for
Short and Long is 0, the Nz function may not be necessary, as addition and
subtraction with 0 is no problem. The following will provide some more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could use
one field ([OverUnder]), with a negative value for short and positive for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the expression is
in a text box control source, as with the above. In a calculated query
field, as above, there is no = sign. In terms of syntax, you would need to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not seem that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It has four
columns: sales, long, short, and deposit. Each day, the sales deposit
will
either have a deposit where money in the cash drawer equals the amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit]; some days
there is more money in the cash drawer than recorded on the sales ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to try the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid Control
Property:control Source and Expression must begin with = sign. So I tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[znibk]

BruceM,
Yes, my form is based on a table and Sales, Short, and Long are fields in
it. I had already tried =[Sales]-[Short]+[Long], however, I did not have my
default value set to 0 (hate all those 0's allover) anyway, I went back and
changed the default value and tried again, and it still did not work, I tried
the formula you gave with the Nz and 0, then I thought to myself "Let's try
to add a new field in the query and see if it works." YEA, it did. I changed
the Control Source of the Form to the query, draged the field boxes to the
form and YEA, again, it worked.

Thank you, Thank you, . . . You have no idea how many hours, formulas,
statements, tears, I've lost on this one small problem. Thank you.

Christ's blessings,
znibk

If you have a form bound to a record source (table or query), and if Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value for
Short and Long is 0, the Nz function may not be necessary, as addition and
subtraction with 0 is no problem. The following will provide some more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could use
one field ([OverUnder]), with a negative value for short and positive for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the expression is
in a text box control source, as with the above. In a calculated query
field, as above, there is no = sign. In terms of syntax, you would need to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not seem that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It has four
columns: sales, long, short, and deposit. Each day, the sales deposit
will
either have a deposit where money in the cash drawer equals the amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit]; some days
there is more money in the cash drawer than recorded on the sales ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to try the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid Control
Property:control Source and Expression must begin with = sign. So I tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[BruceM]

Glad to know you got it working. Just to be clear on a point, the default
value applies only to new records. It would not change the value of fields
in existing records.
I generally prefer to have expressions in a query rather than in the control
source for a text box because I find it more convenient simply to bind a
control to the calculated field. Also, I can easily use the calculated
field elsewhere, check its value, etc. However, it shouldn't matter if the
expression is in the control source rather than in the query.
BTW, Short and Long are reserved words in Access. You would do well to
avoid using them as field names. If they are used as field names, they must
always be enclosed in square brackets.
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords

BruceM,
Yes, my form is based on a table and Sales, Short, and Long are fields in
it. I had already tried =[Sales]-[Short]+[Long], however, I did not have
my
default value set to 0 (hate all those 0's allover) anyway, I went back
and
changed the default value and tried again, and it still did not work, I
tried
the formula you gave with the Nz and 0, then I thought to myself "Let's
try
to add a new field in the query and see if it works." YEA, it did. I
changed
the Control Source of the Form to the query, draged the field boxes to the
form and YEA, again, it worked.

Thank you, Thank you, . . . You have no idea how many hours, formulas,
statements, tears, I've lost on this one small problem. Thank you.

Christ's blessings,
znibk

If you have a form bound to a record source (table or query), and if
Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query
field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value for
Short and Long is 0, the Nz function may not be necessary, as addition
and
subtraction with 0 is no problem. The following will provide some more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could
use
one field ([OverUnder]), with a negative value for short and positive for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the expression
is
in a text box control source, as with the above. In a calculated query
field, as above, there is no = sign. In terms of syntax, you would need
to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not seem
that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It has
four
columns: sales, long, short, and deposit. Each day, the sales deposit
will
either have a deposit where money in the cash drawer equals the amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit]; some
days
there is more money in the cash drawer than recorded on the sales
ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of
the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to try
the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid Control
Property:control Source and Expression must begin with = sign. So I
tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[znibk]

Bruce,

Thanks for additional information. I added the website to my Microsoft Help
Folder in my Favorite's group; and will change the field name to More and
Lesss.

Incidently, do you know the answer to these questions?
Sometimes when I create a form or report, I get the Automatic Number rather
than the text version from a look-up field. How can I elimate that? What I've
been doing is changing the text box in the Report or Form to a Combo box, but
sometimes that takes many tries.

I want to import or link my Excel monthly files to an existing Access table.
I made sure all the field names were the same and everything in the exact
order. I lost the text references to my contributors. In my Access Table,
Contributor is a Long Interger, since I used the Lookup Wizard. In my Excel
Table, I use a drop-down list so all contributor names are the same.

I'm sure I have more questions for this simple database I'm trying to
create. It is so frustrating because I spent five years working with Access
and designing some pretty complex databases but, it has now been almost three
years, and I have forgotten SO MANY things.

znibk

Glad to know you got it working. Just to be clear on a point, the default
value applies only to new records. It would not change the value of fields
in existing records.
I generally prefer to have expressions in a query rather than in the control
source for a text box because I find it more convenient simply to bind a
control to the calculated field. Also, I can easily use the calculated
field elsewhere, check its value, etc. However, it shouldn't matter if the
expression is in the control source rather than in the query.
BTW, Short and Long are reserved words in Access. You would do well to
avoid using them as field names. If they are used as field names, they must
always be enclosed in square brackets.
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords

BruceM,
Yes, my form is based on a table and Sales, Short, and Long are fields in
it. I had already tried =[Sales]-[Short]+[Long], however, I did not have
my
default value set to 0 (hate all those 0's allover) anyway, I went back
and
changed the default value and tried again, and it still did not work, I
tried
the formula you gave with the Nz and 0, then I thought to myself "Let's
try
to add a new field in the query and see if it works." YEA, it did. I
changed
the Control Source of the Form to the query, draged the field boxes to the
form and YEA, again, it worked.

Thank you, Thank you, . . . You have no idea how many hours, formulas,
statements, tears, I've lost on this one small problem. Thank you.

Christ's blessings,
znibk

If you have a form bound to a record source (table or query), and if
Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query
field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value for
Short and Long is 0, the Nz function may not be necessary, as addition
and
subtraction with 0 is no problem. The following will provide some more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could
use
one field ([OverUnder]), with a negative value for short and positive for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the expression
is
in a text box control source, as with the above. In a calculated query
field, as above, there is no = sign. In terms of syntax, you would need
to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not seem
that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It has
four
columns: sales, long, short, and deposit. Each day, the sales deposit
will
either have a deposit where money in the cash drawer equals the amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit]; some
days
there is more money in the cash drawer than recorded on the sales
ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of
the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to try
the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid Control
Property:control Source and Expression must begin with = sign. So I
tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[BruceM]

I don't know what you mean when you say that you "get the Automatic Number
rather than the text version from a look-up field". However, I can tell you
that lookup fields can lead to problems. The following link provides more
information:
http://www.mvps.org/access/lookupfields.htm
For the rest of your questions, there are several more points I do not
understand. Please explain more about how it takes several tries to change
a text box to a combo box. BTW, there's no point I can see to a combo box
in a report, since a report is not interactive.
I have little experience linking or importing from Excel except as a way to
populate a table initially. I hope somebody with more experience in this
matter is following this thread, and can offer a suggestion.
Another point about all of this is that an examination of your database
structure may prove helpful. I expect the customer number should be stored
in a table with financial data (the sales log or whatever), but that
customer information should be stored in a separate, related table. Same
for salespeople, who should have their own table.
Note that a lookup table and lookup fields are very different things.
Looking up customers in order to store the customer number from a related
table is good design practice.

Bruce,

Thanks for additional information. I added the website to my Microsoft
Help
Folder in my Favorite's group; and will change the field name to More and
Lesss.

Incidently, do you know the answer to these questions?
Sometimes when I create a form or report, I get the Automatic Number
rather
than the text version from a look-up field. How can I elimate that? What
I've
been doing is changing the text box in the Report or Form to a Combo box,
but
sometimes that takes many tries.

I want to import or link my Excel monthly files to an existing Access
table.
I made sure all the field names were the same and everything in the exact
order. I lost the text references to my contributors. In my Access Table,
Contributor is a Long Interger, since I used the Lookup Wizard. In my
Excel
Table, I use a drop-down list so all contributor names are the same.

I'm sure I have more questions for this simple database I'm trying to
create. It is so frustrating because I spent five years working with
Access
and designing some pretty complex databases but, it has now been almost
three
years, and I have forgotten SO MANY things.

znibk

Glad to know you got it working. Just to be clear on a point, the
default
value applies only to new records. It would not change the value of
fields
in existing records.
I generally prefer to have expressions in a query rather than in the
control
source for a text box because I find it more convenient simply to bind a
control to the calculated field. Also, I can easily use the calculated
field elsewhere, check its value, etc. However, it shouldn't matter if
the
expression is in the control source rather than in the query.
BTW, Short and Long are reserved words in Access. You would do well to
avoid using them as field names. If they are used as field names, they
must
always be enclosed in square brackets.
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords

BruceM,
Yes, my form is based on a table and Sales, Short, and Long are fields
in
it. I had already tried =[Sales]-[Short]+[Long], however, I did not
have
my
default value set to 0 (hate all those 0's allover) anyway, I went back
and
changed the default value and tried again, and it still did not work, I
tried
the formula you gave with the Nz and 0, then I thought to myself "Let's
try
to add a new field in the query and see if it works." YEA, it did. I
changed
the Control Source of the Form to the query, draged the field boxes to
the
form and YEA, again, it worked.

Thank you, Thank you, . . . You have no idea how many hours, formulas,
statements, tears, I've lost on this one small problem. Thank you.

Christ's blessings,
znibk

:

If you have a form bound to a record source (table or query), and if
Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query
field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation
won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value
for
Short and Long is 0, the Nz function may not be necessary, as addition
and
subtraction with 0 is no problem. The following will provide some
more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could
use
one field ([OverUnder]), with a negative value for short and positive
for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the
expression
is
in a text box control source, as with the above. In a calculated
query
field, as above, there is no = sign. In terms of syntax, you would
need
to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not
seem
that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It
has
four
columns: sales, long, short, and deposit. Each day, the sales
deposit
will
either have a deposit where money in the cash drawer equals the
amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit];
some
days
there is more money in the cash drawer than recorded on the sales
ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there
is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of
the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to
try
the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid
Control
Property:control Source and Expression must begin with = sign. So I
tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[znibk]

Good morning BruceM,

In trying to be exact, I keep loosing everything I've typed because I have
to click the Refresh button. Last night, I thought I'd remember to copy and
paste into this when I re-did it this morning, but of course, I slept. Here
goes:

The “Automatic Number†referred to below, is the FieldID. I use the query to
build the report. Sometimes, the FieldID AutoNumber, ie, “13,†shows in the
Report rather than the entered information, ie, “Joe Green.†I pull the Field
Name from the Query. Incidentally, when I run the Query, “Joe Green†shows,
not “13.â€

And to complicate matters, in this report, two other problems are cropping
up:
1) The “Description Footer†and the “Whoes Footer†are appearing in the
report above the headers at the top of the page!
2) I have a few “0.00†appearing throughout the report. I thought I had all
default values set to “0,†but I guess when I changed the format to
“Currency†I don’t. I tried the following two expressions in the field text
box and get an Invalid Control Source and Circular Reference message when I
try to get out of the text box. I tried =IIF(Is Null([Client Check Amount]),â€
“,[Client Check Amount]) and when that did not work, the comment you’d made
earlier about the Null field, I put =IIF(Nz(Client Check Amount],0),â€
“,[Client Check Amount])

I really you have a lot of patience and can help me out again.

I don't know what you mean when you say that you "get the Automatic Number
rather than the text version from a look-up field". However, I can tell you
that lookup fields can lead to problems. The following link provides more
information:
http://www.mvps.org/access/lookupfields.htm
For the rest of your questions, there are several more points I do not
understand. Please explain more about how it takes several tries to change
a text box to a combo box. BTW, there's no point I can see to a combo box
in a report, since a report is not interactive.
I have little experience linking or importing from Excel except as a way to
populate a table initially. I hope somebody with more experience in this
matter is following this thread, and can offer a suggestion.
Another point about all of this is that an examination of your database
structure may prove helpful. I expect the customer number should be stored
in a table with financial data (the sales log or whatever), but that
customer information should be stored in a separate, related table. Same
for salespeople, who should have their own table.
Note that a lookup table and lookup fields are very different things.
Looking up customers in order to store the customer number from a related
table is good design practice.

Bruce,

Thanks for additional information. I added the website to my Microsoft
Help
Folder in my Favorite's group; and will change the field name to More and
Lesss.

Incidently, do you know the answer to these questions?
Sometimes when I create a form or report, I get the Automatic Number
rather
than the text version from a look-up field. How can I elimate that? What
I've
been doing is changing the text box in the Report or Form to a Combo box,
but
sometimes that takes many tries.

I want to import or link my Excel monthly files to an existing Access
table.
I made sure all the field names were the same and everything in the exact
order. I lost the text references to my contributors. In my Access Table,
Contributor is a Long Interger, since I used the Lookup Wizard. In my
Excel
Table, I use a drop-down list so all contributor names are the same.

I'm sure I have more questions for this simple database I'm trying to
create. It is so frustrating because I spent five years working with
Access
and designing some pretty complex databases but, it has now been almost
three
years, and I have forgotten SO MANY things.

znibk

Glad to know you got it working. Just to be clear on a point, the
default
value applies only to new records. It would not change the value of
fields
in existing records.
I generally prefer to have expressions in a query rather than in the
control
source for a text box because I find it more convenient simply to bind a
control to the calculated field. Also, I can easily use the calculated
field elsewhere, check its value, etc. However, it shouldn't matter if
the
expression is in the control source rather than in the query.
BTW, Short and Long are reserved words in Access. You would do well to
avoid using them as field names. If they are used as field names, they
must
always be enclosed in square brackets.
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords

BruceM,
Yes, my form is based on a table and Sales, Short, and Long are fields
in
it. I had already tried =[Sales]-[Short]+[Long], however, I did not
have
my
default value set to 0 (hate all those 0's allover) anyway, I went back
and
changed the default value and tried again, and it still did not work, I
tried
the formula you gave with the Nz and 0, then I thought to myself "Let's
try
to add a new field in the query and see if it works." YEA, it did. I
changed
the Control Source of the Form to the query, draged the field boxes to
the
form and YEA, again, it worked.

Thank you, Thank you, . . . You have no idea how many hours, formulas,
statements, tears, I've lost on this one small problem. Thank you.

Christ's blessings,
znibk

:

If you have a form bound to a record source (table or query), and if
Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query
field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation
won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value
for
Short and Long is 0, the Nz function may not be necessary, as addition
and
subtraction with 0 is no problem. The following will provide some
more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could
use
one field ([OverUnder]), with a negative value for short and positive
for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the
expression
is
in a text box control source, as with the above. In a calculated
query
field, as above, there is no = sign. In terms of syntax, you would
need
to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not
seem
that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It
has
four
columns: sales, long, short, and deposit. Each day, the sales
deposit
will
either have a deposit where money in the cash drawer equals the
amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit];
some
days
there is more money in the cash drawer than recorded on the sales
ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there
is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of
the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to
try
the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid
Control
Property:control Source and Expression must begin with = sign. So I
tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[znibk]

Good afternoon BruceM,

I think I should have asked another couple of questions before thinking I
had everything going well. I'm gathering from others that it is better to
base my form on a table than a query. So, I changed the control for my Income
form back to my tblIncome. But, I cannot get the field name I created for
Deposit: [Sales]-Nz(Less],0) +Nz([More],0) onto the form and I have tried
using the query builder to the right of Control Source in design view of the
form. Therefore, I thought I would ask what you meant by

"I find it more convenient simply to bind a control to the calculated field."

and ask how I do that.

Hope you are still checking on me every now and then and can help me out
again. If not, I hope someone out there can help me out because I don't want
to double post.

znibk

Glad to know you got it working. Just to be clear on a point, the default
value applies only to new records. It would not change the value of fields
in existing records.
I generally prefer to have expressions in a query rather than in the control
source for a text box because I find it more convenient simply to bind a
control to the calculated field. Also, I can easily use the calculated
field elsewhere, check its value, etc. However, it shouldn't matter if the
expression is in the control source rather than in the query.
BTW, Short and Long are reserved words in Access. You would do well to
avoid using them as field names. If they are used as field names, they must
always be enclosed in square brackets.
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords

BruceM,
Yes, my form is based on a table and Sales, Short, and Long are fields in
it. I had already tried =[Sales]-[Short]+[Long], however, I did not have
my
default value set to 0 (hate all those 0's allover) anyway, I went back
and
changed the default value and tried again, and it still did not work, I
tried
the formula you gave with the Nz and 0, then I thought to myself "Let's
try
to add a new field in the query and see if it works." YEA, it did. I
changed
the Control Source of the Form to the query, draged the field boxes to the
form and YEA, again, it worked.

Thank you, Thank you, . . . You have no idea how many hours, formulas,
statements, tears, I've lost on this one small problem. Thank you.

Christ's blessings,
znibk

If you have a form bound to a record source (table or query), and if
Sales,
Short, and Long are fields in the table (or query), you could put the
following into the control source of an unbound text box:
=[Sales]-[Short]+[Long]
If the form is based on a query, you could create a calculated query
field.
At the top of a blank column in query design view, something like:
DayTotal: [Sales]-[Short]+[Long]
Or maybe you would need to use the Nz function, as the calculation won't
work with Nulls in any of the fields:
DayTotal: [Sales] - Nz([Short],0) + Nz([Long],0)
Keep in mind that 0 and Null are not the same. If the default value for
Short and Long is 0, the Nz function may not be necessary, as addition
and
subtraction with 0 is no problem. The following will provide some more
information about Nulls.
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
Consider that you don't really need a Short and Long field. You could
use
one field ([OverUnder]), with a negative value for short and positive for
long. Your expression would be:
DayTotal: [Sales] + Nz([OverUnder],0)

In a nested IIf statement you only use the = sign once if the expression
is
in a text box control source, as with the above. In a calculated query
field, as above, there is no = sign. In terms of syntax, you would need
to
do something like:
=IIf([Long] Is Null And [Short] Is Null,etc. However, it does not seem
that
you need an IIf statement in this situation.

I am trying to convert a Sales Journal into an Access Database. It has
four
columns: sales, long, short, and deposit. Each day, the sales deposit
will
either have a deposit where money in the cash drawer equals the amount
recorded on the sales ticket, therefore: [sales]=[salesdeposit]; some
days
there is more money in the cash drawer than recorded on the sales
ticket,
long, therefore: [sales]+[long]=[salesdeposit]; and some days there is
less
money in the cash drawer than recorded on the sales ticket, short,
therefore:
[sales]-[short]=[salesdeposit].

This information needs to be on the Form so immediate calculation of
the
deposit is made (as if it was in an Excel Spreadsheet) I tried
=salesdeposit!Sales-[Short]+[Long] and when I went to form view, I
received
#Name? So I tried other calculations and got nowhere. I decided to try
the
IIF Statement.

I tried various IIF Statements like: =IIF(IsNull([Long]) And
([Short]),([Sales]),=IIF(Is
Null([Long]),([Sales])-([Short]),=IIf(IsNull([short]),([sales])+([long])---With
this expression, I get a message which says I have an Invalid Control
Property:control Source and Expression must begin with = sign. So I
tried
=IIF(Is Null([Long]),([sales])-([short])=([salesdeposit]),If(Is
Null([Short]),[(sales])+([Long])=([salesdeposit]),([sales])=([salesdeposit]),([sales](=([salesdeposit])

What am I doing wrong?

 

[John W. Vinson]

Good afternoon BruceM,

I think I should have asked another couple of questions before thinking I
had everything going well. I'm gathering from others that it is better to
base my form on a table than a query. So, I changed the control for my Income
form back to my tblIncome. But, I cannot get the field name I created for
Deposit: [Sales]-Nz(Less],0) +Nz([More],0) onto the form and I have tried
using the query builder to the right of Control Source in design view of the
form. Therefore, I thought I would ask what you meant by

"I find it more convenient simply to bind a control to the calculated field."

and ask how I do that.

Hope you are still checking on me every now and then and can help me out
again. If not, I hope someone out there can help me out because I don't want
to double post.

pmfji but...

Your concern that forms should not be based on queries is misplaced. Almost
all the forms in my database are based on queries!

What you want to avoid is big, complex, multitable queries (at least if you
want to update data using the form). A one-table query with some calculated
fields is perfectly reasonable and standard.

John W. Vinson [MVP]

 

[znibk]

Good morning John,

tfi.....

I had to think about the "pmfi," I don't text message. No wonder it can be
done so rappidly. I shall remember what you said and use queries as needed.
Incidently, my forms are working well tty. And tfkaeom.
Your help is always appreciated. God bless.
K

Good afternoon BruceM,

I think I should have asked another couple of questions before thinking I
had everything going well. I'm gathering from others that it is better to
base my form on a table than a query. So, I changed the control for my Income
form back to my tblIncome. But, I cannot get the field name I created for
Deposit: [Sales]-Nz(Less],0) +Nz([More],0) onto the form and I have tried
using the query builder to the right of Control Source in design view of the
form. Therefore, I thought I would ask what you meant by

"I find it more convenient simply to bind a control to the calculated field."

and ask how I do that.

Hope you are still checking on me every now and then and can help me out
again. If not, I hope someone out there can help me out because I don't want
to double post.

pmfji but...

Your concern that forms should not be based on queries is misplaced. Almost
all the forms in my database are based on queries!

What you want to avoid is big, complex, multitable queries (at least if you
want to update data using the form). A one-table query with some calculated
fields is perfectly reasonable and standard.

John W. Vinson [MVP]

 

[znibk]

Good morning again,

Would you please also look at "Summing in a form" original post 5/15/07 with
16 posts. I posted a question in someone elses thread, and the "Andy" who has
been helping the ?p has not replied to my post. Would you please take a look
at it.

I had been wondering the same thing about my beginning balance and ending
balance. As always, thank you so very, very much.

k

Good afternoon BruceM,

I think I should have asked another couple of questions before thinking I
had everything going well. I'm gathering from others that it is better to
base my form on a table than a query. So, I changed the control for my Income
form back to my tblIncome. But, I cannot get the field name I created for
Deposit: [Sales]-Nz(Less],0) +Nz([More],0) onto the form and I have tried
using the query builder to the right of Control Source in design view of the
form. Therefore, I thought I would ask what you meant by

"I find it more convenient simply to bind a control to the calculated field."

and ask how I do that.

Hope you are still checking on me every now and then and can help me out
again. If not, I hope someone out there can help me out because I don't want
to double post.

pmfji but...

Your concern that forms should not be based on queries is misplaced. Almost
all the forms in my database are based on queries!

What you want to avoid is big, complex, multitable queries (at least if you
want to update data using the form). A one-table query with some calculated
fields is perfectly reasonable and standard.

John W. Vinson [MVP]