DLookup Usage Samples

DLookup Usage Samples

Note ……… that the same logic applies to most Domain Aggregate Functions (DMax, DMin etc.)


Normal usage

For numerical values:

DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:

DLookup("FieldName" , "TableName" , "Criteria= #date#")


Refering to a Form Control

For numerical values:

DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

For strings: (note the apostrophe before and after the value)

DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

For dates:

DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")



DLookup("FieldName", "TableName", "Criteria1 = " & Forms!FormName!Control1 _

& " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _

& " AND Criteria3 =#" & Forms!FormName!Control3 & "#")


Refering to Field Values in a Recordsource

DLookUp("[Field1] - [Field2]", "TableOrQueryName", "[CriteriaField] = {criteria expression}")

View Replies


Domain Function Usage Samples

Domain Function Usage Samples

This applies to Domain Aggregate Functions: DAvg, DCount, DFirst, Dlast, DLookup, DMax, Dmin, DstDev, DstDevP, DSum, DVar, DVarP

Criteria could be a key or other field

Typical Use

Numerical values:

DLookup("[FieldName]" , "TableName" , "[Criteria] = n")

Strings: *a single quote before and after the value

DLookup("[FieldName]" , "TableName" , "[Criteria]= 'string'")


DLookup("[FieldName]" , "TableName" , "[Criteria]= #date#")

Referring to a control on a form

Numerical values:

DLookup("[FieldName]", "TableName", "[Criteria] = " & forms!FormName!ControlName)

Strings: *single quote before and after the value

DLookup("[FieldName]", "TableName", "[Criteria] = '" & forms!FormName!ControlName & "'")


DLookup("[FieldName]", "TableName", "[Criteria] = #" & forms!FormName!ControlName & "#")


DLookup("[FieldName]", "TableName", "[Criteria1] = " & Forms![FormName]![Control1] _

& " AND [Criteria2] = '" & Forms![FormName]![Control2] & "'" _

& " AND [Criteria3] =#" & Forms![FormName]![Control3] & "#")

View Replies View Related

DLookup Usage - Version: 2000 (9.0)

I have a report that is already based on two queries that needs to display 28 values (times of the day, activities, etc.) from a table different from the ones tied into the queries. I'm using "DLookup" now and it works great, but I was wondering if using 28 instances of it in one report is too many. The table has 28 fields with only one value (the one being accessed by "DLookup") in each field.

View Replies View Related

Question On Usage Of DLookup - Version: 2002 (10.0) XP

i am having trouble with a DLookup. what are each sections and where do i get them from.

this is my problem. i am making a database which logs students onto courses. there are only a certian amount of places on each course. i need to make it so once the course is full no more students can be added to the course.

the students are added by a form which has a subform. you click a combo box to choose a student ID and the subform shanges to the courses they are on. you can add the courses by clikcing on the combo box in the subform and choosing a course which adds it to the students list.

i am using VB for this problem and i have a query which deletes the course from the student but i need to know the coding and i cant think.

i need to lookup the course full, Yes/No box in the table called tbl_CourseDetails
but i dont know the criteria where do i find that and once i have the Dlookup where do i go from there?

View Replies View Related

Submission Of Samples

Hi All

I would just like to find out how long it takes before a sample submitted to the samples forum is approved. I submitted a database about 2 days ago and I see that nothing has shown up on the forum.


View Replies View Related

Where To Find Office Developer Samples And Tools Available For D

Take a look at this Microsoft Support Page .

View Replies View Related

Inlcuded Access Samples - Version: 2003 (11.0)

A previous version off Office that I used had some databases in the Sample folder (c:program filesmicrosoft officeoffice_xxsamples) but I don't seem to have them at my current work.

One of them was a contact management database which I thought was a little better than the template that Access creates... does anyone have this particular database they can attach here?

Thank you, please!


View Replies View Related

Default And Custom MsgBox And Input Box Samples! - Version: 2000 (9.0)

This sample application demonstrates how to use all sorts of input/message boxes!
It includes default access versions and custom ideas.
Here are some samples included:
Default MsgBoxes (critical, information...)
Default MsgBox closing itself without user interaction
Custom MsgBox closing when idle
Default InputBox checking cancel press
Default InputBox with password mask
Custom InputBox with password mask
Custom InputBox closing when idle

Tested on Win2k and Acc2k.
Feel free to provide comments/suggestions/questions...via PM at utteraccess.com (freakazeud)!

View Replies View Related

Progress/Status Bars Demo Samples - Version: 2000 (9.0)

I was asked several times about a progress bar. I haven't seen too many documented examples, so I thought I fast put together a db with a couple of different variations. Maybe some other people would find this helpful and useful for their applications.
You can use progress bars when opening a DB, running queries, opening forms... Any time you want the user to have some kind of visual confirmation about the progress. Don't let the user unknowingly sit in front of your application.
I create the db in ACC 2000 and it worked fine. I did not have time to test in other versions, sorry.
Feel free to provide feedback via private message.
Thanks and have a good day,

View Replies View Related

Usage Help Please...

How do you calculate usage? For example...
If I've got a field named Time, then a drop-down box with "Morning", "Afternoon" and "Evening"
How do I make a query so it displays the number of each time...
For example...

There's two times for Morning, three for afternoon and one for evening.

I tried using the 'Sum' total and the criteria of "Morning" but it says data mismatch. That was merely a guess, so I would like to know how to do it.



Morning: Afternoon: Evening:
2 3 1


View Replies View Related

% Usage

how do i use a query to calculate % usage of a particullar feild. ive got a total box and a current amount but i need a query. thx in dvanc

View Replies View Related

Query Usage

Over the last 12 months my database has grown in size.

I'd like to delete queries that are no longer used. Is there a utility I can use that will list where all my queries are used, ie used in other queries + reports.

Does anyone know?


View Replies View Related

Usage Of Enter Key

I have a continuous form with an unbound text box (for search critera) and a command button to kick off the search, which simply applies a filter.

When a user enters text in the unbound text box and hits "Enter", I want the search to be kicked off (instead of having to tab over or mouse over to click the "Search" command button. This is the default functionality of an HTML <form>, so users kind of expect it. But I'm having trouble implementing it in MsAccess.

I've tried using the KeyDown property of the unbound textbox, but no luck, since the KeyDown method occurs before the data in the box is saved. I'm guessing this is related to detecting what key has been pressed but my issue seems to be the order of events occurring on the form.

Any suggestions? This *must* be common situation, but I've found no related topics in searching the forum.

View Replies View Related

Audit All Objects Usage

We have an access app for reporting using a mixture of local tables and linked table in Oracle, Access queries, Forms and Reports.

Ther app has grown over time and has become quite cumbersome to work on.

We want to strip out parts that are not being used but need to identify what is being used first.

Is there a way to keep an audit trail of all object usage within the app? The audit doesn't need to be complex, just object name, timestamp and user.

Thanks in advance.


View Replies View Related

General Query Usage

hi Guys,

need help in using query, plz let me know that how to use designed query in Reports ?
thanks in advnce.

View Replies View Related

Parameterized Query Usage

So I have a query which prompts the user for a username to search by, GetUsername.

Im used to TSQL usage which would be something like:

exec GetUsername 'blah'

My goal is to generate about 10 reports based on this query but with different (hardcoded) input. I do not want to prompt the user while generating these reports.

How would I invoke this query with a hardcoded parameter? (Id rather not save 10-20 versions of it).

View Replies View Related

Logging Usage Of Records

I have a form from which people view records out of a db.
I would like to log the date/time and the key record they view into a table
The usage table has a date/time field & a string field for the viewed record.

I can run an append query to update the Usage table but can't figure out how to get the date/time into the table ?

Is it easier to write the whole lot out to a file ?

View Replies View Related

Can I Calculate Peak Time Usage?

Hello All,

Is it possible to generate a query that can calculate the 'TotalMins' that an event covers our peak times?

I.e. Our peak times are 0700 to 1000 if we have a start time of 0600 and a finish time of 0900 is it possible for the query to work out that we have covered 120 mins of the peak period?

Thanks for any help provided!:)

View Replies View Related

The Eval() Function And Its Possible Usage. - Version: 97 (8.0)

G’day all.

Here is a little A97 demo of some of the tricks that the Eval() Function can get up to.

When the Eval() Function is passed the correct arguments, in the correct way, it becomes a very good line parser. It will parse both Logical and Analog expressions correctly if we pass the correct information.

The A97 demo uses a Replace97() function (that uses a GoTo…sorry about that) but it also converts to A2K OK.

At the end of the Analog section is an Eval() of a ‘V’ Notch Weir calculation that was derived for http://www.lmnoeng.com/Weirs/vweir.htm. The accuracy of the data generated in the Report, produced by the Eval() call, can be verified on that site.

Simply place the cursor in the desired Sub in Module mdlTestCases and press F5.

Of course, how you use it is up to your imagination.



Edit: Fixed link. DougY

Edited by: DougY on Sat Nov 25 1:41:06 EST 2006.

View Replies View Related

Form Usage - Version: 2003 (11.0)

Ok I have a database that I've built to keep track of end of season softball stats. An issue that I'm having is the form I use to input new player names. I want this form to react differently depending how it is accessed. The form contains a Player ID, Last Name, First Name, and DOB.

While using the stat input form, I can't add new players and must open the input player form and add the player and then reopen the stat input form. I want to be able to access the player input form (the easy part), but then due to it being opened via that stat input form jump to adding a new record. After this form is closed I'd like for the stats input form to be reopened with access to the new name.

I've tried using a variable that changes as certain events happen, and then use if then statements. I'm not a full bottle on VB or Access and would like some help on how to solve this problem. I'd rather not use an additional form however will if that is considered the smarter option.

Also this project is a work in progress to help me build my database and VB skills

View Replies View Related

CPU Usage Module / Command - Version: 97 (8.0)

I'm looking for a module / command to return the current CPU Usage. I'm using Access to boot a statistical software and if the CPU Utilization is too high, the application sometimes fails. If I knew the CPU Usage, I could not boot the statistical application until the CPU Utilization was okay.


View Replies View Related

Help With WHERE Clause Usage - Version: 2003 (11.0)

I am trying to use a WHERE clause to run a Select Query from a form with 5 drop down menus.

The problem I am having is that if the user leaves one of the drop downs 'empty', I need the query
to return ALL the records, including the ones with empty fields. I posted a snippet of code earlier and
got some response, but no solution yet.

I need the following to work, if possilbe. (

If IsNull(Me.cboInjuryType.Value) Then
strInjuryType = " Is Null Or Like '*'"
strInjuryType = "='" & Me.cboInjuryType.Value & "' "
End If
This will return a Syntax error stating that there is an operator missing in the query statement, based on the
second line of code 'strInjuryType = " Is Null Or Like '*'"

Is it possilbe that you can NOT state that you want ALL returned, including NULL values?
I originally thought that '*' would return ALL, but apparently it will ignore the empty fields, and simply
not return those records. I need to return them all.

Thanks - I look forward to the great UA help that I have seen on other's questions.


View Replies View Related

Filter Usage - Version: 2000 (9.0)

I am creating a client database for a company and I am using forms a lot to make it easy for them to use. I have created a form that allows them to search and sort via many different methods using filters. I would like to have an update button that changes the values in all the fields only included in that filter. Is there some way to use save or store which filter is used and use an update query or some better way?


View Replies View Related

Dual Usage - Version: 2003 (11.0)

what happens if 2 users try to access the database on the server at exactly the same time??

View Replies View Related

Limit DB Usage - Version: 2003 (11.0)

Hello all,
Iam making a database that will have 4 colums and numerous rows. Heres what I wanna do. I want to make a DB that allows the user to scan a user name barcode, jump to the next cell, scan the associated equipment barcode, jump to thenext cell, scnan sign in, or sing out, and go directly to the next record. Is there a way to do it, without ever touching a keyboard, or a mouse... Like some VB Code??


View Replies View Related

IIf Function Usage - Version: 2003 (11.0)

I am writing a very simple database for my son's band program and really do not know what I am doing! Can anyone tell me if I can use the IIf function on a report based on a query that will return a letter grade based on criteria? I have several students that turn in practice records that have parent signatures on them. I built a query to sum the total minutes and also had it sum the amount of times it was not signed by the parent. I want the report to generate a letter grade, A,A-,B,B-,etc.. based on these results. I tried to use this statement and was returned an error: =IIf([SumOfMin Practiced]>100<150,"B"([SumOfMin Practiced]>150<200'"A"))

Any help would be greatly appreciated!


View Replies View Related

Concurrent Usage - Version: 2003 (11.0)

I have developed a database which renames files. The user enters the path to a folder. This path is stored in a table. The database then loops through all the files in that folder and loads the filenames into a table. The user then assigns values into that table and clicks a button that renames the files.

This database has been placed on the network so that anyone in the department can use it. Will it cause problems if two people try to use it at the same time? What can I do to prevent any problems?


View Replies View Related

Calculating Usage For No Record - Version: 97 (8.0)

OK, one of the functions of the db I am rebuilding is to calculate vehicle usage. After normalizing the db, I have a tblvehicles that stores all the info pertinent to a vehicle, I also have a tblVehUsage which stores the usage info. On a report that needs to be generated, each vehicle is reported and the usage for each month is reported. My question is this, what if there is no usage for a month? Then there would be no record for the vehicle for the month in tblVehUsage!!

So let's say November a car doesn't go out, how do I report that? There is no record to show 0 miles used. Shoud I just add a record for the month that actually records that value...0? Seems feasible but then you have records with 0's in them....Hmmmm.

Thanks in advance for the help. Freakazeud, Im sure I'll see your image below mine here so HI!!


View Replies View Related

Track Form/Query/Report Usage


About 10 years ago I wrote an Access application which is still in use. Over the years certain parts have become superfluous and new parts have been added by a variety of users. Thankfully they have stuck to the original ideal and only used macros and queries - and not modules of badly written code.

However, the system is beginning to creak and I have been asked to rewrite the system from scratch with a shiny new interface, cutting the dead wood and incorporating those new features that did not have a half life of one report.

I would like to be able to monitor the use of the system in order to be able to determine just which tables, forms, queries & reports are being used. I'm not interested in auditing who added what to which table, so a 'normal' audit system is not required. Can anyone tell me how I would record which of the above are being used and how regularly?

I have asked the users and (of course) they don't know.


View Replies View Related

Converting To 2007 From 2003 - High Cpu Usage

This is very strange. I have converted two computers from 2003 to 2007. I have also installed 5 2007's on to new computers. All of these are Professional XP sp2 setups. I copied over a database to woek on those computers. Originally the performance was very good, on all of the computers (but not the lowest specified converted laptop ) exitting from a form containing a subform takes 37 seconds. The word Calculating... is on the status bar. The laptop takes 3 seconds par with what it used to do.

I have transferred the 'Data' database, the 'Apps' database, Utility.mda and another .MDA. I have also transferred all the Reference executables to one of the other computers. This has not made a jot of improvement.

Has anyone any pointers. I have exhausted all the resources trying to find a solution to this one.

View Replies View Related

Table Link And Multiple Combo Box Usage

Could someone help me ?

I'm really experiencing problems ...

I will need those tables : tblCostumers , tblSystems , tblRepair

tblRepair will link through its unique CostumerID primary key, as you said but,
is it through a foreign key ?

I will need a "tblSystems" too, witch will contain all the details about the computer systems each costumer has at home or work.
How to link this to each costumer ?

Another doubt :

At the "tblReapir" I'm thinking to use combo box to select all repair procedures like combo boxes follow, so future statistical analysis will be made easily :

Replacements combo box

. . .

Software combo box

. . .

Problem is : A repair service may need several
items to be selected from the same combo box
( in example : Replacements -> HARD DISC + POWER SUPPLY and
Software Installation -> WORD + ANTI VIRUS ) but each new item
I select from the same combo box must be stored on a different field on the repair table ( If I'm thinking well ) .
As each combo box is located into its own field and associated with it, how to do this ?

Can I make appear a new combo box at the right side of each one I use,
so I will have so many combo boxes from the same type as my needs of selecting new items from the same type ?

My idea is :

1. Initial visible combos are:

Repair Software

2. I select a item from repair and visible combos are :

Repair Repair Software

4. I select a item from Software and visible combos are :

Repair Repair Software Software

Will this solution create combo boxes that although they are necessary for one repair service, they may be unnecessary to other repair services, being there anyway, as different repair services share common columns ?

Anyway, if this is a valid idea, how to implement this ?

Or ... Is that another way to approach the problem ?

Lots of thanks in advance,

Miguel Garcia

View Replies View Related

Report Usage Tracking - Version: 2000 (9.0)

Is there a way to track each time a report is printed out of a db?

View Replies View Related

Add Usage To Part Table - Version: 2000 (9.0)


I would like to create an archive folder of parts that are not use from my main parts table.
We have a good 2000 bolts/nuts/washers that will never be used. The one who created the catalog used every single bolt config from our suppliers but we just wont use them all. So I want to archive the lesser used ones, but keep them available.

So I need to add a field to the parts table (I think) to tally the usage of the parts based on when they get called up in a project.


View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved