If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
New User, having problems with DLookup
First of all, this is the first time I have tried using Access, let alone
trying to build a database from scratch, so if I've made a ton of mistakes, please bear with me. I am attempting to build a database to calculate allowed vacation and personal time, as well as track it automatically rather than me doing it manually. The intended end result is a database that can be used year after year by only changing a couple of fields. At the moment I am stuck on getting it to calculate allowed vacation. The string I used to test my syntax - that failed miserably - is: DLookup ( [FT Vacation]![Allowed Vacation] , "=1990", [FT Vacation]![Hire Year] ) I also have an outline of IIf statements that include the DLookup for once I finally get just this part working. There's probably an easier way, but I don't know what it is. Thanks for any help and constructive advice that you can give me. Phelonia. |
#2
|
|||
|
|||
New User, having problems with DLookup
On Wed, 23 Dec 2009 22:34:41 GMT, "Phelonia" u57034@uwe wrote:
First of all, this is the first time I have tried using Access, let alone trying to build a database from scratch, so if I've made a ton of mistakes, please bear with me. I am attempting to build a database to calculate allowed vacation and personal time, as well as track it automatically rather than me doing it manually. The intended end result is a database that can be used year after year by only changing a couple of fields. At the moment I am stuck on getting it to calculate allowed vacation. The string I used to test my syntax - that failed miserably - is: DLookup ( [FT Vacation]![Allowed Vacation] , "=1990", [FT Vacation]![Hire Year] ) I also have an outline of IIf statements that include the DLookup for once I finally get just this part working. There's probably an easier way, but I don't know what it is. Thanks for any help and constructive advice that you can give me. Phelonia. DLookUp is probably not the best tool for this task: it's inefficient and may not be as flexible as you would like. A Query can do the calculation for you, probably better! However, to help you with the query would require that you post some information about the structure of your tables and where the data can be found. That said... you're misusing DLookup. The Domain functions (DLookUp, DCount, DSum etc.) all take three String arguments. The first argument is the name of the value to be looked up; the second argument is the name of a Table or Query from which it should be looked up; and the third argument (which is optional) is a valid SQL WHERE clause criterion specifying which record should be looked up. Again, not knowing how you are storing the data, I can't suggest the correct DLookUp for your case - but as I say, it's probably not the right tool anyway! More info please? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
New User, having problems with DLookup
Thanks, John.
Like I said, I've never used Access before (nor SQL for that matter), so my misusing DLookup is not a surprise. As for the structure of my tables, I'll do my best to explain it. In the table named Employees2 (I've been playing around with it quite a bit to get it working the way I want it to, so many tables are 1 2 3 etc...), there are fields for ID, Last Name, First Name, HireDay, HireMonth, HireYear, Status (FT, PT, SAL or ARR), Lead (lookup column), Book (where I keep all of the vacation requests). Table Leads has merely LeadID and Lead Name - made solely for the lookup column. I did it this way so there was no way to change a name accidentally, plus which supervisor a person has can change from time to time. Table PT Vacation and table FT Vacation have PT ID (or FT ID), Year of Hire, Months Remaining in Year of Hire, Full Calendar Year, and Allowed Vacation. I think I should explain this one a bit. An employee's hire date determines how much vacation they have the year they are hired as well as their first full calendar year. Let's say one was hired in the last three months of the year - they are not eligible for vacation that year, and only 5 days the next year (their first full calendar year. A person hired during the first 9 months of the year is eligible for up to 4 days the year they were hired, and 8 days the next year. After that first full calendar year, it is the same for everyone - it just goes up at the 5th full calendar year, and from the 16th to the 20th they get an extra day per year up to 20 days. After the 20th year, they're stuck where they're at. ARR is easy - it's 0 vacation across the board, and SAL needs to be manual entry because it changes from person to person depending on their contract. I also need to include personal time somewhere, but that's much easier to determine - except for the year they are hired. FT gets 16 hrs, PT gets 10, ARR gets 0 and SAL needs to be manual entry. In addition to all that, I need it to track vacation and personal time used somehow, and be able to export it into a report that can be by status, lead, employee, amount/percentage remaining, or any combination thereof. Is all of this possible? Man, I hope so.... Thanks again, Phelonia John W. Vinson wrote: First of all, this is the first time I have tried using Access, let alone trying to build a database from scratch, so if I've made a ton of mistakes, [quoted text clipped - 16 lines] Phelonia. DLookUp is probably not the best tool for this task: it's inefficient and may not be as flexible as you would like. A Query can do the calculation for you, probably better! However, to help you with the query would require that you post some information about the structure of your tables and where the data can be found. That said... you're misusing DLookup. The Domain functions (DLookUp, DCount, DSum etc.) all take three String arguments. The first argument is the name of the value to be looked up; the second argument is the name of a Table or Query from which it should be looked up; and the third argument (which is optional) is a valid SQL WHERE clause criterion specifying which record should be looked up. Again, not knowing how you are storing the data, I can't suggest the correct DLookUp for your case - but as I say, it's probably not the right tool anyway! More info please? |
#4
|
|||
|
|||
New User, having problems with DLookup
On Thu, 24 Dec 2009 00:06:04 GMT, "Phelonia" u57034@uwe wrote:
Thanks, John. Like I said, I've never used Access before (nor SQL for that matter), so my misusing DLookup is not a surprise. As for the structure of my tables, I'll do my best to explain it. In the table named Employees2 (I've been playing around with it quite a bit to get it working the way I want it to, so many tables are 1 2 3 etc...), there are fields for ID, Last Name, First Name, HireDay, HireMonth, HireYear, Status (FT, PT, SAL or ARR), Lead (lookup column), Book (where I keep all of the vacation requests). Rather than three separate fields for the hire date, just use one Date/Time field HireDate. You can easily use Year([hiredate]), Month([hiredate]) and Day([hiredate]) to extract the needed values. Table Leads has merely LeadID and Lead Name - made solely for the lookup column. I did it this way so there was no way to change a name accidentally, plus which supervisor a person has can change from time to time. There's no need - and many drawbacks - to use a Lookup Field for this purpose. The only thing a Lookup Field does is make it a bit easier to use table datasheet view - but you shouldn't be using table datasheet view for data interaction anyway, so why bother? Just leave it a Long Integer field and use a combo box on a form to select the ID (while displaying the supervisor's name). Table PT Vacation and table FT Vacation have PT ID (or FT ID), Year of Hire, Months Remaining in Year of Hire, Full Calendar Year, and Allowed Vacation. I think I should explain this one a bit. An employee's hire date determines how much vacation they have the year they are hired as well as their first full calendar year. Let's say one was hired in the last three months of the year - they are not eligible for vacation that year, and only 5 days the next year (their first full calendar year. A person hired during the first 9 months of the year is eligible for up to 4 days the year they were hired, and 8 days the next year. After that first full calendar year, it is the same for everyone - it just goes up at the 5th full calendar year, and from the 16th to the 20th they get an extra day per year up to 20 days. After the 20th year, they're stuck where they're at. You can use the DateSerial and DateDiff functions to calculate this. Type Ctrl-G to open the VBA editor (to get connected to the right help file) and look for Help on these. For example, DateDiff("m", [HireDate], Date()) will get the integer number of month changes since the hire date. ARR is easy - it's 0 vacation across the board, and SAL needs to be manual entry because it changes from person to person depending on their contract. I also need to include personal time somewhere, but that's much easier to determine - except for the year they are hired. FT gets 16 hrs, PT gets 10, ARR gets 0 and SAL needs to be manual entry. In addition to all that, I need it to track vacation and personal time used somehow, and be able to export it into a report that can be by status, lead, employee, amount/percentage remaining, or any combination thereof. You'll need a separate table of vacation taken (with fields like EmployeeID, VacStartDate and VacEndDate). The report can be based on a Query joining the employee table to itself by Lead (to get the supervisor's name), with totals operations to get amount or percentage remaining, etc. Is all of this possible? Man, I hope so.... Yep. Not trivial but certainly possible! Thanks again, Phelonia John W. Vinson wrote: First of all, this is the first time I have tried using Access, let alone trying to build a database from scratch, so if I've made a ton of mistakes, [quoted text clipped - 16 lines] Phelonia. DLookUp is probably not the best tool for this task: it's inefficient and may not be as flexible as you would like. A Query can do the calculation for you, probably better! However, to help you with the query would require that you post some information about the structure of your tables and where the data can be found. That said... you're misusing DLookup. The Domain functions (DLookUp, DCount, DSum etc.) all take three String arguments. The first argument is the name of the value to be looked up; the second argument is the name of a Table or Query from which it should be looked up; and the third argument (which is optional) is a valid SQL WHERE clause criterion specifying which record should be looked up. Again, not knowing how you are storing the data, I can't suggest the correct DLookUp for your case - but as I say, it's probably not the right tool anyway! More info please? -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|