Syntax For DSum Function For Currency Fields

Nov 25, 2006

I have a Number field with value as
DSum function where the criteria is
Textfield which works well.

=DSum("[MCRef]","Miracle_Cloth_Main","[Cust_ID]='" & [Cust_ID] & "'")

It doesn't show the SUM when the type of
textBox is CURRENCY

Can anyone suggest IF Ineed to change the syntax ?

View Replies


ADVERTISEMENT

Dsum Function In A Query

Mar 19, 2007

hello
i have a query summing sales data by month.
what i need to do is be able to list in this query the sales summed for same period last year.
I have some other criteria but this is the just of it.
trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.

example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.

I can not get the dsum function to work???
any
ideas????

i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either.
If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.

View 1 Replies View Related

Aggregate Function DSUM

Feb 17, 2008

Hi All,

I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.

To calculate the TDAYS i need to SUM the DAYS column for every employee.

When an employee again log in the application he should be aware of the total number of leaves which he has already availed.

Please help me for the same as I am unable to do so.

Kindly revert back for any queries or any further information.

With Warm Regards,
Sanjay

View 3 Replies View Related

Pareto Graph And The DSum Function

May 1, 2008

Hi All,

I currently have a Pareto graph created by a query, that counts occurence and then uses the Dsum function to create the cumalative %. The function is as follows:

CumPct: DSum("[CountofConcern_Number]","qry_ProbByConcern","[CountofConcern_Number]>=" & [Total Concerns] & "")/DSum("[CountofConcern_Number]","qry_ProbByConcern")

This is as per MS article: http://support.microsoft.com/kb/208373

Now this is OK and is as per the MS article but if you have counts that are the same, it just give the same cumalative % for each one that is the same. Please see attached image for an example of what this query produces.

Does anyone know how I can sort this out? TIA

View 1 Replies View Related

Forms :: DSum Function In Form

Dec 7, 2013

How is the collection of a sum of money to a particular substance.There is, for example, in the table named baby milk product.How can I collect the amounts of such material only.I used a DSum

View 6 Replies View Related

DSum Function To Sum Sales Of Each Customer?

Jun 5, 2014

Can Dsum function sums the sales of each customer. For example, let say we have the following query

Code:
QueryCustomerSales
Customer Name
Order ID
Item
Amount
Price

So, can i get output something like :

Code:
John 200.540 $
Mark 300.350 $

I tried the following code "

Code:
Dsum ("[Amount] * [Price]", "QueryCustomerSales", "[Customer Name]= [Customer Name]")

However, it returns the sales of all customers (all together ):

Code:
John 500.890 $
Mark 500.890 $

View 5 Replies View Related

Forms :: Getting Error With DSum Function In Subform?

Feb 16, 2015

How to use Dsum Function? Am using below following Dsum functions but they are not working and shown an error result.

=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]=MILK"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]='MILK'"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]<>MILK"),0)

View 3 Replies View Related

Queries :: Format Date Parameter In Dsum - Undefined Function

Sep 25, 2014

I m using Access 2010.I m Facing a problem in the undermentioned expression,

OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)

when i run the query it gives "Undefined Function 'Format' in Expression" ?

View 3 Replies View Related

Dsum Need 2 Fields In Calc

Jun 11, 2006

I have a Qry field:

TotalUsage: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblDetail]",),4)*100))

the problem is that the DSUM needs to sum up all the like records using the same SubID. I need some extra code in the divisor part. EG

subID uomUsage TotalUsage
33 10 100
33 40 100
33 50 100

34 43 100
34 57 100

the dsum needs to use SubID in order to sum correcly EG on SubID 33 as 100 uomUsage.
SubID is a Number field, Long integer.


I am presently getting wrong info. the formula above Dsum is summing as 200 since i eroneously don't have Subid in the Qry calc.

How can i fix? Thanks

Penwood

View 1 Replies View Related

Customizing Currency Fields

Feb 5, 2007

Hi,

I am creating a database of private equity funds. I want to have a currency field for an input entitled fund size. However, I need to be able to display whether the size of the fund is in dollars or euros. How can customize a number/currency field to allow me to input the correct currency symbol?

Thanks

View 3 Replies View Related

Adding Currency Fields Up

Aug 15, 2007

Hi,

I have tried to add up fields in a table but am struggling to get it to work. Can anyone help please.

In design view of tables, I have four fields (that I will be using in a a form eventually);

RaffleIncome
AlmsIncome
OtherIncome
TotalIncome

I want to get the TotalIncome field to automatically be the total sum of any or all of the other three.

I have tried putting =[RaffleIncome]+[Almsincome]+OtherIncome} in the control box of the TotalIncome field (a guess) in a form I made, and it gives me the total sum of the others three but does not save when I look at it in the table view. (that is, the TotalIncome column remains £0.00)

I am learning as I go along and am okay figuring some stuff out, but this has got me a bit frustrated, and I guess I cannot see the wood for the trees !

Thanks anyone

Adrian

View 6 Replies View Related

General :: Changing All Currency Fields

Aug 20, 2012

How do I change all the currency fields (many many of them....)from $ to currency without going in and changing every form and fields manually.

View 3 Replies View Related

Tables :: How To Change Default Currency In Access 2013 To A Foreign Currency

Dec 20, 2012

how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?

View 3 Replies View Related

Forms :: Sorting Fields - Syntax Error (Missing Operator) In Query Expression

Sep 16, 2013

I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:

Syntax Error (Missing Operator) In (Field Name)

I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.

I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.

View 7 Replies View Related

Multiply Fields, Using The Sum Function.

Mar 17, 2008

Hi all, wondering if anyone can help me.

Im trying to a run a query to see the results of my table. but i am experincing problems, when use the SUM function.

I have constructed a query with three fields.
1) QTY.
2) Cost
3) Total.

what i would like is to be able to Multiply QTY & Cost to get the total.

so whan QTY Is 0 and Cost is £0.00. the query recongises it and changes the total should then be £0.00.

any advice on enabling the query is welcome.

View 12 Replies View Related

Modules & VBA :: Function That Loops Through A Set Of Fields Seeking A Non Zero Value?

Apr 17, 2014

The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.

What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.

First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional.

As an example, if I had a product like this:

Product: X
SalesID: 1111
Price: 9.99
Month 1: 0
Month 2: 0
Month 3: 1582
Month 4: 2790
Month 5: 4501
Month 6: 4210

Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.

So in the new table, I would have:

Product: X
SalesID: 1111
Price: 9.99
Month 1: 1582
Month 2: 2790
Month 3: 4501
Month 4: 4210

View 3 Replies View Related

Queries :: MIN Function With Additional Fields Displayed

Feb 5, 2015

I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.

View 3 Replies View Related

Tables :: Turning Off Rounding Function In Calculated Fields

Jan 22, 2013

Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.

Then the result is used to calculate how much loose coin will remain.

But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.

View 4 Replies View Related

Modules & VBA :: List Fields Via Table Info Function

Nov 5, 2013

I a trying to list all fields from ALL tables in a certain DB.I am using the tableinfo function. However, because the output of the immediate window is limited to 200 lines, I can only see the last 200 fields. Is there a way to export this in another way than the debug.print procedure, so I can bypass this limitation?

Code:

Public Sub showtablefields()
Dim db As Database
Dim tdl As TableDef

[code]...

View 5 Replies View Related

Using Countif Function For Multiple Fields From Specific Record

May 28, 2015

Using Access 2010

I'm an expert user in Excel and am trying to learn more about Access, so I relate many of the functions to what they are called in Excel. And what I'm trying to do right now is count the number of fields in a specific record where the value is a specific result (CountIf).

For example, I need to return the number of fields in a specific record where the value is "Meets Requirements". The specific record will be determined by the Name, Contact, and Email fields (which together comprise the Primary Key).

Is there a way to count the values in multiple fields that equal "Meets Requirements"?

View 7 Replies View Related

Queries :: DateDiff Function - Calculate Difference In Dates Between Two Fields

Apr 22, 2013

I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.

View 3 Replies View Related

How To Handle Null Fields Requiring Numeric And Text Function Simultaaneously

Jan 8, 2007

Hi friends,

I am new to this forum, and I am facing a problem while learning MS-Access.

I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables.
A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.

The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it.
I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.

The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :

Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])

It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.

Can anybody help me at this?

pc

View 5 Replies View Related

Forms :: Manage MsgBox With Blank Required Fields By Function In A Module

May 11, 2013

I created this function to manage a MsgBox containing all required fields with no data:

Code:
Function FormValidation(TheForm As Form) As Boolean
Dim boolResponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolResponse = True
strError = Null

[code]...

Then, I have a Form_BeforeUpdate event, where I wanna place the function, which contains also some VBA code to manage duplicates records:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, dbs As DAO.Database, strICAO As String
Dim ctl As Control, txtMsg As String

[code]....

Now, how to add the function in this event to get these two results:

1. if required fields are blank and I press OK on the MsgBox, the routine must stop;
2. the focus must go in the first required blank control.

I tried some options but I get different kind of malfunctions: no custom message for duplicate records but only the access default one, "go to next record" feature with tab key not working and so on.

View 6 Replies View Related

Modules & VBA :: Public Function With Variables Referencing Table Date Fields

May 31, 2014

I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.

Here is the code:

Option Compare Database
Option Explicit
Public Function getCurrentRepDates() As Date
Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")

[Code] ....

I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010

View 5 Replies View Related

Should I Use DSUM?

Jul 30, 2007

I want to add to values based on if yes or no is selected in an option group.

My thinking is to use DSUM. When 'yes' is selected I want to grab a value from one field and add it to a value in another field giving a running total. I am currently using the following code but it throws back an error. Is DSUM the way to go?

Case 1

txtTotal.Value = DSum("[s1]", "[s2]")

View 3 Replies View Related

Using Dsum()???

Mar 29, 2008

Hi there guys, ill keep it short and snappy,

Im trying to use the Dsum() function to add together the "Quantities" of specific products bought in transactions i.e. the total quantity for a product called "red t-shirt" and the total quantity for another product called "blue t-shirt". the following was the code i tried to use but it just adds together all quantities of all products:

DSum("[Quantity]","Query2","[Product Name] =[Product Name]")

Does anyone have any ideas at all please?

Thanks

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved