A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Translate if statement in query



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 09:19 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Translate if statement in query

The query has table [Demand] twice, with second instance having suffix _1 to
appear as [Demand_1] and they both check for nulls.
The same could be accomplished like this --
New Request: Nz([Demand]![Average requested],0)

Old Request: Nz(IsNull([Demand_1]![Average requested],0)


--
Build a little, test a little.


"Jazz" wrote:

I took both of these if statements from a query in an access database and I
am trying to interpret them.

New Request: IIf(IsNull([Demand]![Average requested]),0,[Demand]![Average
requested])

Old Request: IIf(IsNull([Demand_1]![Average
requested]),0,[Demand_1]![Average requested])

My interpretation is that each statement in the query is giving a field name
to the field and then doing something or nothing depending on the data in
TABLE: Demand / FIELD: Average requested, but I am not sure what. Are you
help to help me translate these statements completely?

  #2  
Old May 27th, 2010, 09:23 PM posted to microsoft.public.access.queries
Jazz
external usenet poster
 
Posts: 53
Default Translate if statement in query

Thank you Sir. Your feedback has been very helpful to me!

"KARL DEWEY" wrote:

a null value means there is no valid data or the data is unknown.

A null is like a vacuum. There is no data, valid, invalid, or unknown.

If you add a null to something you the results is a null.

I am assuming if the result is null than the record will display a 0, however if the result is not null the record will display the number in the Average requested field. Am I correct?

Correct.

do you know what adding the suffix to Demand so it becomes Demand_1 accomplishes?

Access does it automatically when in design view you place the table twice
in the space above the grid.
Some time you want to compare one record to another so you use the table
twice in the query so as to pull different records at the same time in order
to do the comparrison.
Also you may want to compare summaries (totals, averages, minimums,
maximums, etc.) to some individual record.

--
Build a little, test a little.


"Jazz" wrote:

Thank you for the feedback; I appreciate your help and the suggested
modifications, they are much better. I still have some questions that I need
help clarifying.

I understand that both statements are checking for null values and a null
value means there is no valid data or the data is unknown. What I am trying
to understand is what the statement does if the result is null and what the
statement does if the result is not null. I am assuming if the result is
null than the record will display a 0, however if the result is not null the
record will display the number in the Average requested field. Am I correct?

Also, do you know what adding the suffix to Demand so it becomes Demand_1
accomplishes?


"KARL DEWEY" wrote:

The query has table [Demand] twice, with second instance having suffix _1 to
appear as [Demand_1] and they both check for nulls.
The same could be accomplished like this --
New Request: Nz([Demand]![Average requested],0)

Old Request: Nz(IsNull([Demand_1]![Average requested],0)


--
Build a little, test a little.


"Jazz" wrote:

I took both of these if statements from a query in an access database and I
am trying to interpret them.

New Request: IIf(IsNull([Demand]![Average requested]),0,[Demand]![Average
requested])

Old Request: IIf(IsNull([Demand_1]![Average
requested]),0,[Demand_1]![Average requested])

My interpretation is that each statement in the query is giving a field name
to the field and then doing something or nothing depending on the data in
TABLE: Demand / FIELD: Average requested, but I am not sure what. Are you
help to help me translate these statements completely?

  #3  
Old May 27th, 2010, 09:48 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Translate if statement in query

a null value means there is no valid data or the data is unknown.
A null is like a vacuum. There is no data, valid, invalid, or unknown.

If you add a null to something you the results is a null.

I am assuming if the result is null than the record will display a 0, however if the result is not null the record will display the number in the Average requested field. Am I correct?

Correct.

do you know what adding the suffix to Demand so it becomes Demand_1 accomplishes?

Access does it automatically when in design view you place the table twice
in the space above the grid.
Some time you want to compare one record to another so you use the table
twice in the query so as to pull different records at the same time in order
to do the comparrison.
Also you may want to compare summaries (totals, averages, minimums,
maximums, etc.) to some individual record.

--
Build a little, test a little.


"Jazz" wrote:

Thank you for the feedback; I appreciate your help and the suggested
modifications, they are much better. I still have some questions that I need
help clarifying.

I understand that both statements are checking for null values and a null
value means there is no valid data or the data is unknown. What I am trying
to understand is what the statement does if the result is null and what the
statement does if the result is not null. I am assuming if the result is
null than the record will display a 0, however if the result is not null the
record will display the number in the Average requested field. Am I correct?

Also, do you know what adding the suffix to Demand so it becomes Demand_1
accomplishes?


"KARL DEWEY" wrote:

The query has table [Demand] twice, with second instance having suffix _1 to
appear as [Demand_1] and they both check for nulls.
The same could be accomplished like this --
New Request: Nz([Demand]![Average requested],0)

Old Request: Nz(IsNull([Demand_1]![Average requested],0)


--
Build a little, test a little.


"Jazz" wrote:

I took both of these if statements from a query in an access database and I
am trying to interpret them.

New Request: IIf(IsNull([Demand]![Average requested]),0,[Demand]![Average
requested])

Old Request: IIf(IsNull([Demand_1]![Average
requested]),0,[Demand_1]![Average requested])

My interpretation is that each statement in the query is giving a field name
to the field and then doing something or nothing depending on the data in
TABLE: Demand / FIELD: Average requested, but I am not sure what. Are you
help to help me translate these statements completely?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.