I have the the following DSum in a query - Totals: DSum("[RentPerAnnum]","Lease","[PropertyRef]='" & [PropertyRef] & "'")
This works well and gives me the total sum in one field of Leases attached to the PropertyRef. However, what I would like is for it only to sum the live records. I have a field [Archived] as a Yes/No check box. I cannot seem to get it to only sum the fields where [Archived] is checked as No. I've tried - Totals: DSum("[RentPerAnnum]","Lease","[PropertyRef]='" & [PropertyRef] & "'" AND [Archived]='False')
but it doesn't work. I've tried other ways but they don't work either. Is there another way to express a check box in the query code?
-
Totals: Format(DSum("[RentPerAnnum]", "Lease", "[PropertyRef]='" & [PropertyRef] & "' AND [Archived] = False"),"Currency")
-
-
Phil
5  1448 
Assuming Archived is a True / False field, you are trying to compare it with a string 'False'
Try -
Totals: DSum("[RentPerAnnum]", "Lease", "[PropertyRef]='" & [PropertyRef] & "' AND [Archived] = False")
-
Note also the change of punctuation by the word AND
Phil
Hi Phil, worked perfectly. Thank you. Is there a way to format it into currency in the code? I cannot do this on the query or text box within the form?
-
Totals: Format(DSum("[RentPerAnnum]", "Lease", "[PropertyRef]='" & [PropertyRef] & "' AND [Archived] = False"),"Currency")
-
-
Phil
Thank you Phil. When your solutions are produced, it makes them look so simple!
Been playing with Access for nearly 25 years. Getting too long in the tooth to learn new tricks.
Phil
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rolan |
last post by:
I'm using Access 97 and need some assistance in sorting out a proper
DSum expression, or maybe even DCount might be an alternative. I have
tried numerous combinations, but with no apparent success....
|
by: Dalan |
last post by:
I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.
First some...
|
by: Alex |
last post by:
Acc 97
Hi,
I have the following in my query which works well if it is all set to
numbers on table design.
But what I want to do is where it states MC (short for machine name) use the
actual...
|
by: John Baker |
last post by:
Hi:
I have a form, and wish to show on the form the current total for a single field on a
table. I have set this up thus: =DSum(,!) in an unbound
text field. These are correct field and table...
|
by: Mark Reed |
last post by:
All,
I have never used this function before and am not sure it what I need.
Just to clarify, I have a report based on a query which has amoungst other
field, wk, parea & packs_req. What I am...
|
by: beppe005 |
last post by:
I would like to calculate a total for all the field with the same
in a query.
I don't know how to set the "criteria" for this dsum function, it should be
something like = but it doesn't make...
|
by: gbb0330 |
last post by:
Hi All
i am trying to calculate Quantity on hand in a unbound textbox in a
form with a subform
QonHand=Qreceived - TotalQSold
TotalQsold=QsoldOnEbay+QsoldThruOtherChannels
here is my...
|
by: potassium flower |
last post by:
I am trying to create a food order system for a restaurant.
I have tried using both the DSum and Sum functions to calculate the total cost of an order. The total cost is a textbox on the form...
|
by: danielgoss |
last post by:
Hi
I have a report that has loads of textboxes that calculate things based on the value on another textbox in the report. I have put a hidden textbox on my report that gets its value from an...
|
by: majapa |
last post by:
To start, here is my DSum expression:
Develop: Nz(DSum(". & "] + . & "]","CSO","='Ability to Develop'")*-1,0)
I actually have 4 of these, each one with a different option for .
For more context,...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
