DCount question

[LarryJ]

I have this code:



Dim RowCount
RowCount = 0
RowCount = DCount("*", "PlayerQuery")

Even when the query, when run outside the function returns 0 rows, RowCount is always 1. What am I doing wrong here?
I am using this to determine if I need to update (which is whole other thing) or insert a row on the table.

[Micron]

when run outside the function returns 0 rows

You get 0 rows if you simply open the query from the nav pane? Or 1 row? Since you're using Dcount without criteria you are asking for the count of a domain (the query) and your result should be whatever the query sql was last saved as. Try this with the immediate window to illustrate:
Open query from nav pane, check the row count. In immediate window enter ?DCount("*", "PlayerQuery") and hit Enter. Returned number should be the same.
Now add criteria to query in design view so that no records are returned. Run it but don't save it. Put cursor at the end of the line in the immediate window and hit Enter again. Result should be the same as it was before but not agree with what your query just returned.
Go back and save the query and repeat in immediate window. The number returned should now be zero.
In short, DCount function without criteria runs your query as it was last saved. Maybe that is the reason for your results.

[LarryJ]

When I run the query:

SELECT Count(All_Teams.Team) AS CountOfPlayer
FROM All_Teams
WHERE (((All_Teams.Team)=[TeamSelect].[Value]) And ((All_Teams.Name)=[PlayerName].[Value]));

I got zero rows. When I reference it in the DCount function I always get 1.

[Micron]

Can you post a db copy to eliminate a lot of back and forth posting? If so, copy db, compact/repair and zip file because IIRC, forum max is 500kb for uploads. See "How to attach files" at the top of this page if you need help.

[LarryJ]

Hopefully it can be opened. It is the Origlnal Ratings Calculator where the code can be found.

Sherco Classic_2023-08-20_(1).zip

[davegri]

PlayerQuery returns one row only.
You are counting the rows in a query that always returns one row. The result will always be one.

[Micron]

Some instructions on what to do would help. This is the PlayerQuery or did you use that in a control as its row source (or other)?

SELECT Count(All_Teams.Team) AS CountOfPlayer
FROM All_Teams
WHERE (((All_Teams.Team)=[TeamSelect].[Value]) And ((All_Teams.Name)=[PlayerName].[Value]));

If it's the query, it has no knowledge of which form you want to get the criteria from.
BTW, you've designed your tables like spreadsheets and you will often struggle to get info in/out of the db as a result.

[LarryJ]

That is the query. I'm just trying to get the DCount to work properly. Hardcoding it hasn't been working. The tables were created by importing from Excel. At some point I should look at other ways to make them.

[Welshgasman]

So test for 1 instead of 0 for that totals query?

[LarryJ]

Based upon Micron's reply, I need to add a me. to the form fields. And yes, I'm feeling very newbie right now.

[June7]

Me. is VBA shorthand for form or report name code is behind. It works only in VBA, not in Access objects. Consider this coding trick - use With some object reference:

With Me
.POPerGame = Round((.PutOuts / .GiF), 2)
.AssistsPerGame = Round((.Assists / .GiF), 3)
.FieldingPercentage = Round(((.PutOuts + .Assists) / .Chances), 3)
Select Case .Pos
    Case "P"
        If .AssistsPerGame >= 0.7 Then
            .DEF = "9"
        Else
            .DEF = "8"
        End If
        If .POPerGame >= 0.3 Then
            .DEF = .DEF & "5"
        Else
            .DEF = .DEF & "4"
        End If
        If .FieldingPercentage >= 0.99 Then
            .DEF = .DEF & "S"
        End If
...
End With

In TeamList, why did you not use Autonumber field as primary key? They perform more efficiently than text fields.


In [Sherco Classic] form:
Why bother with variables?
.[Value] is also not necessary.
Can shorten SQL statement and use line continuation:

Private Sub AwayTeam_AfterUpdate()
Me.AwayRoster.RowSource = "SELECT Name, B, T, Pos, Pos2, Pos3, Pos4, DEF, BB, K, Clutch, Offense, HR, " & _ 
        "Triple, Spd, Gopher, Rate, IE, Rate2, Walk, Strikeout FROM All_Teams WHERE Team=[AwayTeam];"
End Sub

Name is a reserved word - advise not to use reserved words as names. Also, no spaces nor punctuation/special characters (underscore is only exception).

[LarryJ]

Clearly I have been guilty of thinking in PLSQL and not Access coding. Need to work on that.

[June7]

And consider this for the [Sherco Classic] form:

Private Sub AwayTeam_AfterUpdate()
GetRoster "Away"
End Sub

Private Sub HomeTeam_AfterUpdate()
GetRoster "Home"
End Sub

Sub GetRoster(strT)
Me(strT & "Roster").RowSource = "SELECT Name, B, T, Pos, Pos2, Pos3, Pos4, DEF, BB, K, Clutch, Offense, HR, " & _
        "Triple, Spd, Gopher, Rate, IE, Rate2, Walk, Strikeout FROM All_Teams WHERE Team=[" & strT & "Team"];"
End Sub

[Micron]

I'm kind of busy so didn't study the db much but I'd say all the advice given should be taken. To the above I'll add - don't mix data types in a field (e.g. 1903_Pirates).
Don't start a rebuild without reading all of the below, and not until you understand normalization. You could start a thread on you intended tables design and relationships if you think it might be wise to get input. At first I thought this was about real baseball stats but now think it's about something like a board game so that will likely make some difference.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html

Last/First/DLast/DFirst Explained (in case you intend to ever use these)
- https://codekabinett.com/rdumps.php?...ted-result-set

[LarryJ]

I started using the after update functions on one of the other forms. It does make life easier. With all the help I'm getting, I will stop being such a rookie in no time.