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?
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.
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:
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.
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.
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.
Hi all- I'm creating an update query.I have a field [dateLastEdit] that is stored as text, I have to compare it to a date entered on a form by user [date_from] (I need to select all dates greater than this) but I'm running into problems:when I use date_from in text or date format and compare it to dateLastEdit in text format the comparison is made in numeric order(so 12/02/2006 is selected as well as 12/02/2005 when date_from is 12/01/2006)SELECT PTSData.LastEditDateFROM PTSDataWHERE (((PTSData.LastEditDate)>=[Forms]![chMgmt_frm]![date_from]));when I use date_from in date format and compare it to dateLastEdit converted to date format I get "expression typed incorrectly or it is too complex to be evaluated" msg.SELECT DateValue([LastEditDate]) AS Expr1FROM PTSDataWHERE (((DateValue([LastEditDate]))>=[Forms]![chMgmt_frm]![date_from]));[LastEditDate]'s format is always mm/dd/yyyy hh:mm:ss, but as text, not date data type.I've tried CDate with same results, isolating it in its own query to test it, using hardwired dates as criteria, but no joy, am I missing something obvious?forgot to add- I've used all the above with left also to select only date portion with same results
I have a date field (DueDate) that I am comparing against the current date (Date()). When the current date is greater than the DueDate I filter just those dates. I have done that without any problems. Now I am trying to figure out how to compare that filtered information to show that the DueDate is either over 30 days, over 60 days, or over 90 days overdo. If it shows up on either one of those lists I do NOT want it to show up on the other lists. I also want to have a counter by states. For example:
Current Date DueDate State a. 9/22/14 2/1/14 GA *(90) b. 9/22/14 8/20/14 WV *(30) c. 9/22/14 7/1/14 WV *(60) d. 9/22/14 8/19/14 GA *(30) e. 9/22/14 7/31/14 GA *(30) f. 9/22/14 3/1/14 WV *(90) g. 9/22/14 7/20/14 SC *(60)
Okay, me again, on my DAP (Data Access Page) I have a piece of JavaScript that tells my page onload compare 2 dates. It's straight forward and all and my code works. That's actually where the problem is... When access loads my DAP up it builds the page, executes the javascript and THEN populates my fields with all the records. So my comparison javascript will always execute BEFORE the data loads into my DAP...
<body language=”javascript” onload=”colorBox();”>
<SCRIPT LANGUAGE=”javascript”> Function colorBox(){ If (Quarter1DueDateP1 >= Quarter1CompleteddateP1){ Box3.{background-color:Green;} } Elseif (Quarter1CompleteddateP1>=Quarter1DueDateP1){ Box3.{background-color:Red;} } Else { Box3.{background-color:Yellow;} } }
I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.
The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.
It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.
"Date Overdue" is set to this value:
Code:
DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8 "Date Overdue" has the criteria "<DateValue(CDate(Now()))"
I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..
The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.
I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "
NOTE: I'd like to add that this is just a regular Select query.
Code: SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2)) AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8 AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],
:eek: I have tried almost everything and I still can get this to work. I thought I had it with this attempt until I inputted October data and found that Access was sorting it by Number vs. Date.
I am simply trying to compare the number of tickets opened each month to the number of tickets closed each month. I also need to address how a report will appear if no tickets were opened or if no tickets was not closed in a month.
Incase the SQL in screencap is too difficult to read:
Query: qryTotals_Assigned_And_Completed - This calls the following two queries. ############################################# SELECT qry_AssignedByMonth.MyAssigned, qry_AssignedByMonth.Count, qry_ClosedByMonth.MyClosed, qry_ClosedByMonth.Count, qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year FROM qry_AssignedByMonth INNER JOIN qry_ClosedByMonth ON qry_AssignedByMonth.Month = qry_ClosedByMonth.Month ORDER BY qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year; ############################################# qry_AssignedByMonth ############################################# SELECT (Format([DateAssigned],"mmm"" '""yy")) AS MyAssigned, Str(Month([DateAssigned])) AS [Month], Str(Year([DateAssigned])) AS [Year], Count(*) AS [Count] FROM tblRequests GROUP BY (Format([DateAssigned],"mmm"" '""yy")), Str(Month([DateAssigned])), Str(Year([DateAssigned])) ORDER BY Str(Month([DateAssigned])), Str(Year([DateAssigned])); ############################################# qry_ClosedByMonth ############################################# SELECT (Format([DateClosed],"mmm"" '""yy")) AS MyClosed, Str(Month([DateClosed])) AS [Month], Str(Year([DateClosed])) AS [Year], Count(*) AS [Count] FROM tblRequests GROUP BY (Format([DateClosed],"mmm"" '""yy")), Str(Month([DateClosed])), Str(Year([DateClosed])) ORDER BY Str(Month([DateClosed])); #############################################
I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically
Code:
If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then targetTable.Field3.Value = sourceTable.Field3.Value
The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)
Code: For i = 2 To 5754 For j = 2 To 3500 If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value
The above code works but it is really slow (takes about 12 mins on a high-end CPU).
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!!!!!!!!!!!!!!!
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':
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)
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)
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
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.
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.
I need to check the combination of a text box and a combo box that are bound to the same table for duplicates and then give a message box telling of the existing records. The 2 fields are set up as a compound index so access displays a generic message but I am trying to customize the message without doing it through error trapping (because I want it earlier in the processes on the form).
I am trying to us Dcount as follows but this is not working. It seems to be checking for either rather than a combination of the 2 fields because I get my message if either the Description OR the Group is duplicated (both of the text box and the combo box can have duplicates and it is only the combination that is singular) .
If DCount("[Description]", "[ItemTBL]", "[Description] = '" & Description & "'") And DCount("[Group]", "[ItemTBL]", "[Group] = '" & Group & "'") > 0 Then MsgBox "This Item is already in the database.", vbExclamation, "Already in Database"
Upgraded from Access 2003 to Access 2010. My DCount query was working fine in Access 2003 for many years but now I am having issues. Everything is written in VBA.
Routine: 1.) Insert records into table tblEmployee 2.) Query table tblEmployee to count how many employees are in each department using DCount.
Issue: DCount returns 0 for all Departments.
Findings: If I manually open table tblEmployee and edit an employees department (I actually named it the same department), DCount will then find 1 person in that department (the record I manually edited). It seems a manually edited record will be counted but anything written by the insert query is ignored.
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.
In my database when an item is currently being loaned the item's loaned field will have a 1 to indicate this. When the item is returned the value is 0. Only so many items are available and when I use the DCount function it is counting the 0s as 1s.
My DCount statement is not finding a record that I know is there.
Code: Private Sub Transfer_WN_Books_Click() Dim ST As String ST = "H" If DCount("*", "Books", Author = " & Me!AuthorID & " And StatFlag = " & ST & ") > 0 Then DoCmd.Beep End If End Sub
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
If expression to determine if a duplicate records has been entered.
DateAte is a date MealType is a list box (text)
All I want to do is prevent two lunches, etc. from being entered on the same date. Code that results in the message box every time, regardless if a duplicate record or not.
Private Sub MealType_AfterUpdate() Dim strDA As String Dim DA As Date DA = Me.DateAte.Value strDA = "[DateAte]=" & "#" & DA & "#" If DCount("[MealType]", "tblmeals", strDA) > 1 Then
