473,320 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem using calculated expression within Dcount

Hello,

All help is appreciated, I'm struggling with the following.

Using Access 2003 (front end) connecting to Oracle 7 database (back end). I'm trying to create a query in access that will show how many instances of something happens.

Within the query I am using a calculated filed called "registered".The syntax is

Registered: DateDiff("w",[date_valid],[timestamp])

This returns a number 0.00, 1.00, 2.00 etc

Within the query or form (I've tried both), I'm trying to count the number times that 0.00 appears. I will then take this figure and use it to calculate the percentage. My problem is when using the dcount function to count the instances that something appears. I've tried the following

DCount("salary","Applications","registered=0.00")

This is where the errors start, I've also tried

DCount("salary","Applications","(DateDiff(""w"",[date_valid],[timestamp]))=1.00")

but this also errors with the same message. The error message always refers to "Start Date" which is one of the parameters of "date_valid" (the other being "End Date"). I've removed this parameter but it still asks for it.

Where am I going wrong?
Apr 23 '07 #1
3 2039
MMcCarthy
14,534 Expert Mod 8TB
In a new query based on the first query ...
Expand|Select|Wrap|Line Numbers
  1. Count0: Sum(IIf(Registered=0.00,1,0))
  2. Count1: Sum(IIf(Registered=1.00,1,0))
  3. Count2: Sum(IIf(Registered=2.00,1,0))
  4.  
Mary
Apr 24 '07 #2
In a new query based on the first query ...
Expand|Select|Wrap|Line Numbers
  1. Count0: Sum(IIf(Registered=0.00,1,0))
  2. Count1: Sum(IIf(Registered=1.00,1,0))
  3. Count2: Sum(IIf(Registered=2.00,1,0))
  4.  
Mary
Thanks for that, you have solved my problem, I didn't know about the IIF function.

Thanks again
Apr 24 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Thanks for that, you have solved my problem, I didn't know about the IIF function.

Thanks again
You're welcome.
Apr 24 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Richard Holliingsworth | last post by:
Hello: Thanks for reading this post. I need to create a metrics (form or report - I don't care which) to display calculated fields about the database (A2002 front end to SQL Server 2K) 1) I...
1
by: Judy | last post by:
I have the following table in part --- TblClassDate ClassDateID CourseID ClassDate Classes for courses are held on multiple days. The data for a course in the table might look like:...
2
by: Samaba via AccessMonster.com | last post by:
I need to create a calculated control which will count the number of cases if the is "open" and the is "a" for a calendar quarter. I have been struggling with which formula would work best:...
6
by: geronimo_me | last post by:
I have 20 queries that compare fields in one table with fields in another table - the query results are the records that do not match in Table1 and Table2. ie Table1 DOB 28/02/78 Table2 DOB...
6
by: jstaggs39 | last post by:
I want to create a Dcount and an If...Then...Else statement to count the number of records in a table based on the date that is entered to run the form. The If....Else statment comes in because if...
0
by: Chris Ericoli | last post by:
Hi, I am working with an 'in session' ado dataset with an asp.net application. My dataset is comprised of two tables, one of which maintains a few calculated datacolumns. For some reason these...
3
by: Saxman | last post by:
=DCount("*","Clients"," = '1'")+DCount("*","Clients"," = '1'")+DCount("*","Clients"," = '1'")+DCount("*","Clients"," = '1'")+DCount("*","Clients"," = '1'")+DCount("*","Clients"," = '1'") The...
7
by: Michael R | last post by:
Good afternoon. I'm stucked in composing the syntax for DCount expression in a select query. The query qryCustomers has CustomerID field, the DCount function uses tblLoans with LoanDate and Id fields...
2
by: MusicMogul | last post by:
Hey Everyone, I'm trying to evaluate the DCOUNT expression. If there were no values found, it gives a "#Error" in my report. That "#Error" message doesn't look good on a report. How can I...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.