Counting Dates And Null Values In Charts

Nov 29, 2007

Hi, I searched the forum for this but the only thread that came close to what I was looking for was this.

http://www.access-programmers.co.uk/forums/showthread.php?t=125240&highlight=null+chart

Basically I have chart in a report thats based on a query that counts the amount of entries per month between two dates inputted by the user.

It all works fine but the chart that is based on the query only shows months that have an entry.

Eg if it counts all dates between the two dates and say the only month that has an entry is July, the chart will only show July. What I want is the other months to show (Null values) as zero, so every month shows. I'm probably missing something basic but can anyone help?

[TextPriDate] is the start date
[TextPriDate2] is the end date

This is the query code (QryDate)
SELECT
tblMain.ID1, tblMain.Dt
FROM
tblMain
WHERE
(((tblMain.Dt) Between [Forms]![frmSwitchboard]![TextPriDate] And [Forms]![frmSwitchboard]![TextPriDate2]));

This the code from the chart in the Report
SELECT
(Format([Dt],"MMM 'YY")) AS Expr1, Count(*) AS [Count]
FROM
QryDate
GROUP BY
(Format([Dt],"MMM 'YY")), (Year([Dt])*12+Month([Dt])-1);

Thanks

View Replies


ADVERTISEMENT

Queries :: Query Filter Based On Dates - Excludes Null Values

Apr 17, 2014

how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.

I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.

Code:
SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter
FROM qryReportSelector2_Authority
WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));

View 8 Replies View Related

Queries :: Pivot Charts - Displaying Values As Percentage

Apr 8, 2014

I work at a program where we have individuals try out for a field (coming from various backgrounds). The program has been in place since 2012, and I wanted to start looking at the data for analysis and comparing trends, etc; I imported our master file (excel) into Access. It's in one big table, column headings include roster number, name, gender, type, FY (fiscal year), and finalcode (Select, Non-Select - but put it in as 1 or 2 in the field)).

I created a query, and was able to get data I was looking for.I then set it to Pivot Chart (bar graph).

Bottom (Believe its X) Axis: I have Type, Year.
Right Side of Chart (TypeCodeNumber)
Top of Chart: Count of CodeNumber

It gives me the totals (whether select or non-select) just fine, but I have been racking my brains, watched tons of YouTube videos and read a lot on how to make it give me a percent. I found a way to go Percent on the left side of the bar chart, but the Values go up to say 3300%. I know the problem is with the Count up top.

View 7 Replies View Related

Modules & VBA :: Invalid Use Of Null With No (obvious) Null Values

Jul 5, 2013

It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:

Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset

[Code] ....

View 1 Replies View Related

Counting Dates

Jul 20, 2005

Okay, this is gonna be weird :o What I'm looking to do is count the dates a preticular training task was accomplished. However, I do not want the dates that are expired to be counted. I will be running this off of a quiry that shows the "due date" of each task. I really have no idea where to start :confused: . Many thanks, Tim

View 2 Replies View Related

Counting Dates

Jan 5, 2006

Here is my table:
tbl_tc
Name
Date
Tech_Name
Issue
Resolved
Resolved_Date
In
Out

I need a query that will do the following:
Total Number of:
Tickets Open (IE, Date field is populated, but Resolved_Date is not)
Tickets Closed (both Date fields are populated)
Total In
Total Out

Total By Tech_Name:
Same as above but I need it seperated by the techs name.

Problem is that I only need it to pull up the Current Months tickets, I will pull additional months later on.

I am not really sure how to even start this... HELP PLEASE!

View 3 Replies View Related

Counting Dates

Mar 6, 2007

I have a report that lists a medical record number and under that number is a list of dates that the patient was seen. I need to know how to count the number of times the patient was seen. Example:

Medical Record #
12345678

Dates Seen
1/1/07
1/2/07
1/3/07

Total Days Seen: 3

So: I need to know, on my report, how to count the total number of days seen. Thanks for any help.

P.S. I'm learning so much from this forum, thanks!

View 8 Replies View Related

Counting Dates

Mar 23, 2007

My report has certain patient's listed. Under each patient there are different dates that the nurse visited these patients. I need to count how many days the nurse visited ALL the patients. the problem being that she will see many patients on the same day. As an example:

Patient Smith
1/1/07
1/5/07
1/9/07

Patient Jones
1/1/07
1/5/07
1/20/07
1/31/07

I need to know the number of distinct "days" during the month the nurse was visiting patients. The example above should show that the nurse had visited patients 5 days out of the month.

View 4 Replies View Related

Syntax Error - Counting A Not Null Field

Jun 29, 2012

The below formula is counting the records that have null fields in the InspDate. What is wrong in the syntax?

=[YrInpDueG0]-Sum(IIf([FSL]=0,IIf(Not IsNull([Insp_Date]),1,0)))

View 2 Replies View Related

Counting Years Between Dates

Jan 25, 2006

Friends,

I have a form with four fields, date1, date2, date3 and date 4.
These have all a mm/dd/yyyy format.
I need to add an unbound control which will count in months and years the difference between the dates.

Ex.
date1: 01/01/1970
date2: 05/05/1980

date3: 06/06/1990
date4: 06/30/2001

Fields date3 and date4 may be blank, therefore the code should consider only counting the first two.

Thanks for any help.

View 14 Replies View Related

Counting Working Days Between Dates

Feb 1, 2006

I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?

Thank you for your help.

View 1 Replies View Related

Reports :: Counting Repeated Dates As 1

Jul 14, 2014

I have an activity report which has multiple entries for the same date by club members. I have managed to build the database:

2 Tables
2 Queries
2 Forms
1 Report

Which list attendance's and activities by Member and Date and Prints each one starting on a fresh page..What I wish to do is count each Club Members total days attendance. I have tried using the Totals button but I don't know enough about expressions to get it to count multiple dates as 1.

Everywhere I have looked can tell me how to count between dates, around dates, workdays in a year etc. etc. etc. but none give me multiple dates the same counted as 1.

View 14 Replies View Related

Queries :: Counting Days Between 2 Dates - Just Workdays

Oct 11, 2013

I have 2 dates that I need to count between. Easy enough just use the datediff right? Nope cause it won't count just the 5 workdays. I researched and found that the "w" in the function doesn't work the way I need it to. I found lots of code to make a module that will do it for me and they all include having a holiday table. Right now I don't need a holiday table I just want the simple dates in between.

View 2 Replies View Related

Queries :: Counting Multiple Dates In A Single Query Field

Feb 8, 2015

I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.

View 1 Replies View Related

*?? Counting Values In The DB *??

Feb 2, 2006

Hi I have a table that looks like this:

Col1 Col2 Col3 Col4 Col5 Col6
1 A B C D E
2 A B C D F
3 A B C G H
4 A B J K L
5 A D M N P


Does anyone know how I can run an SQL query to count how many letters there are For example : 5 A's 4 B's 3 D's Etc

Cheers,
bikeboardsurf

View 2 Replies View Related

Field Criteria: Is Null; There Are Null Values In That Field; No Records Are Returned

Nov 16, 2007

I think the title pretty much sums it up....

I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.

I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.

The query is refusing to return any results...

Any ideas?

View 10 Replies View Related

Counting Values In A Field

Feb 28, 2008

Hey, I'm new to microsoft access, and I could do with a little help please :) . On a database I have created, I have a table with the field "Results" in it. This field has been set up in the Lookup properties to be a choice from "Win" "Loss" or "Draw". My question is, what is the expression I would use to count the total number of records in my form with "Win" selected?

I tried to set up the DCount expression, but this gave me some odd results.

Currently my database is set up like this,

TBL_Match_Results:
Fields:

GameNumber (Autonumber + Primary key)
Date
Result (With the choices above)


Then I made a continuous form from this table, and on the Form Footer I had a text box with the value,

=DCount([GameNumber],"TBL_Match_Results",[Result]="Win")

I had hoped this would simply give me the total number of records with a win in them. However, insted the value changes depending on which record I have selected on the form. It is "10" if the selected record is a win, or "0" if it is not. I'm not sure if the problem is the poorly written expression (I'm not 100% sure how to work them), or if I'm even using the right expression (I was only told to use DCount). If anyone could shed some light on this, I would greatly appreciate it!

EDIT: I've just realised where the 10 is coming from, it's the number of records in my table, when i added a new one it changed to 11.

View 2 Replies View Related

Counting Values In A Field

Feb 28, 2008

Hey, I'm new to microsoft access, and I could do with a little help please . On a database I have created, I have a table with the field "Results" in it. This field has been set up in the Lookup properties to be a choice from "Win" "Loss" or "Draw". My question is, what is the expression I would use to count the total number of records in my form with "Win" selected?

I tried to set up the DCount expression, but this gave me some odd results.

Currently my database is set up like this,

TBL_Match_Results:
Fields:GameNumber (Autonumber + Primary key)DateResult (With the choices above)
Then I made a continuous form from this table, and on the Form Footer I had a text box with the value,

=DCount([GameNumber],"TBL_Match_Results",[Result]="Win")

I had hoped this would simply give me the total number of records with a win in them. However, insted the value changes depending on which record I have selected on the form. It is "10" if the selected record is a win, or "0" if it is not. I'm not sure if the problem is the poorly written expression (I'm not 100% sure how to work them), or if I'm even using the right expression (I was only told to use DCount). If anyone could shed some light on this, I would greatly appreciate it!

EDIT: I've just realised where the 10 is coming from, it's the number of records in my table, when i added a new one it changed to 11.

View 1 Replies View Related

UPDATEing Null Dates

Aug 29, 2005

I've got a few questions on storing dates in an MDB using SQL UPDATE...

(1) I would prefer to have empty date fields really be empty (rather than some unusal date like January 1, 0001 or whatever).

If a textbox.text equals "", what would I set the corresponding field in the database to? Some sort of Null value..?

I tried setting it equal to a DBNull value, but it didn't like that. It said, "Parameter?_10 has no default value."


(2) If a user types garbage into a text box that is supposed to hold a date, CDate(thatTextBox.text) complains loudly.

Is there a cleaner way to test the contents of the text box for valid date than to allow CDate() to attempt the conversion and then clean up the mess afterwards? Maybe something like a IsValidDate("10/11/2001") that would return a true or false.


Here is some code to put this question into context. The Null part does not work.

Dim Null as System.DBNull
[...]
' Prepare Paramater [HireDate]
pm = New OleDb.OleDbParameter
pm.OleDbType = OleDb.OleDbType.DBDate
pm.Value = iif (isdate(cdate(txtHireDate.Text),cdate(txtHireDate. Text), Null)))
dbCmd.Parameters.Add(pm)

[...]

update personnel set [Lastname]=?, [Firstname]=?, [HireDate]=? where [ssno]=?

View 1 Replies View Related

Modules & VBA :: SQL Null Dates

Apr 30, 2015

I'd like to put a WHERE clause where I confont the date in an unbound textbox with the date in the table. In vba I control the null case of the unbound textbox, but I'm not sure how to control the null value in the sql code.here is the code "strDataRiferiment" that I later insert in a concatenated SQL code. The rest of the code works, while this line doesn't. The problem are the null date records present in the table COMMESSE.What I'd like is a WHERE clause that:

- returns records with all null date values and
- returns records with not null values where these dates are < date chosen in the unbound textbox

Code:

Select Case Nz(Me.InizioDataRif.Value, "Null") 'the unbound textbox
Case "Null"
strDataRiferimento = "AND (([if is not null] COMMESSE.DataChiusura =< #" & Format(Me.FineDataRif, "mm/dd/yyyy") & "#) OR (COMMESSE.DataChiusura IS NULL)) "
Case 'etc
'etc..
end select

I tried for example:

Code:
"AND ( IFF ((COMMESSE.DataChiusura,0) IS NOT NULL,
(COMMESSE.DataChiusura,0) =< #" & Format(Me.FineDataRif, "mm/dd/yyyy") & "#) , (COMMESSE.DataChiusura) IS NULL) )) "

View 12 Replies View Related

Counting Values Into 1 Hour Bins

Jul 16, 2005

MY QUESTION:

I’m using Access 2002.

I’m trying to create a query that will COUNT the number of values within 1 hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that I need to have every hour bin represented whether there is any value in it or not. I believe that I need to create a separate table of the bins that I want (include bin start-value & stop-values) and then bring this table into my query and join it to the original table.

I was able to construct the make table query below to COUNT the values in hour bins for which there were records, but I also need rows for all of the zero values;

SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS [TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins]
FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter
GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI Detections.Date]), [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time])
HAVING ((([All NWHI Detections].Species)="tiger"))
ORDER BY [All NWHI Detections].Transmitter;


Q1? Creating the hour bins table

This table will be very large because it will need to have 4 years worth of hour bins for each transmitter (N=15) and location (N=12). This results in 6,307,200 hour bins! How can I write a query to create this table, or is there a better way of doing this?

Q2? Bringing the hour bins table into the main query.

How do I include the hour bins table in the main query to get my final result.


Any help would be greatly appreciated! Thank you.

View 1 Replies View Related

COUNTING Yes Values In Checkbox In A Query

Nov 26, 2007

Hi All, I have read a few posts on here but can't quite get a solution to my particular issue.

I have two tables in a query:

tbl_suppliers
tbl_supplier_perf

tbl_suppliers is right joined with tbl_supplier_perf by

[Location Name]----->[Supplier]

No as part of tbl_supplier_perf there is a YES/NO checkbox, where it can be ticked if there is an issue with a supplier delivery. This field is called [Issue?].

I want to report all suppliers (not just those with records in tbl_supplier_perf) with a count of the amount of records created in tbl_supplier_perf with a tick in [Issue?]. So if no records in tbl_supplier_perf have [Issue?] ticked it will just report 0.

Basically the query needs to report all suppliers with a count of how many records have been ticked "YES". It is a check box so I believe they are recorded as 0 and -1.

I believe I need to use Dcount but I do not know how to get that in to my existing query!

View 9 Replies View Related

Reports :: Counting And Displaying All Different Values

Dec 9, 2014

As part of the report I'd like to have a table that would list all different values in column in column A and the number of times they appear in the table in column B.I have a bit of a hard time how to populate the table the easiest way.I have all the values in a separate source table, so first column is easier. but do i have to write the dcount statement for each row for second column?Could I display results of query as subquery in report.If you have a bunch of textboxes with increasing names like NameBox1... for for/next loops in reports... is there an easy way to assign those names?

View 9 Replies View Related

Counting Number Of Values For A Particular Field

Nov 5, 2012

I need to count the number of entries for a particular item located in a table. To keep it simple, we have a database used to process orders for Turkeys, this database has a field for each order called "Long legged Weight". In order to sort the turkeys in the shed, we need to know how many of each weight there are.

Order Number / 10-12 / 11-13 / 12-14 / 13-15 / (etc)
......1 ................1
......2 ..........................1
......3 .....................................1
......4 ..........................1

Total ..................1 ......2 .........1

View 14 Replies View Related

Queries :: Counting Text Values Based On Their Status

Jan 25, 2015

I have a question regarding counting of text values base on their status and using that result to a calculation.

Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.

What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.

The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.

I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.

View 2 Replies View Related

Reports :: Several Fields In Report - Counting Record Values

May 10, 2014

Suppose we have a report that outlines several fields, one field shows the City. In the report, this week, we see 10 records "New York", 8 records "Houston", 7 records "London", 3 records "Paris" and so on.

Next week's report outlines different cities and different number of records. I need to have in the report footer a "recapitulation" , a field that would say :

New York 10
Houston 8
London 7
Paris 3

Total 28

Next week cities and number of records might not be the same, we may have

Tokyo 12
Singapore 14
New York 6
London 7

Can i make my report in that way that it will count the values without using VBA ? I tried the count values option but it counts the overall report, does not take into consideration the different values.

View 2 Replies View Related







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