Dlookup / Dcount ? Stumped!!!
Feb 10, 2005
Hi all,
I have a form with the following fields:-
StartTime = Format dd/mm/yyyy hh:nn:ss
ExpFinishTime = Format dd/mm/yyyy hh:nn:ss
Dentist = Number
StartTime is bound to my table, ExpFinishTime is calculated in the forms underlying query.
Before the record updates I am trying to check if anyone already has an appointment scheduled in during that particular time for that particular dentist. So if the record being added has a starttime between those two times for that dentist, throw up a message box.
Thanks for any help.
Taff
View Replies
ADVERTISEMENT
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
Aug 2, 2006
Hi,
I m trying to find a way of automatically inputting the progress of my project.. it's confusing so i'll try ot explain more. I have a project plan broken down into weeks . Each element of this has a schedule and complete date for various different milestones which indicate what week my project is sitting at:- eg: I have reached this milestone therefore my project is at week 10 and so on until completion.
Is there some query I can run that can do this automatically?
I'd be grateful for any help on this.
Thanks K
View 14 Replies
View Related
Feb 17, 2005
hi all - i know there are many threads relating to linking pictures to records etc, but forgive me for asking one more time:
I have managed to get pictures into my dbase - using Microsoft KB help - by linking them in a table to an external source.
However, I want the dbase to know which picture is for what details. eg. I have a cars sales dbase. I have all the pictures in one folder of all my cars which are linked to the dbase. When I pull up car details in the form, I want the picture to come up aswell - the correct picture.
Pls let me know if this has been done. Sorry for extending this topic; however, I could not find a "simplish" soln. to this.
Thank you once again :o
View 4 Replies
View Related
Apr 25, 2008
I’ve developed a query that produces a list of forms and an effective date where the form may be applicable. But now I am stumped. I need to reduce the results to a specific state (which I’ve done and it works) and for a specific date (the stumped part). Let’s say user needs 2/15/2005. I think I can eliminate forms with effective dates greater than 2/15/2005 (not quite sure how yet) but I am perplexed how to eliminate certain forms that are no longer valid because they’ve been superceded. In the sample table for CA, I would expect the following forms retuned and no others: CG0001 1204; CG0002 1204; CG0009 1204; CG0033 10 01; CG0034 1204; and CG0035 1207. The expected results for the CT column would be different (though some would be the same).
Any advice to point me in the right direction would be appreciated.
FormNumForm_vdt CA CT
CG00010798 09/01/1999
CG0001100103/26/200102/01/2002
CG0001120407/13/200407/01/2005
CG0001120712/05/200712/01/2007
CG0002100103/26/200102/01/2002
CG0002120407/13/200407/01/2005
CG0002120712/05/200712/01/2007
CG0009100103/26/200102/01/2002
CG0009120407/13/200407/01/2005
CG0009120712/05/200712/01/2007
CG0033019603/26/200105/01/1996
CG0033100107/13/200402/01/2002
CG0033120412/05/200707/01/2005
CG0033120712/07/200712/01/2007
CG0034100103/26/200102/01/2002
CG0034120407/13/200407/01/2005
CG0035100112/05/200702/01/2002
CG0035120403/26/200107/01/2005
CG0035120707/13/200412/01/2007
View 2 Replies
View Related
May 18, 2005
I have a form with two subforms. One of the two subforms works perfectly. The other just gives me a grey screen (like there's no recordset).
The two forms are very similar and I've checked everything I can think of... Here's what I've done. If you can think of anything else to try, please let me know - This is driving me crazy!
1) The subform works independent of its parent
2) The source object for the subform is set properly
3) The child and master fields are set properly. Data types are the same.
4) The record source for the form (used as subform) is set to the correct query.
5) The query used has been run independently with no problems
6) The form has a bound text box with the proper control source
7) I've tried placing a breakpoint at the first line of form_current.. never gets hit.
I don't know why it's not working and there's nothing that tells me what/where the problem is. I'm out of ideas on this one.... Any help is greatly appreciated!
View 3 Replies
View Related
Apr 25, 2005
I am having trouble visualizing how to break out the following info into functional Tables
I have a table to hold Items
tblListG1Items
IDItemList
ITemNumber
ItemName
I then have a detail table
tblDetailG1Items
IDItemDetail
FKToItemList
ItemMFG
ItemMSP
ItemLead
Here is where I get lost.
Each item has a variable for each month
So item 1
January = .01
February = .03
ECT.
Once set the Variables rarely change, at most once per year.
I will need to be able to pull an item and based on what month it is pulled uses the variable to multiply several other factures.
My first Thought was,
tblVarItemList
IDVarList
FKToItemList
VarJanF
VarFebF
VarMarF
--And so on--
It just does not look right.
Any Suggestions on what might work Better. :confused:
View 6 Replies
View Related
Jun 3, 2005
I've been asked to collate some information based on a kid league, I am trying to sort out how precisely it works
the data I have is this
finish position, name, club
I need to calculate the best kiddie based on 3 of a possible 6 races
So tbls
tblResults
fldSeries
NAME
CLUB
Pos(isition)
tblScores
fldID
pos
Points
query1
SELECT tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, Sum(fldScore.Points) AS SumOfPoints
FROM tblResults LEFT JOIN fldScore ON tblResults.Pos = fldScore.Pos
GROUP BY tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, tblResults.Pos
HAVING (((tblResults.fldSeries)="220-2005") AND ((tblResults.Pos)<51))
ORDER BY Sum(fldScore.Points) DESC;
I need this to be changed to only pick up athletes with only the best three scores?
any hints?
STuart
View 3 Replies
View Related
Nov 2, 2006
Form: frmProduct
Field: unitprice.
I have some simple validation that unit price is greater than 0. The code is this:
If Me.txtUnitPrice.Value <= 0 Then
MsgBox "Please enter a value greater than zero", vbOKOnly, "Alert"
Me.txtUnitPrice.Value = Null
Me.txtUnitPrice.SetFocus
End If
However, when I run it, the setfocus doesn't work. It jumps straight into the next field. I can make it run to any other field (productname, productID). But not back to UnitPrice.
View 6 Replies
View Related
Mar 27, 2008
Hello,
When I try to run any query that contains a specific table I get a message a Not enough memory error. I can open that table which, contains 47 thousand records and view that data unless I get to one record then the entire database crashes. I have tried to Compress and Repair and the database crashes. When I say crash I means it completely disappears and I get the send error to Microsoft Message. I can not select record due to it crashes when I get to it. I have also tried selecting record by going to one above the quickly scrolling to a record way below it and it errors and crashes. I have tried to delete it using a delete query and it only returns “Invalid argument” when I try to select or delete.
ANY suggestions or ideas would be greatly appreciated.
View 10 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 23, 2005
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 hope this makes sense.
Kim
View 3 Replies
View Related
Jun 15, 2007
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.
Any help will be very much appreciated
Cheers!
Goh
View 8 Replies
View Related
Jan 18, 2008
Hello,
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.
Does anyone know what it could be?
Thank you
View 3 Replies
View Related
Aug 8, 2006
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?
Thanks in advance!
~Lana F Call
View 14 Replies
View Related
Jan 26, 2005
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.
View 4 Replies
View Related
Mar 15, 2005
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 guess my question is, how do I insert criteria?
Matt
View 1 Replies
View Related
May 20, 2005
In the After Update Event of the field Lastname I have some code to check
if a person with that First- and Last name already exists in the database:
If DCount("*", "[tblPeople]", "[LastName]= '" & Me.LastName & "' And [Firstname] = '" & Me.FirstName & "'") Then
Beep
MsgBox "This name already exists in the database!" & vbCrLf & " Please check for duplicate entry.", vbExclamation, ""
Me.Undo
Cancel = True
This works just fine, but the problem starts when the Lastname is something
like: O'Leary.
Then I get this error:
Syntax Error(Missing Opererator) in Query Expression '[Lastname] = 'O'Leary'
and [Firstname] = 'John".
Obviously it is the ' that is causing the problem.
Any solution for this?
Thank you all.
View 2 Replies
View Related
Dec 12, 2005
This is my string to calculate the next increment # based on request date and users initials.
However, I get the #NAME? error when I run the form?
=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"#mm/dd/yyyy#") & " AND [initials] = '" &
me.txtinitials & "'")+1
Any help will be appreciated.
Thanks.
View 3 Replies
View Related
