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

Compile error: byRef argument type mismatch

P: n/a
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(GradeLetter As String) as Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

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

Set db = CurrentDb
Set rs = db.OpenRecordset(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
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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" <ri****************@verizon.net> wrote in message
news:%Wefd.6585$8W6.796@trnddc05...
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(GradeLetter As String) as Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

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

Set db = CurrentDb
Set rs = db.OpenRecordset(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

Nov 13 '05 #2

P: n/a
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
= 62 <72 = D
= 72 < 82 = C
= 82 < 92 = B
= 92 = A
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_djsteele@NOSPAM_canada.com> wrote in message
news:ZO********************@rogers.com... 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" <ri****************@verizon.net> wrote in message
news:%Wefd.6585$8W6.796@trnddc05...
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(GradeLetter As String) as Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

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

Set db = CurrentDb
Set rs = db.OpenRecordset(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


Nov 13 '05 #3

P: n/a
Somebody said,

"> <ri****************@verizon.net> declaimed the following in
comp.databases.ms-access:"
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" <wl*****@ix.netcom.com> wrote in message
news:ss********************************@4ax.com... On Mon, 25 Oct 2004 22:16:27 GMT, "Richard Hollenbeck"
<ri****************@verizon.net> declaimed the following in
comp.databases.ms-access:
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)


Case Is >= minimumScore("B") 'You need to PASS a string item
'a plain B without the
'quotes is an undefined
'VARIABLE
getComment = "Good work!"
end select.
'etc.
end sub

Then here's the second function:

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

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

Set db = CurrentDb
Set rs = db.OpenRecordset(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


--
> ================================================== ============ <
> wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wu******@dm.net | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <

Nov 13 '05 #4

P: n/a
Oops! I do owe Dennis and this newsgroup an apology. He actually did give
me an answer:
Case Is >= minimumScore("B") 'You need to PASS a string item
'a plain B without the
'quotes is an undefined
'VARIABLE
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" <ri****************@verizon.net> wrote in message
news:9Hlfd.2822$dW.979@trnddc08...
Somebody said,

"> <ri****************@verizon.net> declaimed the following in
comp.databases.ms-access:"
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" <wl*****@ix.netcom.com> wrote in message
news:ss********************************@4ax.com...
On Mon, 25 Oct 2004 22:16:27 GMT, "Richard Hollenbeck"
<ri****************@verizon.net> declaimed the following in
comp.databases.ms-access:
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)
Case Is >= minimumScore("B") 'You need to PASS a string item
'a plain B without the
'quotes is an undefined
'VARIABLE
getComment = "Good work!"
end select.
'etc.
end sub

Then here's the second function:

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

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

Set db = CurrentDb
Set rs = db.OpenRecordset(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


--
> ================================================== ============ <
> wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wu******@dm.net | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <


Nov 13 '05 #5

P: n/a
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message news:<9Hlfd.2822$dW.979@trnddc08>...
Somebody said,

"> <ri****************@verizon.net> declaimed the following in
comp.databases.ms-access:"


without even attempting to provide any constructive commentary.


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

<ri****************@verizon.net> wrote:

For example (from alt.usage.english):

The carbon unit using the name Evan Kirshenbaum
<ki*********@hpl.hp.com> in news:y8**********@hpl.hp.com gave
utterance as follows:

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
Nov 13 '05 #6

P: n/a
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message news:<2Ymfd.6197$LT1.3915@trnddc09>...
MY PROBLEM IS STILL NOT SOLVED.


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_Click()
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(GradeLetter As String) As Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

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

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

produced the results you want. I hope this helps.

James A. Fortune
Nov 13 '05 #7

P: n/a
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 tblScoringOptions) 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 tblScoringOptions.message FROM
tblScoringOptions WHERE tblScoringOptions.letterGrade = 'A'"
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close
ElseIf txtTotal >= minimumScore("B") And txtTotal <
minimumScore("A") Then
StrSQL = "SELECT tblScoringOptions.message FROM
tblScoringOptions WHERE tblScoringOptions.letterGrade = 'B'"
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
getComment = rs.Fields(0)
rs.Close

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

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

ElseIf txtTotal < minimumScore("D") Then
StrSQL = "SELECT tblScoringOptions.message FROM
tblScoringOptions WHERE tblScoringOptions.letterGrade = 'F'"
Set rs = db.OpenRecordset(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(GradeLetter As String) As Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

StrSQL = "SELECT tblScoringOptions.minScore FROM tblScoringOptions WHERE
tblScoringOptions.letterGrade = '" & GradeLetter & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(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" <ja******@oakland.edu> wrote in message
news:a6**************************@posting.google.c om...
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message

news:<2Ymfd.6197$LT1.3915@trnddc09>...
MY PROBLEM IS STILL NOT SOLVED.


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_Click()
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(GradeLetter As String) As Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

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

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

produced the results you want. I hope this helps.

James A. Fortune

Nov 13 '05 #8

P: n/a
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message news:<_Myfd.3854$jD4.2126@trnddc06>...
...
Private Function minimumScore(GradeLetter As String) As Double
Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

StrSQL = "SELECT tblScoringOptions.minScore FROM tblScoringOptions WHERE
tblScoringOptions.letterGrade = '" & GradeLetter & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(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
...


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
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.