Here is the SQL that A2K gives me for the query that has the actions I want it to perform. But I can't get it to give me a count of the number of records returned. I would love to use DCOUNT but I am having a tough time working it out. Also in the final soultion, the numbers 2 and 23 are fields that I have 2 = weekday and 23 = [weekday] + [tt] which are in a different table. If someone could help me out with the DCOUNT formula, I would be forever appreciative.
SELECT DISTINCTROW noweekend.noweekday, noweekend.dayid
FROM noweekend
GROUP BY noweekend.noweekday, noweekend.dayid
HAVING (((noweekend.noweekday)=1) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23)) OR (((noweekend.noweekday)=7) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23));
I am trying to put in a control measure which won't allow the user to accidentally add a client to areferral more than once. The simplified relationship structure reads:
I am as a far as using a dlookup which correctly gives the error message, but only if it is the first client added to the tblClientReferral table. If the hypothetical situation arises in which the user adds a client, then adds a second client to the referral and accidentally adds the second clientn twice, the Dlookup is not picking this up.
I suppose the solution is using a dlookup which has the criteria saying if the referralID = 'this' AND the clientID = 'this' Then 'do this'. But as far as I am aware you cannt use AND in the criteria?? atleast, it hasn't worked when I tried. A snippet of the vba code used is below:
Dim objClient As Object Set objClient = Forms![frmAddClient]![cboClientID]
If (DLookup("clientID", "tblClientReferral", _ "referralID = Forms![frmReferral]![referralID]")) = objClient Then MsgBox "Client already added" GoTo ExitSub Else
Is it possible to use an input mask on a calculated control? I have a field which will return "Home Phone:" [HomePhone] and I'd like it to actually display the phone number as (000) 000-0000. Is there a way to do this? Thank you!
I have a calculated control on a form that is summing other controls and then a subtotal on a subform. This is access 2007
The calculated control does not display the value until I enter a value other then the default in all of the source controls. If I hit the "refresh" button, the value appears in the calculated control for a moment, then disappears.
I have set the default of the source controls to be 0, so none are null.
the calc control value does not stay until I put a value in the subform and its subtotal thus has a value
Eventhough i know calculated control are for queries, I still need to store a calculated value of a sum. I have a form with customers, dates, locations. That form has a subform that contains the services done and materials used. This subform is in datasheet view. In the footer i have a control with =Sum([Price]).
So I need the value of this control to be stored in the table the main form is based on. I did an unbound control in the main form where the =Me!Subform.Form!.Textx will bring the value "over" Then for the on focus event the vba code Me.Total=Me.Textx stores the value. That is fine. The same works with a command button with the same vba code.
However i am asking if there is a more automatic way to get the value over without the unbound control/command button.
I'm getting a Name(hash) error when I try to set up a control source to a calculated query.
I'd like to just be able to create a form and the write in the control source as it look neater (and it the right way to do it) then subforming a query for one text box.
I presently have tried in the expression build =[qryCallRunningAverage]![Average]
I am trying to use DLOOKUp and Trim in a calculated control on a form - D look up works but when i add Left trim to trim a telephone number to just the prefix - it wont work
=DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form]![Data] & "'") - Works OK =DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form],7) & "'") - appears to put square brackets around Left ??
i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
In my DB i would like to make a form that displays different calculated values based on other forms and queries like income running cost etc., but i do not know how to refer in an unbound control to value of a control in another form. I get always #names? error. However it worked when loaded that form i refered to. Is there a way to do it without loading the refered form?
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?
I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
