Query help with Modules

G

Guest

I have 3 modules that I would like to use within a query I am designing. I
currently have an expression in one field of my query that references one of
these modules which is:
Invoice Bucket: IsRange([INVOICES])

Currently this looks at the INVOICES field and assigns a value based on the
parameters in the module IsRange. My 3 modules are called IsRange, IsRangeSF,
and IsRangeLS. Here is what I want to do...
I would like to make my expression conditional based on 2 other fields in my
query, a BOD field and a BRNTYPE field. Info from these two fields will
determine which module to run or look at.
For example, my 4 possible scenarios are:
If BRNTYPE is F and BOD is RU or RV, then use module IsRangeSF
If BRNTYPE is F and BOD is N1, NC, or NH then use module IsRangeLS
If BRNTYPE is B and BOD is RU or RV then use module IsRange
If BRNTYPE is B and BOD is N1, NC, or NH then use module IsRangeLS

I would like to be able to do this in one expression so the output is all in
the same field, because I have a seperate calculation that uses the data
pulled from the module(s) in this Invoice Bucket field.

Is this possible? I hope so and any help is greatly appreciated.
 
G

Guest

I would suggest creating one function to wrap it all. Have a function that
takes BRNTYP, BOD, and INVOICES columns and does your If/Then or Select Case.
You can have this function branch to other private functions (IsRange,
IsRangeSF, IsRangeLS) and return the value that they return. This will be
easier to manage than a horrendous IIF statement.

Barry
 
G

Guest

I forgot to mention that in my 4 scenarios, once it is detemined which module
to use/look at, I need it to still look at the [INVOICES] field to assign a
value from the correct module.
 
G

Guest

That sounds like it would work for me, the only problem is that I am a
failrly inexperienced user and excuse my ignorance but.....well, how do I do
that?



Barry Gilbert said:
I would suggest creating one function to wrap it all. Have a function that
takes BRNTYP, BOD, and INVOICES columns and does your If/Then or Select Case.
You can have this function branch to other private functions (IsRange,
IsRangeSF, IsRangeLS) and return the value that they return. This will be
easier to manage than a horrendous IIF statement.

Barry


Lucien said:
I have 3 modules that I would like to use within a query I am designing. I
currently have an expression in one field of my query that references one of
these modules which is:
Invoice Bucket: IsRange([INVOICES])

Currently this looks at the INVOICES field and assigns a value based on the
parameters in the module IsRange. My 3 modules are called IsRange, IsRangeSF,
and IsRangeLS. Here is what I want to do...
I would like to make my expression conditional based on 2 other fields in my
query, a BOD field and a BRNTYPE field. Info from these two fields will
determine which module to run or look at.
For example, my 4 possible scenarios are:
If BRNTYPE is F and BOD is RU or RV, then use module IsRangeSF
If BRNTYPE is F and BOD is N1, NC, or NH then use module IsRangeLS
If BRNTYPE is B and BOD is RU or RV then use module IsRange
If BRNTYPE is B and BOD is N1, NC, or NH then use module IsRangeLS

I would like to be able to do this in one expression so the output is all in
the same field, because I have a seperate calculation that uses the data
pulled from the module(s) in this Invoice Bucket field.

Is this possible? I hope so and any help is greatly appreciated.
Click to expand...
Click to expand...
 
G

Guest

Lucien said:
That sounds like it would work for me, the only problem is that I am a
failrly inexperienced user and excuse my ignorance but.....well, how do I do
that?
Click to expand...
I think you alrwady know. You have a function called IsRange that probably
looks like this:

Public Function IsRange(ByVal Invoice As Integer) As Boolean
' Do stuff here.
End Function

and you call it in your query with:
Invoice Bucket: IsRange([INVOICES])

Create a new function called IsRangeAll and pass in the three values,
something like this:

Public Function IsRangeAll(ByVal Invoice As Integer, ByVal BRNTYPE As
String, ByVal BOD As String) As Boolean
If BRYNTYPE = "F" And Left$(BOD,1)="R" Then
IsRangeAll = IsRangeSF(Invoice)
Else If BRYNTYPE = "F" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "R" Then
IsRangeAll = IsRange(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
End If
End Function

Private Function IsRange(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

Private Function IsRangeLs(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

By the way, I assumed that INVOICE is an integer, but you should change this
if it's not.

Barry

Private Function IsRangeSF(ByVal Invoice As Integer) As Boolean
'Return you value
End Function
 
G

Guest

Ok so now I am getting an error message that says: Worng number of arguments
used with function in query expression 'IsRangeAll([INVOICES])
I'm not sure what I am doing wrong but here is the function that I created
per your suggestion:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRangeSF(INVOICES)
Else: If BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRange(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
End If
End Function

Private Function IsRange(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeLS(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeSF(ByVal INVOICES As Long) As Boolean
End Function


Any suggestions for me?



Barry Gilbert said:
Lucien said:
That sounds like it would work for me, the only problem is that I am a
failrly inexperienced user and excuse my ignorance but.....well, how do I do
that?
Click to expand...
I think you alrwady know. You have a function called IsRange that probably
looks like this:

Public Function IsRange(ByVal Invoice As Integer) As Boolean
' Do stuff here.
End Function

and you call it in your query with:
Invoice Bucket: IsRange([INVOICES])

Create a new function called IsRangeAll and pass in the three values,
something like this:

Public Function IsRangeAll(ByVal Invoice As Integer, ByVal BRNTYPE As
String, ByVal BOD As String) As Boolean
If BRYNTYPE = "F" And Left$(BOD,1)="R" Then
IsRangeAll = IsRangeSF(Invoice)
Else If BRYNTYPE = "F" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "R" Then
IsRangeAll = IsRange(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
End If
End Function

Private Function IsRange(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

Private Function IsRangeLs(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

By the way, I assumed that INVOICE is an integer, but you should change this
if it's not.

Barry

Private Function IsRangeSF(ByVal Invoice As Integer) As Boolean
'Return you value
End Function
Click to expand...
 
G

Guest

Back in your query, you need to pass all three values into IsRangeAll.
Something like this:
InvoiceBucket: IsRangeAll([Invoices],[BrnType],[BOD])

Barry

Lucien said:
Ok so now I am getting an error message that says: Worng number of arguments
used with function in query expression 'IsRangeAll([INVOICES])
I'm not sure what I am doing wrong but here is the function that I created
per your suggestion:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRangeSF(INVOICES)
Else: If BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRange(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
End If
End Function

Private Function IsRange(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeLS(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeSF(ByVal INVOICES As Long) As Boolean
End Function


Any suggestions for me?



Barry Gilbert said:
Lucien said:
That sounds like it would work for me, the only problem is that I am a
failrly inexperienced user and excuse my ignorance but.....well, how do I do
that?
Click to expand...
I think you alrwady know. You have a function called IsRange that probably
looks like this:

Public Function IsRange(ByVal Invoice As Integer) As Boolean
' Do stuff here.
End Function

and you call it in your query with:
Invoice Bucket: IsRange([INVOICES])

Create a new function called IsRangeAll and pass in the three values,
something like this:

Public Function IsRangeAll(ByVal Invoice As Integer, ByVal BRNTYPE As
String, ByVal BOD As String) As Boolean
If BRYNTYPE = "F" And Left$(BOD,1)="R" Then
IsRangeAll = IsRangeSF(Invoice)
Else If BRYNTYPE = "F" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "R" Then
IsRangeAll = IsRange(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
End If
End Function

Private Function IsRange(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

Private Function IsRangeLs(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

By the way, I assumed that INVOICE is an integer, but you should change this
if it's not.

Barry

Private Function IsRangeSF(ByVal Invoice As Integer) As Boolean
'Return you value
End Function
Click to expand...
Click to expand...
 
G

Guest

Alright, I changed my query to pass all three values into IsRangeAll.
Now I get an error message: Undefined Function 'IsRangeAll' in expression

I have double checked spelling and continuity with field names.



Barry Gilbert said:
Back in your query, you need to pass all three values into IsRangeAll.
Something like this:
InvoiceBucket: IsRangeAll([Invoices],[BrnType],[BOD])

Barry

Lucien said:
Ok so now I am getting an error message that says: Worng number of arguments
used with function in query expression 'IsRangeAll([INVOICES])
I'm not sure what I am doing wrong but here is the function that I created
per your suggestion:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRangeSF(INVOICES)
Else: If BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRange(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
End If
End Function

Private Function IsRange(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeLS(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeSF(ByVal INVOICES As Long) As Boolean
End Function


Any suggestions for me?



Barry Gilbert said:
Lucien" wrote:

That sounds like it would work for me, the only problem is that I am a
failrly inexperienced user and excuse my ignorance but.....well, how do I do
that?
I think you alrwady know. You have a function called IsRange that probably
looks like this:

Public Function IsRange(ByVal Invoice As Integer) As Boolean
' Do stuff here.
End Function

and you call it in your query with:
Invoice Bucket: IsRange([INVOICES])

Create a new function called IsRangeAll and pass in the three values,
something like this:

Public Function IsRangeAll(ByVal Invoice As Integer, ByVal BRNTYPE As
String, ByVal BOD As String) As Boolean
If BRYNTYPE = "F" And Left$(BOD,1)="R" Then
IsRangeAll = IsRangeSF(Invoice)
Else If BRYNTYPE = "F" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "R" Then
IsRangeAll = IsRange(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
End If
End Function

Private Function IsRange(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

Private Function IsRangeLs(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

By the way, I assumed that INVOICE is an integer, but you should change this
if it's not.

Barry

Private Function IsRangeSF(ByVal Invoice As Integer) As Boolean
'Return you value
End Function
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Does your code live in a module? It cannot be in a class module or in a form
or report module.

Barry

Lucien said:
Alright, I changed my query to pass all three values into IsRangeAll.
Now I get an error message: Undefined Function 'IsRangeAll' in expression

I have double checked spelling and continuity with field names.



Barry Gilbert said:
Back in your query, you need to pass all three values into IsRangeAll.
Something like this:
InvoiceBucket: IsRangeAll([Invoices],[BrnType],[BOD])

Barry

Lucien said:
Ok so now I am getting an error message that says: Worng number of arguments
used with function in query expression 'IsRangeAll([INVOICES])
I'm not sure what I am doing wrong but here is the function that I created
per your suggestion:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRangeSF(INVOICES)
Else: If BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRange(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
End If
End Function

Private Function IsRange(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeLS(ByVal INVOICES As Long) As Boolean
End Function

Private Function IsRangeSF(ByVal INVOICES As Long) As Boolean
End Function


Any suggestions for me?



:

Lucien" wrote:

That sounds like it would work for me, the only problem is that I am a
failrly inexperienced user and excuse my ignorance but.....well, how do I do
that?
I think you alrwady know. You have a function called IsRange that probably
looks like this:

Public Function IsRange(ByVal Invoice As Integer) As Boolean
' Do stuff here.
End Function

and you call it in your query with:
Invoice Bucket: IsRange([INVOICES])

Create a new function called IsRangeAll and pass in the three values,
something like this:

Public Function IsRangeAll(ByVal Invoice As Integer, ByVal BRNTYPE As
String, ByVal BOD As String) As Boolean
If BRYNTYPE = "F" And Left$(BOD,1)="R" Then
IsRangeAll = IsRangeSF(Invoice)
Else If BRYNTYPE = "F" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "R" Then
IsRangeAll = IsRange(Invoice)
Else If BRYNTYPE = "B" And Left$(BOD, 1) = "N" Then
IsRangeAll = IsRangeLs(Invoice)
End If
End Function

Private Function IsRange(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

Private Function IsRangeLs(ByVal Invoice As Integer) As Boolean
'Return you value
End Function

By the way, I assumed that INVOICE is an integer, but you should change this
if it's not.

Barry

Private Function IsRangeSF(ByVal Invoice As Integer) As Boolean
'Return you value
End Function
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

Does your code live in a module? It cannot be in a class module or in a form
or report module.
Click to expand...
Also note that you cannot call a Module; a Module contains Subs and
Functions, and you call the Sub or Function, not the Module directly.
In addition, Subs, Functions and Modules share the same "namespace",
so you must give the Module a name different from the names of any
subs or functions in that (or any other) module.

John W. Vinson[MVP]
 
G

Guest

Okay, I think I understand. I do currently have the code in a module. It
obviously doesn't belong there by what you are telling me. Where can I put
it?
 
J

John Vinson

Okay, I think I understand. I do currently have the code in a module. It
obviously doesn't belong there by what you are telling me. Where can I put
it?
Click to expand...

That's not what I meant, Lucien.

The code certainly should be in a Module. What I'm saying is a) that
the Name of the module must be different from the name of the Function
or Sub *in* that module; and b) that when you call the code, you call
it by the name of the Function or Sub, not by the name of the Module.

For example, you could have a module named basMyFunction containing

Public Function MyFunction(vArg1 As Variant) As Integer
<your code goes here>
End Function

In your Query you would use

SomeName: MyFunction([fieldname])


John W. Vinson[MVP]
 
G

Guest

Okay, gotcha. My module that I created from Barry's suggestion is now called
IRMess and my function is called IsRangeAll. Here is how it starts:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean

In my Query this is how I am calling the code:

Invoice Bucket: IsRangeAll([INVOICES],[BrnType],[BOD_CODE])

I get an error message that says

Undefined function 'IsRange' in expression.

Here is my code that the expression is calling for and below that I will put
my code for the IsRange function:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRangeSF(INVOICES)
Else: If BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRange(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
End If
End Function

Private Function IsRange(ByVal INVOICES As Long) As Boolean
'Return you value
End Function

Private Function IsRangeLS(ByVal INVOICES As Long) As Boolean
'Return you value
End Function

Private Function IsRangeSF(ByVal INVOICES As Long) As Boolean
'Return you value
End Function

*******Here is the IsRange function code*********

Private Function IsRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 50
IsRange = "33-50"
Case 51 To 100
IsRange = "51-100"
Case 11 To 12
IsRange = "11-12"
Case 13 To 15
IsRange = "13-15"
Case 16 To 17
IsRange = "16-17"
Case 18 To 21
IsRange = "18-21"
Case 22 To 26
IsRange = "22-26"
Case 27 To 32
IsRange = "27-32"
Case 101 To 250
IsRange = "101-250"
Case Is > 250
IsRange = "GT 250"
Case 10 To 10
IsRange = "10"
Case 9 To 9
IsRange = "9"
Case 8 To 8
IsRange = "8"
Case 7 To 7
IsRange = "7"
Case 6 To 6
IsRange = "6"
Case 5 To 5
IsRange = "5"
Case 4 To 4
IsRange = "4"
Case 3 To 3
IsRange = "3"
Case 1 To 2
IsRange = "1-2"
Case 0 To 0
IsRange = "0"

End Select

End Function


John Vinson said:
Okay, I think I understand. I do currently have the code in a module. It
obviously doesn't belong there by what you are telling me. Where can I put
it?
Click to expand...

That's not what I meant, Lucien.

The code certainly should be in a Module. What I'm saying is a) that
the Name of the module must be different from the name of the Function
or Sub *in* that module; and b) that when you call the code, you call
it by the name of the Function or Sub, not by the name of the Module.

For example, you could have a module named basMyFunction containing

Public Function MyFunction(vArg1 As Variant) As Integer
<your code goes here>
End Function

In your Query you would use

SomeName: MyFunction([fieldname])


John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Okay, gotcha. My module that I created from Barry's suggestion is now called
IRMess and my function is called IsRangeAll. Here is how it starts:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean

In my Query this is how I am calling the code:

Invoice Bucket: IsRangeAll([INVOICES],[BrnType],[BOD_CODE])

I get an error message that says

Undefined function 'IsRange' in expression.
Click to expand...

This would suggest that your query is not only calling IsRangeAll (to
calculate the Invoice Bucket field), but is ALSO calling the IsRange
function - perhaps in an Order By or Group By expression. Since
IsRange is a *private* function it is not available to SQL.

Open the query in SQL view and see if it's still calling IsRange()
somewhere; if so, change it appropriately.

John W. Vinson[MVP]
 
G

Guest

Ok...so now I did find in the SQL that it was calling for IsRange....I fixed
that.
Now.......when I run the query it is giving me a compile error: Else without
If and the debugger is promting the IsRangeAll code:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRangeSF(INVOICES)
Else: If BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then IsRangeAll =
IsRange(INVOICES)
Else: If BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then IsRangeAll =
IsRangeLS(INVOICES)
End If
End Function

I set this up from a recommendation that Barry gave me. It has highlighted
the very first line of code in yellow and then has a blue highlight over the
first Else word.


John Vinson said:
Okay, gotcha. My module that I created from Barry's suggestion is now called
IRMess and my function is called IsRangeAll. Here is how it starts:

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean

In my Query this is how I am calling the code:

Invoice Bucket: IsRangeAll([INVOICES],[BrnType],[BOD_CODE])

I get an error message that says

Undefined function 'IsRange' in expression.
Click to expand...

This would suggest that your query is not only calling IsRangeAll (to
calculate the Invoice Bucket field), but is ALSO calling the IsRange
function - perhaps in an Order By or Group By expression. Since
IsRange is a *private* function it is not available to SQL.

Open the query in SQL view and see if it's still calling IsRange()
somewhere; if so, change it appropriately.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Now.......when I run the query it is giving me a compile error: Else without
If and the debugger is promting the IsRangeAll code:
Click to expand...

I think you need the ElseIf syntax rather than Else: If . Also, note
that typically Then appears alone at the end of an If statement,
followed (on another line) by the action to be taken. Try

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As
String, ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then
IsRangeAll = IsRangeSF(INVOICES)
ElseIf BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then
IsRangeAll = IsRangeLS(INVOICES)
ElseIf BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then
IsRangeAll = IsRange(INVOICES)
ElseIf BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then
IsRangeAll = IsRangeLS(INVOICES)
Else
MsgBox "Erroneous input, cannot determine IsRangeall"
IsRangeAll = False
End If
End Function


John W. Vinson[MVP]
 
N

Neil Sunderland

John said:
I think you need the ElseIf syntax rather than Else: If . Also, note
that typically Then appears alone at the end of an If statement,
followed (on another line) by the action to be taken. Try

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As
String, ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then
IsRangeAll = IsRangeSF(INVOICES)
ElseIf BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then
IsRangeAll = IsRangeLS(INVOICES)
ElseIf BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then
IsRangeAll = IsRange(INVOICES)
ElseIf BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then
IsRangeAll = IsRangeLS(INVOICES)
Else
MsgBox "Erroneous input, cannot determine IsRangeall"
IsRangeAll = False
End If
End Function
Click to expand...

Or how about this as an easier-to-follow alternative?

Public Function IsRangeAll(ByVal INVOICES As Long, ByVal BrnType As
String, ByVal BOD_CODE As String) As Boolean
Select Case BrnType & Left$(BOD_CODE, 1)
Case "FR"
IsRangeAll = IsRangeSF(INVOICES)
Case "FN"
IsRangeAll = IsRangeLS(INVOICES)
Case "BR"
IsRangeAll = IsRange(INVOICES)
Case "BN"
IsRangeAll = IsRangeLS(INVOICES)
Case Else
MsgBox "Erroneous input, cannot determine IsRangeall"
IsRangeAll = False
End Select
End Function
 
J

John Vinson

Or how about this as an easier-to-follow alternative?
Click to expand...

That probably is better... I thought about it but was put off by the
need to concatenate two variables.

The need to DO all this in the first place seems to stem from
questionable table normalization - extracting the leftmost character
of a field to determine a course of action implies that the field
isn't atomic; the interaction between the two fields implies that the
table isn't in first normal form... ouch!

John W. Vinson[MVP]
 
G

Guest

WoW, I don't know what the heck all that meant that you just said, but it
didn't sound good....lol.
I will try what you and Neal suggested. I will post a reply later with my
outcome. Even if this is not the end of my nightmare....the patience and
help you all have provided so far is second to none, and I appreciate it
immensely!!!
 
G

Guest

Alright so I tried both suggestions....first I changed the code the way you,
John, advised, but now I am getting - Run type error '13': Type mismatch
Here is what I changed the code to and it highlights the line "IsRangeAlll =
IsRange(INVOICES) "

Public Function IsRangeAlll(ByVal INVOICES As Long, ByVal BrnType As String,
ByVal BOD_CODE As String) As Boolean
If BrnType = "F" And Left$(BOD_CODE, 1) = "R" Then
IsRangeAlll = IsRangeSF(INVOICES)
ElseIf BrnType = "F" And Left$(BOD_CODE, 1) = "N" Then
IsRangeAlll = IsRangeLS(INVOICES)
ElseIf BrnType = "B" And Left$(BOD_CODE, 1) = "R" Then
IsRangeAlll = IsRange(INVOICES)
ElseIf BrnType = "B" And Left$(BOD_CODE, 1) = "N" Then
IsRangeAlll = IsRangeLS(INVOICES)
End If
End Function

I then used Neal's suggestion and I get the same error...type mismatch for
the same Then statement - IsRangeAlll = IsRange(INVOICES)

Again...please help me identify what I am doing wrong
 

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

Help With Parameter Query 2
Conditional Expression help 2
Help With Query 1
Help with Query/SQL 4
Expression Builder query with zero amounts 5
Criteria not working on expression field in query 3
Question about class modules 5
Select Query with an IIf 2

Top