Dcount And Date Field Problem

Jan 3, 2006

I'm having abit of a problem with the DCount function where date fields are involved.

Basically, I have a table - TblMain.
Within that table there is a field -DateRegist. The data type of this field is set to Date / Time - Format short date.

Elsewhere I have set up a form with a text box. The idea being this text box will count the number of 'DateRegist' fields where a date has been entered between two set dates. (There will be four text boxes in total, one for each quarter of the year)

When I use Date() etc in the control their are no problems. However, when I try and use an actual date it all goes pear. I figure it has something to do with it being a date field bit not sure exactly what.

This is what I have so far (don't laugh, I know its miles off the mark but I am trying)

=DCount("[DateRegist]","[TblMain]","[DateRegist]=Between #" & 1/4/2006 & "# AND #'" & 30/6/2006 & "#")

I know I can do four separate queries, each pulling off a quarters data and then draw off the Dcount from each of them but after two days of trying to figure it out I would like to know where I'm currently going wrong - (If you consider doing to four queries will be quicker for the abstraction of the data then let me know but I would still like to know what I'm doing wrong for future reference)

Thanks in anticipation

View Replies


ADVERTISEMENT

DCount And Getting Num Occurances Over A Date Range

Jan 21, 2006

Hi;

Summary: Using DCount in MS Access 97 to output the name of an event
and the total number of occurances per a user input date range.

e.g. the table is called EventData and it is a table of the types of car repair performed, and the fields are:

EventType Date Desciption other Time
======== ==== ============ ====
fix flat 17/01/06 comment.... 101pm
brakes 15/01/06 comment... 504pm
tuneup 02/01/06 comment... 725am
paint fender 03/01/06 comment.. 852am

My issue is to use a subquery for intermediate results and then another query.

I would like the user to enter the start and end dates, and then for output to be:

Output:


EventType Num Occurances
======== ============
fix flat 2
brakes 5


approach 1, use subquery called SummaryQuery:

SELECT EventData.EventType, EventData.Date
FROM EventData
WHERE (((EventData.Date) Between [Type the Start date:] And [Type the End Date:]));

This gives me output of all the events types in the date range and the time.

Then if I query this via:
SELECT SummaryQuery.EventType, DCount("*","SummaryQuery") AS TotalCount
FROM SummaryQuery
GROUP BY SummaryQuery.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));


Here I am using DCount to get the num of ocurrances.. but it is not working.
I get the error that "Access cannot find the name "type the start date" "

approach 2, Use only one query:


SELECT EventData.EventType, DCount("*","EventData") AS TotalCount
FROM EventData
GROUP BY EventData.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));

for this, I get no output, but the query runs.

Any comments or help with this is appreciated!!!!!!!!!!!!!!!

View 2 Replies View Related

DCount With A Date Range Greater Than X

Mar 3, 2008

Hi,
The query statement below is meant to extract the number of References with a type code of 'M' on the table tblHDRef, then present it under a column named 'Modified':

Modified: DCount("[tblHDRef].[HD Ref]","[tblHDRef]","[tblHDRef].[HD CodeType]='M'")

This works fine - but how can I add further criteria to this query statement to only count references with a HD Date greater than 03/02/2008?

I tried:
Modified: DCount("[tblHDRef].[HD Ref]","[tblHDRef]","[tblHDRef].[HD CodeType]='M' and [tblHDRef].[HD Date]>#03/02/2008#")

but this didn't seem to work at all. Am I missing something?

Cheers in advance.

View 4 Replies View Related

Modules & VBA :: Dcount With Date Variables

Jul 11, 2015

Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:

Dim WorkoutDate as Date
Dim datecount as integer

datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)

Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.

View 5 Replies View Related

Modules & VBA :: DCount Using Date Comparison

Mar 6, 2015

I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.

I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?

Code:
MsgBox DCount("*", "[healthcheck]", "[check_date]=#" & Format([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")

I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.

View 6 Replies View Related

Modules & VBA :: DCOUNT With Date Criteria

Feb 3, 2014

I can't get the following DCOUNT working for the life of me: DCount("EmployeeName", "tblAttendance", "EmployeeName = '" & cboEmployee Name. Value & "'" And "AttendanceDate = #" & txtAttendanceDate.Value & "#")I have checked this forum along with Google and there seems to be no easy way to do this? I have tried editing the syntax over and over again. I have been able to run a query and get the desired result in that query; however I am unable to call that result in VBA.

View 7 Replies View Related

Dcount For 2 Criteria Date Fields?

Apr 19, 2014

With a form with two fields one is a date field formatted as short date and the other is a lookup combo box that I use to lookup predefined times and is formatted as short time. The date and time are stored in the same table as date/time in separate fields. I attempted to use Dcount, and get syntax error missing operator. Below is one of a hundred that i have tried after scouring this forum and the net. I ended up converting the Appt_Time field to a text field in the table to eliminate one date field but still got no where.

Code:
Private Sub Appt_Time_BeforeUpdate(Cancel As Integer)
'Check table for duplicate
If DCount("[Appt_Time]", "tblexams", "Appt_Time= & Appt_Time & And [Sch_Date] = #" & Sch_Date & "#") > 0 Then
'Message box warning of duplication

[Code] ....

View 4 Replies View Related

Forms :: Using Dcount With Combo Box And Date Range?

Jun 13, 2014

I would like to have a text box display the number of records for a selected title that fall within a selected date range. Been looking around for a while and have this so far;

=DCount("Discussion_Title","Discussions","[Discussion_Title]='" & [cboType] & "' And [Discussion_Date] = Between ([txtStartDate] And [txtEndDate])'")

I get an #Error message. I just threw that together because it describes what I want, but I know there are syntax problems.

View 4 Replies View Related

Modules & VBA :: Date Expression For DCount And Where Open Arguments

Oct 9, 2014

I am getting something wrong but I don't know what - date related.

Take this code for example:

Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then
MsgBox "Go Away", vbOKOnly
Else
Go on and do the real work
End If
End Sub

The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:

Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then
MsgBox "Go Away", vbOKOnly

But that throws up a Type Mismatch (which is what I would expect). Then I tried

Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then
MsgBox "Go Away", vbOKOnly

And it gets ignored again.

I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.

View 7 Replies View Related

Queries :: Expression DCOUNT With Last Month Date Criteria

Feb 8, 2015

I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.

View 11 Replies View Related

General :: Unable To Use DCOUNT To Check If A Date Exists And Then Cancel The Input

Aug 22, 2014

I'm trying to use DCOUNT to check to see if a date exists and if it does then i can cancel the input. The code im using is as follows however its returning datatype mismatch.

Code:
If DCount("[pick_date]", "tblPicker_Stats", "[pick_date] = '" & txtPickDate & "'") > 0 Then
MsgBox "Statistics for this date already exist", vbOKOnly, "Error"
End
End If

View 7 Replies View Related

Modules & VBA :: 3 Field Duplicate Dcount

Jun 18, 2015

I have a tblManualRewardIncident in there I have 3 fields I want to have my staff in put these rewards via a frmRewardIncidentEntry However im concerned over another staff member in putting the same data so I want the form to highlight on update that a duplicate is there already so cancel the data entry.

The 3 fields that need to check and match are StrataID , DateOfIncident , EndDateOfIncident these are all in the tblManualRewardIncident and appear as buttons in the form frmRewardIncidentEntry the buttons are as follows
cboStrataID , txtDateOfIncident , txtEndDateOfIncident

I want the VBA to run when I CLICK another button on same form cboSQCDP..All 3 fields must match exactly to flag a issue.

StrataID , DateOfIncident , EndDateOfIncident from tblManualRewardIncident

If it doesn't find a match continue and add the inputs in these boxes to the table if it finds a match flag a message and delete the data from the form.

View 2 Replies View Related

Queries :: DCount With Calculated Field

Dec 8, 2013

I am working with DCount in a query based on a calculated value [MyCalValue] that is a status.

The status can be: Complete, On Going, Past Due, Additional Information, No Date.

In the DCount, I want to return 0 if null.

Sample that works fine

Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE")

But I want combine values in an OR statement but can't get it to work.

Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE OR On Going")

Also not working is when I am trying to get a count based on the month like this:

Code:
LastMonth: DCount(Month("[RequestDate]","Open Issues","[RequestDate] = fLastMonth(Now())",0)

View 7 Replies View Related

Forms :: Auto Populate Date Field Based On Another Date Field

May 13, 2014

I am building an Access database for a client. It is an employee staffing database. With that being said they would like the ability to automatically populate the "T2PPCD" date field based on what is entered into the "Report Date" field. (Same table)

The date is the Monday after 180 days from the report date.

I already know how to get it to auto fill 180 days from the "Report Date" but I'm not sure how to tell it to give me the Monday after 180 days.

View 9 Replies View Related

Modules & VBA :: Date Field To AutoPopulate Other Date Fields To Future Date

Oct 24, 2013

I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"

I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =

Code : DateAdd(d,2,[IncidentDate])

so I removed the parenthesis and nothing happened

Code : DateAdd d,2,[IncidentDate]

I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either

Code : DateAdd d,2,[ContainDueDate]

I'm not sure if I'm even using the right function to get what I want.

View 4 Replies View Related

Deriving / Reformatting Date Field From Another Date Field In Same Table

Jun 19, 2014

I've inherited a database into which the user (not me) is entering essentially the same Date data twice: field 1 contains 6/19/2014 and for field 2 they enter June-14 (the month and year from field 1).

I suspect there should be a way to derive and then reformat the display of field 2 based on the contents of field 1, such that the user only has to enter the first. Is this true? If so, how?

(I know there really is no need for the 2nd field, but existing reports and queries are built around it -- so for right now, I'm just trying to eliminate the duplicate data entry)...

View 7 Replies View Related

Reports :: Showing Date Field Differently Depending On Current Date

Nov 23, 2013

I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.

So:

- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY.
- If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY
- If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY

I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY

View 9 Replies View Related

Display Fields As Column Showing Date One Month After The Date In Field Above

Jun 2, 2015

I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.

The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.

I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!

Now I could do that in Excel, but I don't know how to get Access to do it.

View 4 Replies View Related

Auto Fill Field In A Table By Comparing A Given Date Against Current Date?

Aug 18, 2015

I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.

My table has the following fields:

ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)
Challenge

I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.

View 3 Replies View Related

Forms :: Form Auto-populates Date Field - Want To Add Check Box To Enter Alternate DATE

Nov 1, 2013

We use access to enter our service tickets in at work.What we have are three date fields.

Call Date
Start Date
End Date

We are 24/7 operation.Currently all 3 just autopopulate with the current date.What i would like to do is ADD a CHECKBOX next to each Date Field.And make it work like this.

1. let them autopopulate as they are currently
2. if you end the call AFTER MIDNIGHT (the next day). CHECKING the box would automatically populate yesterdays date in each of the fields that has the check box CHECKED

View 3 Replies View Related

Print Date Range On A Report Based On A Non-date Field

Aug 7, 2005

Is there a way to show the earliest and latest dates of a report generated by a non-date field?

E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)

View 2 Replies View Related

Query Date From DateTime - General Date Format Field.

Mar 10, 2006

I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).

I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.

Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.

How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?

View 7 Replies View Related

Queries :: Date Field With Time - Query For Date Only And Get All Records

Apr 26, 2013

I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.

View 11 Replies View Related

General :: COMPARE DATE Variable To DATE / TIME FIELD

Feb 18, 2014

In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.

View 13 Replies View Related

Forms :: Show Date Picker On Selecting Date Field?

Apr 26, 2014

I am using Access 2007 and I have had a curious issue arise.

I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)

When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)

The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.

View 2 Replies View Related

General :: Date Field - Highlight Future Date In Different Color

Mar 25, 2014

In a date field is it possible for a date that is in the future to be hi-lighted in a different colour.

E.g. todays date is 25/03/14

So anything with todays date or previous would stay in black

24/03/14
25/03/14

but anything from 26/03/14 forward would be in red

26/03/14

Then will change to black when the 26th comes.

View 3 Replies View Related







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