I'm asking for some help on this simple question. I have a form with a button that runs a query based on user's selection. Sometimes the query returns no records but leaves the query window up. The user has to manually close the window. I want to add the feature that if the query returns no records, a msgbox tells them there are no records to display and the query window closes. Would DCOUNT work here?
Private Sub btnApplyFilter_Click()
On Error GoTo Err_btnApplyFilter_Click
ColToSearch = Me.CboCol.Value
Select Case ColToSearch
Case "ID"
DoCmd.OpenQuery "IDNumberQuery", acViewNormal, acReadOnly
' DoCmd.Close acForm, "IDNumberQuery"
Case "Issue"
DoCmd.OpenQuery "Words in Issue Query", acViewNormal, acReadOnly
' DoCmd.Close acForm, "Words in Issue Query"
Case "Discussion"
DoCmd.OpenQuery "Words in Discussion Query", acViewNormal, acReadOnly
' DoCmd.Close acForm, "Words in Discussion Query"
Case "Recommendation"
DoCmd.OpenQuery "Words in Recommendation Query", acViewNormal, acReadOnly
' DoCmd.Close acForm, "Words in Recommendation Query"
End Select
Was wondering if someone could possibly help me with a DCount problem i'm having.
I have a form with a subform, displaying bookings that customer has made. What i want to be able to do, is when a booking is created for a customer in this subform, after the time period chosen is selected, i want a DCount to run, go to a table of regular bookings, count up how many bookings in it have the date of booking, that same as the date just put into the subform, AND the time period of booking the same as just put into the subform. There can only be 1 result at max due to its setup, and from there it should be fine, but i cannot get it to work. The field names are as follow:
Subform: Date for Booking Time Period
tblRegularBookings Date For Time Period
If this doesn't make any sense i can try and explain better.
I have only been using access for about 3 weeks now, and its kicking my butt pretty hard.
Im making a query that does all kinds of math junk in it. I want to be able to find the number of occurrences of x in another column in the table.
For a better example, lets say I have a column named "SP" in the "compiled" table, the values of this column range from 1 to 5, in about 200 entries. So there is another column in the compiled table called "SPX", which has the same value range. So if I am looking at the one entry in the table, I want to take its value in the "SPX" column, and see how many times it shows up in the "SP" column.
I have been trying to get dcount to do this, but I cant seem to get it to work... Must have tried a dozen ways now.
My main table is called NEWcompiled, I have fields named "faction", "SPeffect", and "Launcher_ID". I am trying to use Dcount in a query to count up how many entries have a value in "faction" and "SPeffect" that are equal, and a value of "yes" in "Launcher_ID".
Currently my code looks like this: DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "Launcher_ID = yes")
This indeed counts how many entries have equal values for "faction" and "SPeffect", but then it seems to add that to sum of all the entries that have a yes for "Launcher_id".
i have the problem when using dcount in my query,anyone can help me?this is the situation.
Table 1:
Num
20080207 20080215 20080218
Table 2:
Begin End
20080206 20080208 20080200 200802116
i want to make the query,and i want to add field "sumactive" using the dcount function refer to Table 1,anyone can help me?i want to count how many record "num" in table 1,between field "begin" and field "end" in table 2
Im trying to count the number of records in a table that contain certain crieria, I think I should be using the DCount function and have looked for help on it, but I dont understand it. im unsure if I should be counting the records on the form or the table.
This is my Criteria, Table Name = Armour_Selection, Field name = ExerciseName, I want it to tell me how many records there are with ExerciseName = ?
I've looked at numerous threads on this site and still can't get a dcount to work.
I want the database to check if there is a valid reference number entered before opening a form.
There is a table called 'staff' with a 'payroll number' field in it. This table contains all staff.
I then want the user to enter a payroll number and retrieve the corresponding record. However, if there is no match then the user has entered the number incorrectly.
I've done:
int2=dcount("[payroll number]","staff",forms!control,payroll) and then an:
if int2=0 then msgbox end if exit sub
However, I either now get a message when the number is correct, or it's exitting the sub every time.
are there any restrictions for using Dcount? i used DCount once in a report, and it works fine. but in another report it returns an error.. another question.. can i use DCount on calculated fields in a query?
What I want is for DCount to see how many times a a Box# appears in a table, if it is 0 it puts a message up that the box does not exist.
This is what I have as the code
If DCount("[tblLockbox]![LockboxNo]", "[tblLockbox]", "[tblLockbox]![LockboxNo]=" & Me.[txtGLockbox]) = 0 Then MsgBox "Lockbox not Found! Please try again.", vbCritical Me!txtGLockbox.SetFocus End If
tblLockbox is the table that contains all the lockbox numbers and the names they relate too.
LockboxNo is the field that holds the Box#. I have the field set as a Text because no calculations are done with these values.
txtGLockbox: is the field on the form where they enter the Box#
The problem is I keep getting a data type mismatch in criteria expression. I thought DCount took a count and returned a value, so it shouldn't matter what data type the field in question is.
We are using it in another area where the field in question is a number.
I've been back thru this DCount function, here in the forum and elsewhere. I have posted about this function before and even went back to my old post. Looks like I still need some help.
Here's the premise: My database has a query that tracks Payments made to Students who are on the Federal Work-Study program. We have 4 categories of work: On-Campus; Off-Campus Community Service; Off-Campus Family Literacy; College Support Services.
Of all payments made to students in the year for Federal Work-Study, there are some payments in each category. On the Report, based on the query "FISAP Detail Query", programmed to show every disbursement(payment), I'd like a count of each type in the Report Footer .
I have a control on the report that I'd like to use to count the number of students paid for Community Service.
=DCount("[StudentID]","FISAP Detail Query","[Community Service Amount]>0") and I've tried =DCount("[StudentID]","FISAP Detail Query","[Community Service Amount]>'0'")
Count the number of students listed in the FISAP Detail Query who have a [Comunity Service Amount} greater than zero. Sum totals of disbursements for the year for each student are displayed in the Detail section of the report as a single record. So how many of these records have Commuity Service disbursements; that's what I'd like to know.
The formula returns #Error.
Anybody have any advice for fixing this? It must be some syntax or trying to use the wrong function to do the job.
I am trying to count the number of records in the query result and for some reason, it's not coming up with a number. This comes up "1E+0.."
not sure what's really going on, but this started happening after i converted all my data from excel. However, records come up when i actually go run the query and not from the form.
here's my formula from the form: =DCount("[Queue]","qryODFData","[Queue]= 'NBCT'")
I'm sure some of you have ran into these problems after data conversion.
Ok, I admit that I know just enough about Access to be dangerous, so maybe I'm going about this the wrong way... I'm trying to set up what seemed like a relatively simple Query, but for whatever reason it's just not behaving in the way I thought it would.
We're attempting to set up a database to track sales of product, as work orders, then take that data and reorganize it through Queries for use in our payroll system.
I have the tables, queries and forms set up to enter in our work orders just fine, and there are no issues there. The problem comes when I attempt to re-query that information for use in the payroll side. Here's where I sit at the moment:
I've built a query which pulls data from the [Work Orders] table, using criteria which filters out data one employee at a time, for certain invoice dates, for only certain status codes - the ones which are payable on this payroll week. Then, I built a form, [Payroll1] and added a few fields in it which *should* pull from [Payroll1]![ProductSold] field, and count the number of instances of, say, "Digital" product, tally that number using the DCount function, and display that number on the form, for later data manipulation. It all looked good, until I actually ran the form and instead, recieved an "#Error" in my newly created field, instead of the tally of "Digital" that I expected. Am I using the function DCount wrong, or is there some other relationship that I'm not understanding here?
I have a DCount() statement that checks to see if records (in a qryCheck) meet that condition. I have it where if DCount(…) > 0 then do something. What I would like to be able to do is to display all records where conditional DCount() check = 0 on a certain form. Is this at all possible? I can't seem to figure out how to do: Show all records where DCount(...) = 0. Thank you.
Im trying to count the number of records in a table in a certain field that match a certain criteria.
I've tried to figure out how to use Dcount with some success, but only in counting total records.
Here are two examples. The first one didnt work.
=DCount("RegisteredAt","tblGlobalReg","RegisteredAt=003 - Orland Park") RegisteredAt is a FIeld name in the table tblGlobalReg. RegisteredAt=003 - Orland Park is my criteria.
The second one, with out criteria did work. =DCount("[RegisteredAt]","tblGlobalReg")
I have scoured the net and these forums....although there is plenty of information on DCount I can't seem to find the answer to my question so thought it was time for a post.
I am running a dcount I need to count values that are between (and including) 3000 and 3499 the field is cmbLkpSCType I also need to count values that are between (and including) 3500 and 3999 The cmbLkpSCType values are 3000 through 3499 the field is cmbLkpType
My challenge is to find a way of counting one and not include the other
If Me.cmbLkpSCType = "3000" Then strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2)) strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')") End If '***************** If Me.cmbLkpSCType = "3500" Then strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2)) strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')") End If
I have the code I use below but was now wondering if I can add another cirteria to base the dcount statement on. The code is in the beforeupdate() to allow me to avoid the duplicate.
What I would now like to add is that the Dcount is only calculated on say a particluar date, thus be a criteria in the table. ie I can have multiple locations but do not want to duplicate locations place on the same day.
Dim fBummer As Boolean Dim iReply Dim SID As String Dim stLinkCriteria As String
SID = Me.Location.Value stLinkCriteria = "[Location]=" & "'" & SID & "'" If Len(Me.Location) > 3 Then fBummer = (DCount("Location", "tbl_Data_Vessels", stLinkCriteria) <> 0) If fBummer = True Then iReply = MsgBox("The location " & SID & " has been used." & _ "Please enter the correct location", _ vbFatal + vbOKOnly, gstrAppTitle) Cancel = True End If End If
=DCount("[within]","duration","[within]<= [measure]")i typed this in the control source of a textbox. but it returns an error. can anyone tell me what's wrong?
[within] is a calculated field in the query "duration"
Table: SalesOrders ID: SO Form: SalesOrder textbox:SalesOrderNumber
I am trying to use the DCount function to count the # of records in my table SalesOrders where SO is equal to the value in my textbox SalesOrderNumber . If dcount > 0, I have a duplicate.
I am having trouble with the code for the DCount statement. I tried using the following code, but keep getting an error message that the previous action was cancelled.
I am trying to use DCount in a report and running into an issue with setting one of the criteria to a string variable.
When the report is opened, an input box pops up and asks the user for a parish id.
Here is my code:
Private Sub Report_Open(Cancel As Integer) Dim strparishid As String strparishid = InputBox("Please Enter Parish Id in the form *0000", "Enter Parish Id", "") End Sub
Then I have a textbox with the control source set to the following:
