Solved

access Dcount of yes no field

Posted on 2010-08-15
4
471 Views
Last Modified: 2013-11-28
I have a form in a datasheet view.  I have a few txt fields where I use the Dsum function to show me totals on the form.  Ex: for the Finish column I use:

=DSum("[DrFin]","DoorTagLog","[BatchID] = " & [ID])
This sums up the values in the table DoorTagLog for the DrFin so I can see it in the Main form which is bound to the Main JobsDoor table.

I have a field in the DoorTagLog table that is a yes/no field called DrTagShip.  I have a txt field on my main form called "To Ship".

The ToShip =  number of finished doors - shipped doors.

I have the number of finished doors = DSum("[DrFin]","DoorTagLog","[BatchID] = " & [ID])

I am having a hard time getting the Dcount working to figure out how many doors have shipped. Here is what I have tried so far:

=DCOUNT("[DrTagShip]","DoorTagLog","[BatchID] = " & [ID] and "[DrTagShip] = '-1'")

It returns the same value in each row of the datasheet = 1699  -  I am trying to count the nunber of yes values for each Job.

Any suggestions?

Thanks for your help.

0
Comment
Question by:johnmadigan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 33441059
=DCOUNT("[DrTagShip]","DoorTagLog","[BatchID] = " & [ID] & " and [DrTagShip] = True")
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 33441079
see the difference from your dcount

=DCOUNT("[DrTagShip]","DoorTagLog","[BatchID] = " & [ID] and "[DrTagShip] = '-1'")

revised dcount

=DCOUNT("[DrTagShip]","DoorTagLog","[BatchID] = " & [ID] & " and [DrTagShip] = -1")

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33441114
johnmadigan,Just for reference, internally Access stores boolean True as -1.  Thus, [DrTagShip] = True and [DrTagShip] = -1 are equvalent tests.:)Patrick
0
 

Author Closing Comment

by:johnmadigan
ID: 33441121
Thanks - I knew I was missing something with the syntax - I love a quick fix.

I am usind the Dsum in the txt field of the datasheet - any advantages to  using a subquery in the main form query to do the same thing?

thanks again for the help.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access creating new db - relationships 8 54
Access Need to add combo box to sub form 10 50
Handle Apostrophes in SQL Parameter 16 66
Why can't I change data in my query? 3 35
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question