Dcount Vs SQL(VBA) For A Calculated Control
Oct 7, 2005
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));
View Replies
ADVERTISEMENT
Jun 9, 2005
I am trying to use DCount to count the number of record that = 0 for field checklistitem_1 in table 'Data Input'.
I firstly tried =DCount("[ChecklistItem_1]","Data Input","[ChecklistItem_1] = 0") but this returned nothing.
So I tried =DCount("*","Data Input") just to see if I could get something back and still nothing.
I double check the names are correct, dropping the quotation marks different combination of brackets and nothing every time.
Please help me
View 1 Replies
View Related
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
Oct 15, 2013
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:
tblClient 1-------n tblClientReferral n---------1 tblReferral
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
View 5 Replies
View Related
Apr 24, 2007
Hi,
I am trying to create a calculated field in a query which uses another value in the query to lookup a table. I get an error running the query :
"Microsoft Access can't find the name 'Indcat' you entered in the expression."
The expression I am using is as follows:
Expr1: DLookUp("[Class]","Class","([Indcat] = [Clind]) AND ([2007] Between [From] AND [To])")
Indcat and 2007 are valid field names in the query.
I used this Dlookup in a form and it worked fine (with Form! etc references to Indcat and 2007)
Mystified
Tony Randell
View 3 Replies
View Related
Jan 3, 2006
if i have formula in a text box can that calculation be stored in a table???
example
sales total
sales tax
grand total
where grand total = sales tax+sales total
if the control is bounded to a field in a table once the formula is entered doesnt it loose the connection with the field???
right now when i enter a formula in a control it will no longer store it in the table but it will only show the result in the actual form..???
View 4 Replies
View Related
Jan 31, 2006
Is there a way to get a calculated Control of a form to end up in the underlying table?
View 2 Replies
View Related
Oct 4, 2004
Hi,
I am a first time user to access groups, so I am hoping for a
good experience.
I have a form which has a calculated control. But the calculation
shows on the form but is not storing in the record.
On the property sheet, the name is charge and the control source is
the calculation (=[source1]+[source2]...)
Why is it displaying but not storing? I do not know Visual Basic, so I would
like a solution that is simple if there is such a thing.
Thanks in advance,
Trainjoan
View 2 Replies
View Related
Nov 2, 2005
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!
View 1 Replies
View Related
Nov 7, 2013
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
View 5 Replies
View Related
Jul 5, 2015
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.
View 7 Replies
View Related
Mar 23, 2014
I have a report and in the footer I have added a text box (Textbox136) to work out the average percentage of the field [Percentage], which works fine.
For the value in textbox136 I want to output a grade and want to use an IIF function in the control source, something like:
IIf([Textbox136]>=100,"A+",IIf([Textbox136]>=90,"A+",IIf([Textbox136]>=80,"A",IIf([Textbox136]>=70,"B",
IIf([Textbox136]>=60,"C",IIf([Textbox136]>=50,"D",IIf([Textbox136]>=40,"E",IIf([Textbox136]>=30,"E-","U"))))))))
But it won't work.
View 1 Replies
View Related
Dec 5, 2013
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]
View 1 Replies
View Related
May 22, 2013
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 ??
View 5 Replies
View Related
Sep 14, 2006
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.
View 2 Replies
View Related
Dec 18, 2014
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.
View 13 Replies
View Related
Jul 19, 2015
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?
View 3 Replies
View Related
Feb 18, 2006
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.
Can anyone help me?
Thanks very much in advance
View 1 Replies
View Related
Aug 8, 2006
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.
Any help would be awesome, thank you.
View 7 Replies
View Related
Mar 26, 2007
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".
Any help would be great, thank you for your time.
View 14 Replies
View Related
Feb 26, 2008
dear all,
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
Begin End sumactive
20080206 20080208 1
20080200 20080216 2
thanks
regards
martell
View 1 Replies
View Related
Feb 28, 2006
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 = ?
Could some help please?
View 8 Replies
View Related
Apr 3, 2006
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.
Any corrections please?
View 2 Replies
View Related
May 3, 2006
Hi
Can anyone see what I have done incorrctly as this does not work!
Thanks in advance.....
=DCount("[call type]","qryISAHistoryCount","[Label] = 'Call'" And "[User] = 'Craig'")
View 1 Replies
View Related
Jan 5, 2005
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?
View 1 Replies
View Related
Sep 24, 2013
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?
View 1 Replies
View Related
