Data representation format mistake

[MikeB]

I have an Access table [Games] with fields for White and Black's
scores (chess game).

The fields are defined in Access as :Field Size (Double)
Format(Standard) Decimal Places (1).

The field can really only have 3 values and the values in the
BlackScore and WhiteScore are interrelated. The values are 1 - Win, 0
- Lose, 0.5 - Draw. I see the values fine if I look in the table and
in my reports and forms. There doesn't seem to be a problem.

Now I'm writing the following program:

Option Compare Database
Option Explicit

Sub x1()
Dim dbs As DAO.Database
Dim rsGames As DAO.Recordset
Dim rsWhitePlayer As DAO.Recordset
Dim rsBlackPlayer As DAO.Recordset
Dim strSQLGames As String
Dim strSQLPlayer As String

Dim strGameNumber, strmatchNumber, strWhite, strBlack As String
Dim lngWhiteScore, lngBlackScore As Long
Dim strWhiteName, strBlackName As String
Dim intWhiteRank, intBlackRank As Integer

Set dbs = CurrentDb

strSQLGames = "SELECT * from [Games] " & _
"WHERE (([White] Not In (50,51)) AND ([Black] Not In
(50,51)))"
strSQLPlayer = "Select * from [Players] where [PlayerNumber] ="
Set rsGames = dbs.OpenRecordset(strSQLGames, dbOpenDynaset)

Do While Not rsGames.EOF
strGameNumber = rsGames!GameNumber
strmatchNumber = rsGames!MatchNumber
strWhite = rsGames!White
strBlack = rsGames!Black
lngWhiteScore = rsGames!WhiteScore
lngBlackScore = rsGames!BlackScore
Set rsWhitePlayer = dbs.OpenRecordset(strSQLPlayer & strWhite, _
dbOpenDynaset)
Set rsBlackPlayer = dbs.OpenRecordset(strSQLPlayer & strBlack, _
dbOpenDynaset)
strWhiteName = rsWhitePlayer!FirstName & " " & rsWhitePlayer!
LastName
strBlackName = rsBlackPlayer!FirstName & " " & rsBlackPlayer!
LastName
intWhiteRank = rsWhitePlayer!Ranking
intBlackRank = rsBlackPlayer!Ranking

Debug.Print "Game(" & strGameNumber & ")" _
; Tab(12); "Match(" & strmatchNumber & ")" _
; Tab(25); "White:" & "(" & strWhite & ")" &
strWhiteName & _
"(" & intWhiteRank & ")" _
; Tab(60); "Black:" & "(" & strBlack & ")" &
strBlackName & _
"(" & intBlackRank & ")", _
lngWhiteScore, lngBlackScore

rsGames.MoveNext
Loop

rsGames.Close
Set rsGames = Nothing


End Sub

The weirdness is that when the game is a draw, I get WhiteScore as 0.5
but BlackScore as 0.

If I change the DIM statement for these two variables as follows:

Dim lngWhiteScore as Long
Dim lngBlackScore As Long

Then both print out as 0!?!?!

This has to be a stupid newbie mistake. Please help.

And OK, I was going to wait until I had everything working, but is
this the most efficient way to read the data from the tables?

Next step will be to update the ranking of each player and write the
new value back and also mark the game record as having been ranked.

Thanks.

 

[Steve Schapel]

Mike,

In this line in your code:
Dim lngWhiteScore, lngBlackScore As Long
.... you are dimensioning lngBlackScore as Long, which means it is an
integer, which means that no decimal places will be computed. Whereas
you do not typecast the lngWhiteScore at all, so it will be the default
type for a variable, which is a Variant, so it will allow the decimal
places.

In fact, you told us that in the tables, these score fields are defined
as Double, so to be consistent it would make sense in your code to do
like this:
Dim lngWhiteScore As Double, lngBlackScore As Double

But for a value which will only ever have a decimal value of 0.5 I would
consider using a Currency data type. Seems strange, I know, but that's
what I always do in such cases, as it allows a simpler exact
representation of the decimal values, and you can just format it to not
show the currency symbol etc.

It would appear you may have misunderstood the requirements when
declaring variables. For example:
Dim strGameNumber, strmatchNumber, strWhite, strBlack As String
.... this code dimensions strBlack as a string variable, but
strGameNumber, strmatchNumber, and strWhite, will all be Variants
because you have not typed them.

--
Steve Schapel, Microsoft Access MVP

I have an Access table [Games] with fields for White and Black's
scores (chess game).

The fields are defined in Access as :Field Size (Double)
Format(Standard) Decimal Places (1).

The field can really only have 3 values and the values in the
BlackScore and WhiteScore are interrelated. The values are 1 - Win, 0
- Lose, 0.5 - Draw. I see the values fine if I look in the table and
in my reports and forms. There doesn't seem to be a problem.

Now I'm writing the following program:

Option Compare Database
Option Explicit

Sub x1()
Dim dbs As DAO.Database
Dim rsGames As DAO.Recordset
Dim rsWhitePlayer As DAO.Recordset
Dim rsBlackPlayer As DAO.Recordset
Dim strSQLGames As String
Dim strSQLPlayer As String

Dim strGameNumber, strmatchNumber, strWhite, strBlack As String
Dim lngWhiteScore, lngBlackScore As Long
Dim strWhiteName, strBlackName As String
Dim intWhiteRank, intBlackRank As Integer

Set dbs = CurrentDb

strSQLGames = "SELECT * from [Games] " & _
"WHERE (([White] Not In (50,51)) AND ([Black] Not In
(50,51)))"
strSQLPlayer = "Select * from [Players] where [PlayerNumber] ="
Set rsGames = dbs.OpenRecordset(strSQLGames, dbOpenDynaset)

Do While Not rsGames.EOF
strGameNumber = rsGames!GameNumber
strmatchNumber = rsGames!MatchNumber
strWhite = rsGames!White
strBlack = rsGames!Black
lngWhiteScore = rsGames!WhiteScore
lngBlackScore = rsGames!BlackScore
Set rsWhitePlayer = dbs.OpenRecordset(strSQLPlayer & strWhite, _
dbOpenDynaset)
Set rsBlackPlayer = dbs.OpenRecordset(strSQLPlayer & strBlack, _
dbOpenDynaset)
strWhiteName = rsWhitePlayer!FirstName & " " & rsWhitePlayer!
LastName
strBlackName = rsBlackPlayer!FirstName & " " & rsBlackPlayer!
LastName
intWhiteRank = rsWhitePlayer!Ranking
intBlackRank = rsBlackPlayer!Ranking

Debug.Print "Game(" & strGameNumber & ")" _
; Tab(12); "Match(" & strmatchNumber & ")" _
; Tab(25); "White:" & "(" & strWhite & ")" &
strWhiteName & _
"(" & intWhiteRank & ")" _
; Tab(60); "Black:" & "(" & strBlack & ")" &
strBlackName & _
"(" & intBlackRank & ")", _
lngWhiteScore, lngBlackScore

rsGames.MoveNext
Loop

rsGames.Close
Set rsGames = Nothing


End Sub

The weirdness is that when the game is a draw, I get WhiteScore as 0.5
but BlackScore as 0.

If I change the DIM statement for these two variables as follows:

Dim lngWhiteScore as Long
Dim lngBlackScore As Long

Then both print out as 0!?!?!

This has to be a stupid newbie mistake. Please help.

And OK, I was going to wait until I had everything working, but is
this the most efficient way to read the data from the tables?

Next step will be to update the ranking of each player and write the
new value back and also mark the game record as having been ranked.

Thanks.

 

[MikeB]

Duh! Thanks, as I said - it *had* to be a newbie mistake. I mixed up
"long" and "double". Does Currency type have any overhead? I think I
looked into currency and I couldn't find a way to format it with a
single decimal place, but I might be mistaken, it was a while ago.
I'll look into that again.

OK, thanks for the advice.

So I cannot type a number of variables on one line without expressly
doing the "As type" for each of them? Bah. There has to be some
shorthand, no?

Mike

 

[Steve Schapel]

Mike,

So I cannot type a number of variables on one line without expressly
doing the "As type" for each of them? Bah. There has to be some
shorthand, no?

As you say, No.