query criteria

[bwjohnson1]

I want to know how to make and =IF function for my 'Amount' column that says,
"If the 'Account' value = 1110, leave the 'Amount' field as is. But if the
'Account' value is any other value, change the sign of related 'Amount' to be
the opposite (-/+) sign." How can I do this?

 

[BruceM]

This should produce the desired result in a new calculated query field. Use
whatever name you like instead of NewField:

NewField: IIf([Account] = 1110,[Amount],[Amount]*-1)

The subject of your message mentions query criteria. I don't see how your
question is about query criteria, so I am guessing a little about what
exactly you are trying to do.

 

[John W. Vinson]

I want to know how to make and =IF function for my 'Amount' column that says,
"If the 'Account' value = 1110, leave the 'Amount' field as is. But if the
'Account' value is any other value, change the sign of related 'Amount' to be
the opposite (-/+) sign." How can I do this?

Do you want to just display the negative in a query, for use on a form or
report? Or do you want to permanently edit and replace the value in Amount?

If you just want to display it, use a Query with a calculated field; in a
vacant field cell type

NewAmount: IIF([Account] = 1110, -[Amount], [Amount])

If you want to permanently overwrite it, BACK UP YOUR DATABASE and run an
update query; use a criterion on Account of

=1110

Change the query to an Update query and update Amount to

- [Amount]

 

[bwjohnson1]

Thanks for your help Bruce.

It sounds like you know quite a bit so I am going to ask a couple more
things if you don't mind.

So I have a database that tracks the transactions that hit our bank accounts
and categorizes them into common accounts. I want to use this database to
have a query that will give me Journal Entries as the output.

I created a table that is meant to function as a template for creating JE's.
It has all the static data for the typical transactions that we create
month-end JE's for (shown as the GL Account column above). Each row of the
table has a lookup column for the common account of the transaction (BCBS).
Also, every transaction is assigned to a common account.

GL Account Common Account Transaction AMT Sum
1110 BCBS -$100
6500 BCBS $100

One problem is that each common account, like Blue Cross Blue Shields
(BCBS), has many transactions each month but we only want to do one JE for
the monthly total.

So I need a query to sum the transactions during the month for the BCBS
common account (and all of the other accounts). I already have the parameter
as a date and it gives me a JE for each transaction during the specified
month. I want the transaction amounts to sum for each common account. And I
want the sign to change to distinguish between debits and credits, even
though they both stem from one transaction.

Any suggestions would help.

Thanks,

Brian

This should produce the desired result in a new calculated query field. Use
whatever name you like instead of NewField:

NewField: IIf([Account] = 1110,[Amount],[Amount]*-1)

The subject of your message mentions query criteria. I don't see how your
question is about query criteria, so I am guessing a little about what
exactly you are trying to do.

I want to know how to make and =IF function for my 'Amount' column that
says,
"If the 'Account' value = 1110, leave the 'Amount' field as is. But if the
'Account' value is any other value, change the sign of related 'Amount' to
be
the opposite (-/+) sign." How can I do this?

 

[BruceM]

John Vinson's suggestion was a simpler solution, but in the end his
suggestion and mine do the same thing.

I might not have responded to the latest questions if they were in their own
thread, but since I'm here I'll take a shot at it.

First, it is generally best to avoid the use of lookup fields, if that is
what you are describing. MOre here:
http://www.mvps.org/access/lookupfields.htm

A table is for storing data and nothing else. I don't know what you mean by
a table that functions as a template, or what you mean by "static data".

If you want to total the sum for, say, records with GL Account month by
month, you can create a query with a calculated field based on a date field:

TransactionMonth: Format([DateField],"mmm")

Create a query with the fields GL_Account, CommonAccount, and NewField as
described in the previous post, and the new TransactionMonth field. Click
the Totals button on the query toolbar to create a Totals query. Group by
TransactionMonth, CommanAccount, GL_Account; and Sum by NewField.

If all you need is to present the data, a report's Sorting and Grouping
capabilities may give you what you need.

Without a better understanding of your database's structure and
relatiionships it is difficult to be more specific.

Thanks for your help Bruce.

It sounds like you know quite a bit so I am going to ask a couple more
things if you don't mind.

So I have a database that tracks the transactions that hit our bank
accounts
and categorizes them into common accounts. I want to use this database to
have a query that will give me Journal Entries as the output.

I created a table that is meant to function as a template for creating
JE's.
It has all the static data for the typical transactions that we create
month-end JE's for (shown as the GL Account column above). Each row of
the
table has a lookup column for the common account of the transaction
(BCBS).
Also, every transaction is assigned to a common account.

GL Account Common Account Transaction AMT Sum
1110 BCBS -$100
6500 BCBS $100

One problem is that each common account, like Blue Cross Blue Shields
(BCBS), has many transactions each month but we only want to do one JE for
the monthly total.

So I need a query to sum the transactions during the month for the BCBS
common account (and all of the other accounts). I already have the
parameter
as a date and it gives me a JE for each transaction during the specified
month. I want the transaction amounts to sum for each common account.
And I
want the sign to change to distinguish between debits and credits, even
though they both stem from one transaction.

Any suggestions would help.

Thanks,

Brian

This should produce the desired result in a new calculated query field.
Use
whatever name you like instead of NewField:

NewField: IIf([Account] = 1110,[Amount],[Amount]*-1)

The subject of your message mentions query criteria. I don't see how
your
question is about query criteria, so I am guessing a little about what
exactly you are trying to do.

I want to know how to make and =IF function for my 'Amount' column that
says,
"If the 'Account' value = 1110, leave the 'Amount' field as is. But if
the
'Account' value is any other value, change the sign of related 'Amount'
to
be
the opposite (-/+) sign." How can I do this?