By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,827 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

Is Null pproblem

P: n/a
I cannot define the Null situation in my code. I need the condition
when the quantity in the
field stock is Null.But i cannot do it.I can do it only with <=1 but
then it means that
the presence of 1 piece is not taken into account.

If i write
If CLng(DCount("stock", "Products", StrCondition)) = Null Then
Or
If CLng(DCount("stock", "Products", StrCondition)) = 0 Then
Then the code does not function and no message box appears.

If i write
If CLng(DCount("stock", "Products", StrCondition)) < 1 Then
Then the same happens again.

My code works only when i put the following condition:
If CLng(DCount("stock", "Products", StrCondition)) <= 1 Then
MsgBox "Not on stock" ", vbInformation + vbOKOnly

However, if i write <= 1, then if there is 1 piece on stock, the
mesage says i have
nothing on stock and the programme stops.
How can i avoid this difficulty ?
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Johm,
Sssss booo Listen, nulls in columns that ought to be numeric are a bad
idea. Don't use nulls, use zero for when you are out of stock. Plus, what
about back orders or allocated sales that create a shortage (negative
quantity)? And . . . I *hate* the d[lookup] set of functions. They give me
the shivers. Back in the day I had to fix a database in Access 2.0 that had
dcount, dsum, etc. all over the place. Danged functions are slow, resource
hogs and general pains in the ass. I tend to write a vb function to do what
it is I need instead of them goldarned d[lookup] beasties. It makes my code
easier to read & maintain if I roll my own.

"Johm" <ke**@abv.bg> wrote in message
news:24**************************@posting.google.c om...
I cannot define the Null situation in my code. I need the condition
when the quantity in the
field stock is Null.But i cannot do it.I can do it only with <=1 but
then it means that
the presence of 1 piece is not taken into account.

If i write
If CLng(DCount("stock", "Products", StrCondition)) = Null Then
Or
If CLng(DCount("stock", "Products", StrCondition)) = 0 Then
Then the code does not function and no message box appears.

If i write
If CLng(DCount("stock", "Products", StrCondition)) < 1 Then
Then the same happens again.

My code works only when i put the following condition:
If CLng(DCount("stock", "Products", StrCondition)) <= 1 Then
MsgBox "Not on stock" ", vbInformation + vbOKOnly

However, if i write <= 1, then if there is 1 piece on stock, the
mesage says i have
nothing on stock and the programme stops.
How can i avoid this difficulty ?

Nov 13 '05 #2

P: n/a
Johm wrote:
I cannot define the Null situation in my code. I need the condition
when the quantity in the
field stock is Null.But i cannot do it.I can do it only with <=1 but
then it means that
the presence of 1 piece is not taken into account.

If i write
If CLng(DCount("stock", "Products", StrCondition)) = Null Then
Or
If CLng(DCount("stock", "Products", StrCondition)) = 0 Then

You can't evaluate "Value = Null", use IsNull(Value)

eg.

If Isnull(CLng(DCount("stock", "Products", StrCondition)) ) Then
Then the code does not function and no message box appears.

If i write
If CLng(DCount("stock", "Products", StrCondition)) < 1 Then
Then the same happens again.

My code works only when i put the following condition:
If CLng(DCount("stock", "Products", StrCondition)) <= 1 Then
MsgBox "Not on stock" ", vbInformation + vbOKOnly

However, if i write <= 1, then if there is 1 piece on stock, the
mesage says i have
nothing on stock and the programme stops.
How can i avoid this difficulty ?


As per someone elses reply, not a good idea to use Nulls in a quantity
field.
--
regards,

Bradley
Nov 13 '05 #3

P: n/a
Alan Webb wrote:
Johm,
Sssss booo Listen, nulls in columns that ought to be numeric are a bad
idea. Don't use nulls, use zero for when you are out of stock. Plus, what
about back orders or allocated sales that create a shortage (negative
quantity)? And . . . I *hate* the d[lookup] set of functions. They give me
the shivers. Back in the day I had to fix a database in Access 2.0 that had
dcount, dsum, etc. all over the place. Danged functions are slow, resource
hogs and general pains in the ass. I tend to write a vb function to do what
it is I need instead of them goldarned d[lookup] beasties. It makes my code
easier to read & maintain if I roll my own.


1. I'm using Mozilla's newsgroup client for the first time; I hope this
does not appear as HTML.
2. If you ask Jane/John to count some sets, what is the proper count for
those not yet counted? Surely it is NULL and not zero? And (for
example) what is the average of 6, 4 and 0; how would 6, 4 and NULL
affect this calculation?
3. I used to hate the domain aggregate functions. But today I think
there is no reason to do so. They are very fast and quite convenient for
finding a single piece of information. (I'm the same about iff in
queries; I used to think they were BAD, but I was wrong.)
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.