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.
View RepliesI ahve declared custCount as an integer and id as a string that comes from the user form (id = me.CustID) and trying to count the number of matching records in the recordset (rst) using the following:
custCount = DCount("[customerId]", rst, "[customerId] like '" & id & "'")
I'm getting a type mismatch error on the DCount statement?
Using MS Access 2000
I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.
I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....
=DCount("MemberID","tblHHMembers","CustID = [CustID]")+1
Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?
I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!
Hello,
I am using a Dcount function in an unbound textbox to count the number of records in a query.
I have noticed that it takes some time to process the calculation. Often I need to click in the text box to see the result.
Is there any other way I can do this, perhaps via code? This is what I am writing:
=DCount("[BLN]","SAAdata")
BLN is the field
SAADATA is the query name
Thanks.
HELP PLEASE!!! There are two fields in my form that need the following relationship:
1. When I enter 1000 in my "spec no" field in my form, I need my "Number Field" to enter a "1"
2. When I enter 1000 in my "spec no" field in the next record, I need the "Number Field" for this record to enter "2" but "1" to remain on the first record.
3. If I enter 1001 in my "spec no" field in the next record, I need the "Number Field" to enter "1"
4. Continue this logic for all records without modifying the previous "Number Field" record.
I was told there was a way to accomplish this using DCount or Count function. Maybe there's an easier way??? Any help is greatly appreciated!! TX!
Hello,
I would like to insert a DCount function in an unbound textbox in a form to count all records in a query. My current code in the textbox is: '=DCount("*","Query1")'. The Query 'Query1' depends on two parameters, which has to be entered before opening the query. The result is '#Error', if 'Query1' is open or not.
Thanks for your help.
I have a report where I use numerous DCOUNT functions, and it works fine. But now I need another report which has multiple conditions, and I can't seem to find examples anywhere to get my syntax right. For this example, the field 1 ENJOY in table CONGSITEALL needs to pull out the number of entries for the answer Yes, Definitely, and Most of the Time. I have tried putting the quotes, etc into diffeent places but I just can't seem to find the right combination.
=DCount("[1 ENJOY]","CONGSITEALL","[1 ENJOY] IN ('''Yes, Definitely','Most Of The Time'")
My mainform is "CourseDays" and subform is "CoursesSubForm". The "CoursesSubForm" data source is a table "Courses" which contains a field "Subject".
I add a calculated field on the mainform to count the number of records in the subform. This is my code:
=DCount(”[Subject]”,”[CoursesSubForm]”)
An error message results.
DCount function.
Code:
Me.ImprovementNotice5DayCount = DCount("[txtReferralReason]", "qryRTOFileReferralPopupCount", "[ComplianceTargetDate]-[DateNow]<=5")
I am not sure where I have gone wrong.
What I would like Dcount to count are those dates in the ComplianceTargetDate form control that are <=5 to the DateNow form control.
I get a count of 3 when there is only one. I may have the syntax of the Dcount wrong.
I am working with MS Access 2003. I have a form (frmCalendar) with a textbox (tbDay) and a combobox (cmbLineName)
I want to count the number of records based on the criteria from cmbLineName, and show the result in tbDate. Inside the Control Source of tbDate I put in the following:
=DCount("[EncounterID]","tblEncounters","[LineName] = ' " & "Forms![frmCalendar]![cmbLineName] = " ' ")
The problem is that the result in tbDate is always 0; regardless of what is chosen in cmbLineName. I know that the record count should not be 0 for all criteria.
I am trying to count the number of records in a subform where the text box (txtFinal) = RPR-RPR & RTN. The subform is called PartNumbers. The table name the subform is bound to is named Completed.
I tried using =DCount("[Part Number]","Completed","[Final] = 'RPR-RPR & RTN'"). That is counting the total number of records in the table, not the total number of records in the subform. How do I get it to count the total number of records in the subform?
I am using the dcount function as the example I display below. The problem is that it returns the correct result (i.e. 59) but the data type is text (59 is on the left side) . I need this to be number.
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
I have a query where one field is a calculated field dividing one field summed by another field summed. But when looking at the data in excel, I figured out that one of the fields was adding a fraction onto the number when I summed up the whole column including the field name leading me to believe that Access was doing this as well.
View 14 Replies View RelatedI have a query with about 7500 records that are grouped as follows and sorted by Mtr_Reading
ID Name Event_Start_Time _Hour, Mtr_Reading
2210 XYZ 7/15/2013 13:00 17 150
2210 XYZ 7/15/2013 13:00 14 143
2210 XYZ 7/15/2013 13:00 16 115
2210 XYZ 7/15/2013 13:00 15 110
2210 XYZ 7/15/2013 13:00 13 100
2210 XYZ 7/16/2013 12:00 12 100
2210 XYZ 7/16/2013 12:00 17 150
2210 XYZ 7/16/2013 12:00 14 147
2210 XYZ 7/16/2013 12:00 13 113
2210 XYZ 7/16/2013 12:00 18 110
2210 XYZ 7/16/2013 12:00 15 100
There are about 75 distinct sets of ID's and Names not shown here
The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.
I did try nested select statements and kept getting "invalid argument to function" exceptions.
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"
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedIm 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.
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.
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.
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
It never beeps!
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 RelatedMycode works great when I use the DCount function, but fails when I change it to DSum which errors with: Error 94 - invalid use of null.
dblCntr = DSum("[intEventCount]", "tblResourceEvents", "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = " & Me.cbo1)
The entire table has valid data - no nulls. myKey and Me.cbo1 both have correct values.
I have having an issue with determining how to use a customized fiscal year with a DCount function.
My Code:
ThisYear: DCount("*","Open Issues","year([RequestDate]) = " & Year(DateAdd("y",-1,Date())))
The code only does from Jan 1, YYYY TO Dec 31, YYYY
I'd like the code to read from Oct 1, YYYY TO Sept 30, YYYY
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
[code]....
I am trying to check if a record exists, but I keep getting a error!!!
Private Sub Command10_Click()
If DCount("*", "tbl", "[ID] = "") <> 0 Then
MsgBox "This record already exists.
Else
stDocName1 = "McrAddNewRecord"
DoCmd.RunMacro stDocName1
End If
End Sub