New User, having problems with DLookup

[Phelonia]

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.

 

[John W. Vinson]

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?

 

[Phelonia]

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

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]

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

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?