Problem with variant

  • Thread starter rtwiss via AccessMonster.com
  • Start date
R

rtwiss via AccessMonster.com

i'm stating this:

Public Function GetConclusion2(MaxOfQ0, MaxOfQ1) As Variant

MaxOfQ0 and MaxOfQ1 are fields

when i put it through debugger, place stop and step through the values for
MaxOfQ0 and MaxOfQ1 are the same. The greater value get given to both fields.
Any ideas why this is happening?
 
J

John Spencer

Not without seeing the code and the call to the code.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
K

Klatuu

First, I don't understand your question, second, I think there is some
confusion.

You don't use field names as arguments in a function declaration. They are
variables the function receives values in to work with. As they are not
typed, they are variant data types. You pass values to the function when
you call it.

As to why both are the same, You would need to post the code where you are
using the function and the body of the function.
 
R

rtwiss via AccessMonster.com

Call to code: "Comparison to with previous quarter: GetConclusion2([MaxOfQ1],
[MaxOfQ1])"

Code:

Public Function GetConclusion2(MaxOfQ0, MaxOfQ1) As Variant
On Error GoTo ErrorExit

'Function to generates comparison of current quarter with previous quarter
based on Q0 and Q1

If MaxOfQ0 = Null & MaxOfQ1 = Null Then
GetConclusion2 = "Not sampled in 2 consecutive quarters"
ElseIf MaxOfQ0 = Null Then
GetConclusion2 = "Not sampled this quarter"
ElseIf MaxOfQ1 = Null Then
GetConclusion2 = "Not sampled previous quarter"
ElseIf Left(MaxOfQ0, 1) = "<" And Left(MaxOfQ1, 1) = "<" Then
GetConclusion2 = "Similar to last quarter"
ElseIf Left(MaxOfQ0, 1) = "<" And IsNumeric(MaxOfQ1) Then
GetConclusion2 = "Decrease since last quarter"
ElseIf IsNumeric(MaxOfQ0) And Left(MaxOfQ1, 1) = "<" Then
GetConclusion2 = "Increase since last quarter"
ElseIf MaxOfQ0 > MaxOfQ1 Then
GetConclusion2 = "Increase since last quarter"
ElseIf MaxOfQ0 < MaxOfQ1 Then
GetConclusion2 = "Decrease since last quarter"
ElseIf MaxOfQ0 = MaxOfQ1 Then
GetConclusion2 = "Similar to last quarter"
Else

End If


NormalExit:
Exit Function

ErrorExit:
GetConclusion2 = "Error"
'MsgBox Err.Description & " in module 'strGetQuarter'", vbExclamation,
"Error"
Resume NormalExit

End Function

John said:
Not without seeing the code and the call to the code.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
i'm stating this:
Click to expand...
[quoted text clipped - 5 lines]
MaxOfQ0 and MaxOfQ1 are the same. The greater value get given to both fields.
Any ideas why this is happening?
Click to expand...
Click to expand...
 
K

Klatuu

Both are the same value because you are using the same name twice in your
call.
"Comparison to with previous quarter: GetConclusion2([MaxOfQ1], [MaxOfQ1])"

^ same field ^

Now, your function will not work. Nothing = Null. If you want to test a
variable to see if it is null, you have to use the IsNull function
Also, the & is not a substitue for the word And. It is a concatenation
operator.

For example:
If MaxOfQ0 = Null & MaxOfQ1 = Null Then

Should be
If IsNull(MaxOfQ0) And IsNull(MaxOfQ1) Then


rtwiss via AccessMonster.com said:
Call to code: "Comparison to with previous quarter:
GetConclusion2([MaxOfQ1],
[MaxOfQ1])"

Code:

Public Function GetConclusion2(MaxOfQ0, MaxOfQ1) As Variant
On Error GoTo ErrorExit

'Function to generates comparison of current quarter with previous quarter
based on Q0 and Q1

If MaxOfQ0 = Null & MaxOfQ1 = Null Then
GetConclusion2 = "Not sampled in 2 consecutive quarters"
ElseIf MaxOfQ0 = Null Then
GetConclusion2 = "Not sampled this quarter"
ElseIf MaxOfQ1 = Null Then
GetConclusion2 = "Not sampled previous quarter"
ElseIf Left(MaxOfQ0, 1) = "<" And Left(MaxOfQ1, 1) = "<" Then
GetConclusion2 = "Similar to last quarter"
ElseIf Left(MaxOfQ0, 1) = "<" And IsNumeric(MaxOfQ1) Then
GetConclusion2 = "Decrease since last quarter"
ElseIf IsNumeric(MaxOfQ0) And Left(MaxOfQ1, 1) = "<" Then
GetConclusion2 = "Increase since last quarter"
ElseIf MaxOfQ0 > MaxOfQ1 Then
GetConclusion2 = "Increase since last quarter"
ElseIf MaxOfQ0 < MaxOfQ1 Then
GetConclusion2 = "Decrease since last quarter"
ElseIf MaxOfQ0 = MaxOfQ1 Then
GetConclusion2 = "Similar to last quarter"
Else

End If


NormalExit:
Exit Function

ErrorExit:
GetConclusion2 = "Error"
'MsgBox Err.Description & " in module 'strGetQuarter'", vbExclamation,
"Error"
Resume NormalExit

End Function

John said:
Not without seeing the code and the call to the code.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
i'm stating this:
Click to expand...
[quoted text clipped - 5 lines]
MaxOfQ0 and MaxOfQ1 are the same. The greater value get given to both
fields.
Any ideas why this is happening?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Another problem is that for your last conditions you need to convert the
strings to number to get a valid comparison.

ElseIf MaxOfQ0 < MaxOfQ1 Then

should be
ElseIf Val(MaxOfQ0) < Val(MaxOfQ1) Then


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Both are the same value because you are using the same name twice in your
call.
"Comparison to with previous quarter: GetConclusion2([MaxOfQ1], [MaxOfQ1])"

^ same field ^

Now, your function will not work. Nothing = Null. If you want to test a
variable to see if it is null, you have to use the IsNull function
Also, the & is not a substitue for the word And. It is a concatenation
operator.

For example:
If MaxOfQ0 = Null & MaxOfQ1 = Null Then

Should be
If IsNull(MaxOfQ0) And IsNull(MaxOfQ1) Then


rtwiss via AccessMonster.com said:
Call to code: "Comparison to with previous quarter:
GetConclusion2([MaxOfQ1],
[MaxOfQ1])"

Code:

Public Function GetConclusion2(MaxOfQ0, MaxOfQ1) As Variant
On Error GoTo ErrorExit

'Function to generates comparison of current quarter with previous quarter
based on Q0 and Q1

If MaxOfQ0 = Null & MaxOfQ1 = Null Then
GetConclusion2 = "Not sampled in 2 consecutive quarters"
ElseIf MaxOfQ0 = Null Then
GetConclusion2 = "Not sampled this quarter"
ElseIf MaxOfQ1 = Null Then
GetConclusion2 = "Not sampled previous quarter"
ElseIf Left(MaxOfQ0, 1) = "<" And Left(MaxOfQ1, 1) = "<" Then
GetConclusion2 = "Similar to last quarter"
ElseIf Left(MaxOfQ0, 1) = "<" And IsNumeric(MaxOfQ1) Then
GetConclusion2 = "Decrease since last quarter"
ElseIf IsNumeric(MaxOfQ0) And Left(MaxOfQ1, 1) = "<" Then
GetConclusion2 = "Increase since last quarter"
ElseIf MaxOfQ0 > MaxOfQ1 Then
GetConclusion2 = "Increase since last quarter"
ElseIf MaxOfQ0 < MaxOfQ1 Then
GetConclusion2 = "Decrease since last quarter"
ElseIf MaxOfQ0 = MaxOfQ1 Then
GetConclusion2 = "Similar to last quarter"
Else

End If


NormalExit:
Exit Function

ErrorExit:
GetConclusion2 = "Error"
'MsgBox Err.Description & " in module 'strGetQuarter'", vbExclamation,
"Error"
Resume NormalExit

End Function

John said:
Not without seeing the code and the call to the code.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

i'm stating this:

[quoted text clipped - 5 lines]
MaxOfQ0 and MaxOfQ1 are the same. The greater value get given to both
fields.
Any ideas why this is happening?
Click to expand...
Click to expand...
Click to expand...
 
R

rtwiss via AccessMonster.com

What a Monday error!!!!
Thank you
Both are the same value because you are using the same name twice in your
call.
"Comparison to with previous quarter: GetConclusion2([MaxOfQ1], [MaxOfQ1])"

^ same field ^

Now, your function will not work. Nothing = Null. If you want to test a
variable to see if it is null, you have to use the IsNull function
Also, the & is not a substitue for the word And. It is a concatenation
operator.

For example:
If MaxOfQ0 = Null & MaxOfQ1 = Null Then

Should be
If IsNull(MaxOfQ0) And IsNull(MaxOfQ1) Then
Call to code: "Comparison to with previous quarter:
GetConclusion2([MaxOfQ1],
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
Top