How to make Access ignore missing data (-999)

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

Guest

We enter missing data as -999. In a scoring query, we noticed that it was
written such that it ignores missing data....i.e., labels it as 0.

MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Currently receiving help",1,0))

However we need to know that data is missing (have it left blank) and not
have it labeled as zero. Later in the query, we sum domains by adding up
values from the individual items, so we can not have the missing data come up
as -999 or 0, just as nothing.
Is there a way to do this?
 
OK. Your If statement is only giving you three options,.
1 if "sometimes",
1 if "currently receiving help"
0 if it is anything else.

If it is simple as that, change it to:


MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT
2_0 PAT]!P1T111o="Currently receiving help",1,"BLANK"))

You can of course, change "BLANK" to anything you wish, including simple
"", which will produce a true blank space.

Otherwise, more information will be needed.
 
When I modify the If statment to read:

MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Currently receiving help",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Never",0,"BLANK")))
I get #errors in the data sheet view anywhere there are blanks.

Phil said:
OK. Your If statement is only giving you three options,.
1 if "sometimes",
1 if "currently receiving help"
0 if it is anything else.

If it is simple as that, change it to:


MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT
2_0 PAT]!P1T111o="Currently receiving help",1,"BLANK"))

You can of course, change "BLANK" to anything you wish, including simple
"", which will produce a true blank space.

Otherwise, more information will be needed.




We enter missing data as -999. In a scoring query, we noticed that it was
written such that it ignores missing data....i.e., labels it as 0.

MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Currently receiving help",1,0))

However we need to know that data is missing (have it left blank) and not
have it labeled as zero. Later in the query, we sum domains by adding up
values from the individual items, so we can not have the missing data come up
as -999 or 0, just as nothing.
Is there a way to do this?
Click to expand...
Click to expand...
 
Question. Yuo wish the following results. True?
Sometimes=1
Currently=1
Blank=blank
Other=0?

If so, try:

IIf(isnull([T1 P1 PAT 2_0 PAT]!P1T111o),"BLANK",MeSt_11o: IIf([T1 P1 PAT
2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Currently receiving help",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Never",0,"Other")))







When I modify the If statment to read:

MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Currently receiving help",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Never",0,"BLANK")))
I get #errors in the data sheet view anywhere there are blanks.

:

OK. Your If statement is only giving you three options,.
1 if "sometimes",
1 if "currently receiving help"
0 if it is anything else.

If it is simple as that, change it to:


MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT
2_0 PAT]!P1T111o="Currently receiving help",1,"BLANK"))

You can of course, change "BLANK" to anything you wish, including simple
"", which will produce a true blank space.

Otherwise, more information will be needed.




We enter missing data as -999. In a scoring query, we noticed that it was
written such that it ignores missing data....i.e., labels it as 0.

MeSt_11o: IIf([T1 P1 PAT 2_0 PAT]!P1T111o="Sometimes",1,IIf([T1 P1 PAT 2_0
PAT]!P1T111o="Currently receiving help",1,0))

However we need to know that data is missing (have it left blank) and not
have it labeled as zero. Later in the query, we sum domains by adding up
values from the individual items, so we can not have the missing data come up
as -999 or 0, just as nothing.
Is there a way to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top