Compile error: byRef argument type mismatch

[Richard Hollenbeck]

I have two functions (one using the other) where I want to pass into it the
character A, B, C, D, or F and have it return a Double
indicating the minimum score it takes to get that grade. For example,
here's a stipped-down version of the first function:

private function getComment()
Select Case txtTotal ' the value in the text box txtTotal in the report
Case Is >= minimumScore(B)
getComment = "Good work!"
end select.
'etc.
end sub

Then here's the second function:

Private Function minimumScore(Gr adeLetter As String) as Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

StrSQL = "SELECT tblOptions.minG rade "
StrSQL = StrSQL & "FROM tblOptions "
StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"

Set db = CurrentDb
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
minimumScore = rs.Fields(0)
End Function

But it doesn't work. I'm trying to pass a string into the second function
(the letter grade) and have it return a double. (like 0.82, for example for
a B.) What am I forgetting?

Thanks.

Rich Hollenbeck

[Douglas J. Steele]

Re: Compile error: byRef argument type mismatch

How have you defined B? It must be defined as String, since that's what your
function is expecting. If you haven't got Option Explicit turned on (and you
really should), then B will be a Variant, and you'll get the error message
you described.

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)



"Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message
news:%Wefd.6585 $8W6.796@trnddc 05...[color=blue]
> I have two functions (one using the other) where I want to pass into it[/color]
the[color=blue]
> character A, B, C, D, or F and have it return a Double
> indicating the minimum score it takes to get that grade. For example,
> here's a stipped-down version of the first function:
>
> private function getComment()
> Select Case txtTotal ' the value in the text box txtTotal in the report
> Case Is >= minimumScore(B)
> getComment = "Good work!"
> end select.
> 'etc.
> end sub
>
> Then here's the second function:
>
> Private Function minimumScore(Gr adeLetter As String) as Double
> Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String
>
> StrSQL = "SELECT tblOptions.minG rade "
> StrSQL = StrSQL & "FROM tblOptions "
> StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"
>
> Set db = CurrentDb
> Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
> minimumScore = rs.Fields(0)
> End Function
>
> But it doesn't work. I'm trying to pass a string into the second function
> (the letter grade) and have it return a double. (like 0.82, for example[/color]
for[color=blue]
> a B.) What am I forgetting?
>
> Thanks.
>
> Rich Hollenbeck
>
>[/color]

[Richard Hollenbeck]

Re: Compile error: byRef argument type mismatch

Thanks for your reply.

B is not a variable. letterGrade is a field in the table tblOptions, as is
minScore, and maxScore. I want to query the minimum value for a B from that
table.

Everything works great when I "hard-wire" concrete values into the code.
For example:

< 62 = F[color=blue]
>= 62 <72 = D
>= 72 < 82 = C
>= 82 < 92 = B
>= 92 = A[/color]

However, if another instructor gets hold of this program but uses different
criteria, the whole thing will need to be rewritten. Some instructors may
want <= 80 to be a B and <= 90 to be an A etc. So I have an options table
where the instructor can set up his or her own prefrences for the grades.

So, when I type " minimumScore(B) ," I'm not referring to any variable
named "B" but "B" is the value of the field [tblOptions].[letterGrade] to
query on in the WHERE clause.


"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:ZO6dnXJv4b A0E-DcRVn-qQ@rogers.com.. .[color=blue]
> How have you defined B? It must be defined as String, since that's what[/color]
your[color=blue]
> function is expecting. If you haven't got Option Explicit turned on (and[/color]
you[color=blue]
> really should), then B will be a Variant, and you'll get the error message
> you described.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message
> news:%Wefd.6585 $8W6.796@trnddc 05...[color=green]
> > I have two functions (one using the other) where I want to pass into it[/color]
> the[color=green]
> > character A, B, C, D, or F and have it return a Double
> > indicating the minimum score it takes to get that grade. For example,
> > here's a stipped-down version of the first function:
> >
> > private function getComment()
> > Select Case txtTotal ' the value in the text box txtTotal in the[/color][/color]
report[color=blue][color=green]
> > Case Is >= minimumScore(B)
> > getComment = "Good work!"
> > end select.
> > 'etc.
> > end sub
> >
> > Then here's the second function:
> >
> > Private Function minimumScore(Gr adeLetter As String) as Double
> > Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String
> >
> > StrSQL = "SELECT tblOptions.minG rade "
> > StrSQL = StrSQL & "FROM tblOptions "
> > StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"
> >
> > Set db = CurrentDb
> > Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
> > minimumScore = rs.Fields(0)
> > End Function
> >
> > But it doesn't work. I'm trying to pass a string into the second[/color][/color]
function[color=blue][color=green]
> > (the letter grade) and have it return a double. (like 0.82, for example[/color]
> for[color=green]
> > a B.) What am I forgetting?
> >
> > Thanks.
> >
> > Rich Hollenbeck
> >
> >[/color]
>
>[/color]

[Richard Hollenbeck]

Re: Compile error: byRef argument type mismatch

Somebody said,

"> <richard.hollen beck@verizon.ne t> declaimed the following in[color=blue]
> comp.databases. ms-access:"[/color]

without even attempting to provide any constructive commentary.

I don't like the implication; according to Webster's Dictionary, to declame
means:

"1 : to speak rhetorically; specifically : to recite something as an
exercise in elocution
2 : to speak pompously or bombastically : HARANGUE
transitive senses : to deliver rhetorically; specifically : to recite in
elocution."

Hey guys, I'm just trying to get a little help! What's up with the
insults? Nothing was rhetoric in the sense of a question intended to be
left unanswered. Nothing was stated as an exercise in "bla-bla-bla!" I am
neither pompous nor bombastic. I am not attempting to harangue. However, I
do need an answer to this question. I am sorry if I offended anybody.
Perhaps I should find a different crowd. Many of you have been very
helpful, so I can, in time overlook the rude behavior of some commentators.
I won't seek another crowd; it's just the feeling of the moment. I'll get
over it.

Still sincerely seeking answers to my question,

:-)

Rich Hollenbeck

"Dennis Lee Bieber" <wlfraed@ix.net com.com> wrote in message
news:ssirn01mbv 2hoom2mtu1ja8a5 6hdfkqjg2@4ax.c om...[color=blue]
> On Mon, 25 Oct 2004 22:16:27 GMT, "Richard Hollenbeck"
> <richard.hollen beck@verizon.ne t> declaimed the following in
> comp.databases. ms-access:
>[color=green]
> > I have two functions (one using the other) where I want to pass into it[/color][/color]
the[color=blue][color=green]
> > character A, B, C, D, or F and have it return a Double
> > indicating the minimum score it takes to get that grade. For example,
> > here's a stipped-down version of the first function:
> >
> > private function getComment()
> > Select Case txtTotal ' the value in the text box txtTotal in the[/color][/color]
report[color=blue][color=green]
> > Case Is >= minimumScore(B)[/color]
>
> Case Is >= minimumScore("B ") 'You need to PASS a string item
> 'a plain B without the
> 'quotes is an undefined
> 'VARIABLE
>[color=green]
> > getComment = "Good work!"
> > end select.
> > 'etc.
> > end sub
> >
> > Then here's the second function:
> >
> > Private Function minimumScore(Gr adeLetter As String) as Double
> > Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String
> >
> > StrSQL = "SELECT tblOptions.minG rade "
> > StrSQL = StrSQL & "FROM tblOptions "
> > StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"
> >
> > Set db = CurrentDb
> > Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
> > minimumScore = rs.Fields(0)
> > End Function
> >
> > But it doesn't work. I'm trying to pass a string into the second[/color][/color]
function[color=blue][color=green]
> > (the letter grade) and have it return a double. (like 0.82, for example[/color][/color]
for[color=blue][color=green]
> > a B.) What am I forgetting?
> >
> > Thanks.
> >
> > Rich Hollenbeck
> >[/color]
>
> --[color=green]
> > =============== =============== =============== =============== == <
> > wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> > wulfraed@dm.net | Bestiaria Support Staff <
> > =============== =============== =============== =============== == <
> > Home Page: <http://www.dm.net/~wulfraed/> <
> > Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color][/color]

[Richard Hollenbeck]

Re: Compile error: byRef argument type mismatch

Oops! I do owe Dennis and this newsgroup an apology. He actually did give
me an answer:
[color=blue][color=green]
> > Case Is >= minimumScore("B ") 'You need to PASS a string item
> > 'a plain B without the
> > 'quotes is an undefined
> > 'VARIABLE[/color][/color]

But I need to explain that I've already tried that and the case is simply
ignored. I know the table has the value "B" in it--I've quadruple-checked
it. So it can't be that it doesn't find a "B" unless I'm doing something
else wrong.

Dennis; Sorry for the comments about you being rude. It was me that was
rude to reply without hunting through your reply. I REALLY didn't see it.
I just thought you were saying that I was speaking rhetorically and didn't
really want an answer. As I went through it line-by-line I found a reply
hidden in there. Perhaps a message at the top of the post will be helpful in
the future. In the future I'll check every line before I make stupid
comments like the ones I made tonight.

MY PROBLEM IS STILL NOT SOLVED.

Many thanks to Doug Steele, Dennis Lee Bieber, and all the rest of you for
your help and patience.

"Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message
news:9Hlfd.2822 $dW.979@trnddc0 8...[color=blue]
> Somebody said,
>
> "> <richard.hollen beck@verizon.ne t> declaimed the following in[color=green]
> > comp.databases. ms-access:"[/color]
>
> without even attempting to provide any constructive commentary.
>
> I don't like the implication; according to Webster's Dictionary, to[/color]
declame[color=blue]
> means:
>
> "1 : to speak rhetorically; specifically : to recite something as an
> exercise in elocution
> 2 : to speak pompously or bombastically : HARANGUE
> transitive senses : to deliver rhetorically; specifically : to recite in
> elocution."
>
> Hey guys, I'm just trying to get a little help! What's up with the
> insults? Nothing was rhetoric in the sense of a question intended to be
> left unanswered. Nothing was stated as an exercise in "bla-bla-bla!" I[/color]
am[color=blue]
> neither pompous nor bombastic. I am not attempting to harangue. However,[/color]
I[color=blue]
> do need an answer to this question. I am sorry if I offended anybody.
> Perhaps I should find a different crowd. Many of you have been very
> helpful, so I can, in time overlook the rude behavior of some[/color]
commentators.[color=blue]
> I won't seek another crowd; it's just the feeling of the moment. I'll get
> over it.
>
> Still sincerely seeking answers to my question,
>
> :-)
>
> Rich Hollenbeck
>
> "Dennis Lee Bieber" <wlfraed@ix.net com.com> wrote in message
> news:ssirn01mbv 2hoom2mtu1ja8a5 6hdfkqjg2@4ax.c om...[color=green]
> > On Mon, 25 Oct 2004 22:16:27 GMT, "Richard Hollenbeck"
> > <richard.hollen beck@verizon.ne t> declaimed the following in
> > comp.databases. ms-access:
> >[color=darkred]
> > > I have two functions (one using the other) where I want to pass into[/color][/color][/color]
it[color=blue]
> the[color=green][color=darkred]
> > > character A, B, C, D, or F and have it return a Double
> > > indicating the minimum score it takes to get that grade. For example,
> > > here's a stipped-down version of the first function:
> > >
> > > private function getComment()
> > > Select Case txtTotal ' the value in the text box txtTotal in the[/color][/color]
> report[color=green][color=darkred]
> > > Case Is >= minimumScore(B)[/color]
> >
> > Case Is >= minimumScore("B ") 'You need to PASS a string item
> > 'a plain B without the
> > 'quotes is an undefined
> > 'VARIABLE
> >[color=darkred]
> > > getComment = "Good work!"
> > > end select.
> > > 'etc.
> > > end sub
> > >
> > > Then here's the second function:
> > >
> > > Private Function minimumScore(Gr adeLetter As String) as Double
> > > Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String
> > >
> > > StrSQL = "SELECT tblOptions.minG rade "
> > > StrSQL = StrSQL & "FROM tblOptions "
> > > StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"
> > >
> > > Set db = CurrentDb
> > > Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
> > > minimumScore = rs.Fields(0)
> > > End Function
> > >
> > > But it doesn't work. I'm trying to pass a string into the second[/color][/color]
> function[color=green][color=darkred]
> > > (the letter grade) and have it return a double. (like 0.82, for[/color][/color][/color]
example[color=blue]
> for[color=green][color=darkred]
> > > a B.) What am I forgetting?
> > >
> > > Thanks.
> > >
> > > Rich Hollenbeck
> > >[/color]
> >
> > --[color=darkred]
> > > =============== =============== =============== =============== == <
> > > wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> > > wulfraed@dm.net | Bestiaria Support Staff <
> > > =============== =============== =============== =============== == <
> > > Home Page: <http://www.dm.net/~wulfraed/> <
> > > Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color][/color]
>
>[/color]

[Edward]

Re: Compile error: byRef argument type mismatch

"Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message news:<9Hlfd.282 2$dW.979@trnddc 08>...[color=blue]
> Somebody said,
>
> "> <richard.hollen beck@verizon.ne t> declaimed the following in[color=green]
> > comp.databases. ms-access:"[/color]
>
> without even attempting to provide any constructive commentary.[/color]

It's a joke, man. Plenty of people set up their responses to be
slightly more personal than

<richard.hollen beck@verizon.ne t> wrote:

For example (from alt.usage.engli sh):

The carbon unit using the name Evan Kirshenbaum
<kirshenbaum@hp l.hp.com> in news:y8vt82jp.f sf@hpl.hp.com gave
utterance as follows:

Edward
--
The reading group's reading group:

http://www.bookgroup.org.uk

[James Fortune]

Re: Compile error: byRef argument type mismatch

"Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message news:<2Ymfd.619 7$LT1.3915@trnd dc09>...[color=blue]
> MY PROBLEM IS STILL NOT SOLVED.
>[/color]

tblOptions
MinScore Long
MaxScore Long
LetterGrade Text

0 61 F
63 71 D
72 81 C
82 91 B
92 100 A

Code behind frmGetComment:
'--------------------------------------
Option Compare Database
Option Explicit

Private Sub cmdGetComment_C lick()
MsgBox (getComment())
End Sub

Private Function getComment() As String
Select Case txtTotal ' the value in the text box txtTotal in the report
Case Is >= minimumScore("B ") '<--- Quotes here
getComment = "Good work!"
Case Else
getComment = "No Comment."
End Select
'etc.
End Function

Private Function minimumScore(Gr adeLetter As String) As Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

StrSQL = "SELECT tblOptions.minS core " '<---- Check variable name
StrSQL = StrSQL & "FROM tblOptions "
StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"

Set db = CurrentDb
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
minimumScore = rs.Fields(0)
End Function
'--------------------------------------

produced the results you want. I hope this helps.

James A. Fortune

[Richard Hollenbeck]

Re: Compile error: byRef argument type mismatch

Thanks, James. I got it to work.

I want the minScore and maxScore to be Doubles because I get decimals in the
calculations. I hate dividing Doubles and Longs together. I added a
message field to the options table (now called tblScoringOptio ns) so the end
user can alter their messages (comments) on the fly just before the report
opens. I also noticed I don't even need a maxScore field since I 'm not
using it. Hey, this is really working great now! Here's what I finally
came up with: (sorry if the wrap-around feature on your news-reader program
screws up the formatting. It should display correctly in NotePad or some
other text reader.)

Private Function getComment()

Dim StrSQL As String, db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb

If txtTotal >= minimumScore("A ") Then
StrSQL = "SELECT tblScoringOptio ns.message FROM
tblScoringOptio ns WHERE tblScoringOptio ns.letterGrade = 'A'"
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close
ElseIf txtTotal >= minimumScore("B ") And txtTotal <
minimumScore("A ") Then
StrSQL = "SELECT tblScoringOptio ns.message FROM
tblScoringOptio ns WHERE tblScoringOptio ns.letterGrade = 'B'"
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close

ElseIf txtTotal >= minimumScore("C ") And txtTotal <
minimumScore("B ") Then
StrSQL = "SELECT tblScoringOptio ns.message FROM
tblScoringOptio ns WHERE tblScoringOptio ns.letterGrade = 'C'"
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close

ElseIf txtTotal >= minimumScore("D ") And txtTotal <
minimumScore("C ") Then
StrSQL = "SELECT tblScoringOptio ns.message FROM
tblScoringOptio ns WHERE tblScoringOptio ns.letterGrade = 'D'"
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close

ElseIf txtTotal < minimumScore("D ") Then
StrSQL = "SELECT tblScoringOptio ns.message FROM
tblScoringOptio ns WHERE tblScoringOptio ns.letterGrade = 'F'"
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close

Else: getComment = "" 'Don't really need another else since all
possibilities are already accounted for.

End If
db.Close
End Function

Private Function minimumScore(Gr adeLetter As String) As Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

StrSQL = "SELECT tblScoringOptio ns.minScore FROM tblScoringOptio ns WHERE
tblScoringOptio ns.letterGrade = '" & GradeLetter & "'"
Set db = CurrentDb
Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
rs.MoveFirst 'Do I really need rs.MoveFirst? There will always only be
one record in this set.
minimumScore = CDbl(rs.Fields( 0)) * 0.01
rs.Close
db.Close
End Function


"James Fortune" <jafortun@oakla nd.edu> wrote in message
news:a6ed3ce7.0 410261217.45069 f9a@posting.goo gle.com...[color=blue]
> "Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message[/color]
news:<2Ymfd.619 7$LT1.3915@trnd dc09>...[color=blue][color=green]
> > MY PROBLEM IS STILL NOT SOLVED.
> >[/color]
>
> tblOptions
> MinScore Long
> MaxScore Long
> LetterGrade Text
>
> 0 61 F
> 63 71 D
> 72 81 C
> 82 91 B
> 92 100 A
>
> Code behind frmGetComment:
> '--------------------------------------
> Option Compare Database
> Option Explicit
>
> Private Sub cmdGetComment_C lick()
> MsgBox (getComment())
> End Sub
>
> Private Function getComment() As String
> Select Case txtTotal ' the value in the text box txtTotal in the report
> Case Is >= minimumScore("B ") '<--- Quotes here
> getComment = "Good work!"
> Case Else
> getComment = "No Comment."
> End Select
> 'etc.
> End Function
>
> Private Function minimumScore(Gr adeLetter As String) As Double
> Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String
>
> StrSQL = "SELECT tblOptions.minS core " '<---- Check variable name
> StrSQL = StrSQL & "FROM tblOptions "
> StrSQL = StrSQL & "WHERE letterGrade = '" & GradeLetter & "'"
>
> Set db = CurrentDb
> Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
> minimumScore = rs.Fields(0)
> End Function
> '--------------------------------------
>
> produced the results you want. I hope this helps.
>
> James A. Fortune[/color]

[James Fortune]

Re: Compile error: byRef argument type mismatch

"Richard Hollenbeck" <richard.hollen beck@verizon.ne t> wrote in message news:<_Myfd.385 4$jD4.2126@trnd dc06>...[color=blue]
>...
> Private Function minimumScore(Gr adeLetter As String) As Double
> Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String
>
> StrSQL = "SELECT tblScoringOptio ns.minScore FROM tblScoringOptio ns WHERE
> tblScoringOptio ns.letterGrade = '" & GradeLetter & "'"
> Set db = CurrentDb
> Set rs = db.OpenRecordse t(StrSQL, dbOpenDynaset)
> rs.MoveFirst 'Do I really need rs.MoveFirst? There will always only be
> one record in this set.
> minimumScore = CDbl(rs.Fields( 0)) * 0.01
> rs.Close
> db.Close
> End Function
>...[/color]

According to the BOF helpfile entry (A97):

"When you open a Recordset object that contains at least one record,
the first record is the current record and the BOF and EOF properties
are False"

but I almost always do the .MoveFirst anyway in case MS changes things
in the future. Also, you are using dbOpenDynaset when it seems that
you only need dbOpenSnapshot. That's O.K. I guess. Keep up the good
work. The important thing is that you are continually coming up with
better and more flexible ways to use Access.

James A. Fortune

"bla bla bla..." Of course, I'm paraphrasing here.
-- Richard Hollenbeck