Discussion on:
View:
Show:
Have you ever run into problems caused by someone who neglected to accommodate null values? Are there any additional situations where you've had to work around or proactively handle null values to prevent errors?
It's just that easy.
Darn, now I'm going to have to do some more homework. And I thought I had explained that, to somebody, once.
In the old days (even before M$ exclude) we solve that with 0.0 and with a '+' or '-' before a number. Later with a '$' for monitary sum.
The rest of that solution is some where in my old memory bank poluted with a lot of coffee. It will come back to me though. Was this a test of some sort? Geeze I think I seen this some where else.
Darn, now I'm going to have to do some more homework. And I thought I had explained that, to somebody, once.
In the old days (even before M$ exclude) we solve that with 0.0 and with a '+' or '-' before a number. Later with a '$' for monitary sum.
The rest of that solution is some where in my old memory bank poluted with a lot of coffee. It will come back to me though. Was this a test of some sort? Geeze I think I seen this some where else.
Where not(source.field) is null
does not make any sort of sense to me
Where not source.field is null
Or
Where not (source.field is null)
in proper SQL you might find a use for the NullIf function which is a sort of inverse of IsNull
NullIf(Source.Field,0)
useful if someone in the past got round a nullability problem by putting rubbish in the field.
does not make any sort of sense to me
Where not source.field is null
Or
Where not (source.field is null)
in proper SQL you might find a use for the NullIf function which is a sort of inverse of IsNull
NullIf(Source.Field,0)
useful if someone in the past got round a nullability problem by putting rubbish in the field.
That's not designed to be code, but rather the parens are used to indicate that a feild name is inserted in that location within the line of code. The line of code would read as either of your options--though the latter would likely make it cranky.
Access generates SQL code, but it's not the cleanest line.
Access generates SQL code, but it's not the cleanest line.
If you build a query in Access and put 'Is Not Null' in the criteria row under the field you want to exclude nulls, Access builds the SQL statement like this:
SELECT Fee_Account
FROM tbl_Account
WHERE (((Fee_Account) Is Not Null));
Not saying the other way won't work, but this does and seems to make sense. I don't know if it proper or not.
SELECT Fee_Account
FROM tbl_Account
WHERE (((Fee_Account) Is Not Null));
Not saying the other way won't work, but this does and seems to make sense. I don't know if it proper or not.
I have to compare two identical tables for indifferences but the NULLS are not being found. Does this logic make sense of should it be coded differently?
WHERE ((([TABL_1]![TEMPB])[TABL_2]![TEMPB])) OR ((([TABL_1]![TEMPB]) Is Not Null) AND (([TABL_2]![TEMPB]) Is Null));
WHERE ((([TABL_1]![TEMPB])[TABL_2]![TEMPB])) OR ((([TABL_1]![TEMPB]) Is Not Null) AND (([TABL_2]![TEMPB]) Is Null));
as null = null then it gets messy
Where (NOT (f1 is null) and (f2 is null))
or ((f1 is null) and (f2 is not null))
or ((f1 is not null) and (f2 is null))
or ((f1 is not null) and (f2 is not null) and (f1 f2))
Another way is to use the isnull function, it's a bit naughty depends on how whatever uses the data treats nulls.
for instance if nulls in an integer field are treated as zeros in the application then
isNull(f1,0) isnull(f2,0) would do the job, if you want to say the NULL = 0 !
you could use another value instead of zero e.g. -1 given that - 1 should never be in the column.
HtHs
Where (NOT (f1 is null) and (f2 is null))
or ((f1 is null) and (f2 is not null))
or ((f1 is not null) and (f2 is null))
or ((f1 is not null) and (f2 is not null) and (f1 f2))
Another way is to use the isnull function, it's a bit naughty depends on how whatever uses the data treats nulls.
for instance if nulls in an integer field are treated as zeros in the application then
isNull(f1,0) isnull(f2,0) would do the job, if you want to say the NULL = 0 !
you could use another value instead of zero e.g. -1 given that - 1 should never be in the column.
HtHs
Have a field with no value can I use IsNull to highlight that field & use conditional format
IsNull([SomeField],'Unknown')
in a condition might as well just use
([SomeField] is null)
If it's you want 'unknown' on the report and to do some condtional output, then by all means.
in a condition might as well just use
([SomeField] is null)
If it's you want 'unknown' on the report and to do some condtional output, then by all means.
Thanks Tony- I figured it out. After thinking about what I wanted to do last night, I decided to format the field to have a background color automatically and then I used conditional formatting to take the background color away if it met the opposite criteria (ie: if it contained a date.) It gave me what I wanted, Thanks for your help.
Really a beautiful solution I could find here. I was having the problem of selecting the null values along with dates and I was getting blank reports. Now with the suggested solutions, I could solve the problem in a friction of second. Thanks a lot.
Hello to all. hmm what is the possible code to use when I want to retrieve null values of access in listview (visual basic6) I try nz funtion but it doesnt work. Could anyone help me or teach me how to start to code.
Thanks in advance for every input here.
Thanks in advance for every input here.
Ask in questions, and post a bit of the code.
Oh and why are you still using VB6?
Oh and why are you still using VB6?
You pick on someone for using VB6, and you are writing code INTO A DATA BASE.
You should be hanging your head in shame and using a DB Just as a DB, and do your programming with a separate programming language.
Rob
PS If you are using a programming language, then I apologize. Please then pass my shock and horror to others that are writing code into a DB.
You should be hanging your head in shame and using a DB Just as a DB, and do your programming with a separate programming language.
Rob
PS If you are using a programming language, then I apologize. Please then pass my shock and horror to others that are writing code into a DB.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
