Select Case - "Invalid use of Null" Problem

G

Guest

Hi all,
I've got a button which is supposed to open differrent forms depending on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them together
- it gives me the message "Invalid use of Null". What could be wrong here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers (which
can have values with +/- or 0). Why it sais "Invalid use of Null"? Please,
can anybody help?

Thank you.
Lana
 
D

Douglas J. Steele

You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select
 
G

Guest

Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value. And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has "0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields] will
have a negative value and the [CashVND] will have equal positive value. And i
need to open 4 different forms depending on which of the [bank fields] is not
Null)

Can you show me my mistake please?
Thank you.
Lana


Douglas J. Steele said:
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Lana said:
Hi all,
I've got a button which is supposed to open differrent forms depending on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers (which
can have values with +/- or 0). Why it sais "Invalid use of Null"? Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
 
D

Douglas J. Steele

That error makes no sense to me, as I don't see a reference to ID anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has "0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields] will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields] is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


Douglas J. Steele said:
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Lana said:
Hi all,
I've got a button which is supposed to open differrent forms depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on each
record...
Lana


Douglas J. Steele said:
That error makes no sense to me, as I don't see a reference to ID anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has "0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields] will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields] is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


Douglas J. Steele said:
You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The error message is telling you that the query that's being used for the
recordsource for the form you're trying to open contains 2 (or more) fields
named "ID" (whether or not they show up in the list of fields in the SELECT
part of the query). You need to be a little more explicit. Either

stLinkCriteria = "[Table1].[ID]=" & Me!ID1

or else alias the particular ID field of interest (SELECT Table1.ID AS
ThisID, Table1.Field2, Table2.FIeld3,...) and use that alias in the
criteria.

stLinkCriteria = "[ThisID]=" & Me!ID1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be
more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on each
record...
Lana


Douglas J. Steele said:
That error makes no sense to me, as I don't see a reference to ID
anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has
"0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields]
will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields]
is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


:

You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms
depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you so much Douglas!
When i changed to
stLinkCriteria = "[T_MAIN].[ID]=" & Me!ID1
all worked fine!!!!!!!!
Next time i should be careful and will not give same name to "ID" fields of
different tables.
Lana


Douglas J. Steele said:
The error message is telling you that the query that's being used for the
recordsource for the form you're trying to open contains 2 (or more) fields
named "ID" (whether or not they show up in the list of fields in the SELECT
part of the query). You need to be a little more explicit. Either

stLinkCriteria = "[Table1].[ID]=" & Me!ID1

or else alias the particular ID field of interest (SELECT Table1.ID AS
ThisID, Table1.Field2, Table2.FIeld3,...) and use that alias in the
criteria.

stLinkCriteria = "[ThisID]=" & Me!ID1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be
more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on each
record...
Lana


Douglas J. Steele said:
That error makes no sense to me, as I don't see a reference to ID
anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

My fields (values, but not controls on form) sometimes have Null value.
And
I wanted to treat them separately form "0" values at first. Anyway, I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them has
"0"
value my button will open the form "blanks" - which is also ok with me.

But one thing still doesnt work - the cases with "Withdrawals" such as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields]
will
have a negative value and the [CashVND] will have equal positive value.
And i
need to open 4 different forms depending on which of the [bank fields]
is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


:

You're looking at values for a number of different fields (or are they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms
depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put them
together
- it gives me the message "Invalid use of Null". What could be wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

One school of thought says there's nothing wrong with using ID as a field
name in every table. You just have to remember to disambiguate all
references to the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Thank you so much Douglas!
When i changed to
stLinkCriteria = "[T_MAIN].[ID]=" & Me!ID1
all worked fine!!!!!!!!
Next time i should be careful and will not give same name to "ID" fields
of
different tables.
Lana


Douglas J. Steele said:
The error message is telling you that the query that's being used for the
recordsource for the form you're trying to open contains 2 (or more)
fields
named "ID" (whether or not they show up in the list of fields in the
SELECT
part of the query). You need to be a little more explicit. Either

stLinkCriteria = "[Table1].[ID]=" & Me!ID1

or else alias the particular ID field of interest (SELECT Table1.ID AS
ThisID, Table1.Field2, Table2.FIeld3,...) and use that alias in the
criteria.

stLinkCriteria = "[ThisID]=" & Me!ID1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lana said:
Sorry, I forgot to post 2 last lines - thought the problem should be
somewhere with "cases". Below is my full code:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True

Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"

Case Else
stDocName = "blanks"
End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click
End Sub


Does it make any sense now? I cannot understand how it can possibly be
more
than 1 ID match - it is my key field.
The form is continuous and i have this button and Me.ID1 text-box on
each
record...
Lana


:

That error makes no sense to me, as I don't see a reference to ID
anywhere
in your code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,

My fields (values, but not controls on form) sometimes have Null
value.
And
I wanted to treat them separately form "0" values at first. Anyway,
I
reconsidered and changed my code as to below:

Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ((Nz([CashVND], 0) > 0) And (IsNull([CW_No])))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) < 0)
stDocName = "Cash Spent"

Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case (Nz([BA351USD_N], 0) > 0)
stDocName = "BA-351-Received"
Case (Nz([BA324VND_N], 0) > 0)
stDocName = "BA-324-Received"
Case (Nz([BA305VND_N], 0) > 0)
stDocName = "BA-305-Received"

Case ((Nz([BA176USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-176-Spent"
Case ((Nz([BA351USD_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-351-Spent"
Case ((Nz([BA324VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-324-Spent"
Case ((Nz([BA305VND_N], 0) < 0) And (IsNull([CW_No])))
stDocName = "BA-305-Spent"

Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
Case ((Nz([BA351USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-351-Withdrawal"
Case ((Nz([BA324VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-324-Withdrawal"
Case ((Nz([BA305VND_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-305-Withdrawal"
Case Else
stDocName = "blanks"
End Select

Now if all 4 [bank fields] and [CashVND] are "Null" or any of them
has
"0"
value my button will open the form "blanks" - which is also ok with
me.

But one thing still doesnt work - the cases with "Withdrawals" such
as
below:
Case ((Nz([BA176USD_N], 0) < 0) And (Not IsNull([CW_No])))
stDocName = "BA-176-Withdrawal"
I get the error statement that "The specified filed [ID] could refer
to
more
than one table listed in the FROM clause of your SQL statement".

Why that happens? Something wrong with my logic in the code?
(for explanation: when [CW_No] is not Null, one of the [bank fields]
will
have a negative value and the [CashVND] will have equal positive
value.
And i
need to open 4 different forms depending on which of the [bank
fields]
is
not
Null)

Can you show me my mistake please?
Thank you.
Lana


:

You're looking at values for a number of different fields (or are
they
controls on a form?): [NotPaid], [CashVND] and [BA176USD_N]. Are
you
positive all of them have values?

To be safe, try something like:

Select Case True
Case (Nz([NotPaid], False) = True)
stDocName = "PREQs"
Case (Nz([CashVND], 0) > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case (Nz([CashVND], 0) <= 0)
stDocName = "Cash Spent"
Case (Nz([BA176USD_N], 0) > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi all,
I've got a button which is supposed to open differrent forms
depending
on
some condition. I got a "Select Case" Code for it as below:
Select Case True
Case ([NotPaid] = True)
stDocName = "PREQs"
Case ([CashVND] > 0) And (IsNull([CW_No]))
stDocName = "Cash Received"
Case ([CashVND] <= 0)
stDocName = "Cash Spent"
Case ([BA176USD_N] > 0)
stDocName = "BA-176-Received"
Case Else
stDocName = "blanks"
End Select

I tested it each case one by one and it works, but when i put
them
together
- it gives me the message "Invalid use of Null". What could be
wrong
here?

[CW_No] is a text field, [NotPaid] is a "Y/N", the rest are
numbers
(which
can have values with +/- or 0). Why it sais "Invalid use of
Null"?
Please,
can anybody help?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

other solution to If-Then? 4
Invalud Use of Null 3
Invalid use of Null 17
Select case problem 1
Case Select when StLinkCriteria is Null?? 7
Using Select case and isnull 5
DSum w/Between = Invalid use of Null 12
Select case problem - else if 2

Top