Exclude date range in table Results

Hi all,

Need some help here please:

Personal tbl contain, among others, a StaffNum field
Career tbl contain, among others, a StartDate field
(Personal and Career is one-to-many on ID field)

Holiday tbl contain HolDate field listing all public holidays (I populate this)

I need a query listing all Staff (Personal) and the number of days they worked since their StartDate until today excluding any Holidays.

Shout if you need more info.


I have looked high and low over the internet for a way to do this, but can't seem to find any.

I have a form with 2comboboxes (popup calender) that takes a vacation date range (fromDate and toDate) for each employee (peopleId). This form is bound to a table (tblAbsense) with fields as peopleId, absenceDate, absenceType. After i click the submit button, I would like to make entries in my table for each date in the date range.

Eg. if i select fromDate - toDate: 9/14/06 - 9/21/06. I would like it to fill in each day absent, "only the weekdays" (excluding saturday and sunday) into my tblAbsence.
So, table should take entry like:

peopleId absenceDate absenceType
001 9/14/06 vacation
001 9/15/06 vacation
001 9/18/06 vacation
001 9/19/06 vacation
001 9/20/06 vacation
001 9/21/06 vacation

(notice i have excluded 16th and 17th in my table entries as those dates fall on weekend)?

This is what i'd like to achieve. Can anyone help me?

I have a vehicle reservation database and need a report that shows total reservation hours per day per vehicle based on a user entered date range excluding non-work hours and days. The thing is the table only records the Check Out Date/Time and Check In Date/Time. I need a query that can display all dates in that date range and break down all reservations that fall in that date range to display total hours per day per vehicle. Example of reservations in tblReservations:

VehicleID        CheckOutDate         CheckInDate
1                  1/4/10 10:00 AM      1/6/10 12:00 PM
2                  1/5/10 2:00 PM        1/5/10 4:00 PM
3                  1/6/10 4:00 PM        1/8/10 8:00 AM

So, if user entered date range of 1/4/10 to 1/6/10 the query needs to look at each entry and decide if any part of the reservation falls on 1/4/10, 1/5/10, and/or 1/6/10. I'm guessing it would need to start looking for reservations with a date of 1/4/10, find all those and move to next date until it loops through all the records for the last date, which in this case is 1/6/10. So the query should display, assuming work hours are 8:00 AM to 5:00 PM...

ReservedDate   VehicleID   HoursReserved
1/4/10                 1                  7
1/5/10                 1                  9
1/5/10                 2                  2
1/6/10                 1                  4
1/6/10                 3                  1

I currently have a query that displays total reserved hours excluding non-work hours and days, but it is for the whole reservation and does not break down into individual days. I have no idea how to set the query up to get this information, can someone please help me out.

Thank you!

I am using MS access via OBDC to a DB.

A column in a table contains dates, however they are in the format DD/MM/YYYY and DD/MM/YYYY HH:MM:SS.

I have a query (main) that calls other queries (sub), and the Main query requires the user to input a From and To date.

The results of the query exclude the records that have HH:MM:SS for the To date.

How can I ensure I retrieve the missing dates?

I have tried converting the date to text by the of the Day, Moth, Year function, and then converting it back with DateValue. ie

td: DateValue(Day([add_date]) & "/" & Month([add_date]) & "/" & Year([add_date]))

If I place the date range in the criteria field ie "between #01/01/2012# and #05/01/2012#", the query works fine and the results back are as expected. If I change the criteria to "between [From] and [To]" and then run the query and I put in 01/01/2012 for From and 05/01/2012 for To, I get all records with a value of DD equal to 01, 02, 03, 04, 05, and MM and YYYY every combination that is in the data ie 01,02,03,04,05,06,07,08,09,11,12 and 2012, 2011, 2010 etc

All the queries are built in the Design section and not in VB

I am at wits end


I found this code for Access 2007 and I cannot see any reason why it would not work in 2003, but of course, something is not right and I am sure it is my fault since I am not much of a programmer and have little knowledge about VBA.

I want to create a column in a query that takes a start date and an end date and calculates the difference but also factors out the weekends and Holidays. The code is suppossed to handle the weekends through a second function that factors out the weekends in the selected date range. It then looks at a Table I created that handles the Holidays.

When I enter my function "Working days: Workdays([Clay-Ref]![TrueSentToQADate],[Clay-Ref]![GSUQAComplDate],[Holidays])" in the Field row of the query I get a Undefined function 'Workdays' in expression error.

I have read through the code several times and cannot figure out where I am going wrong. Can anyone see what I am doing wrong. I am attaching the two functions and a picture of the Holiday table.

Workdays Function:

	Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday]

I have a form that contains a couple of combo boxes and a three text boxes.

The combo boxes pull their data from two different fields within one table but they are unbound. This is the Row Source for one of them -

SELECT [FAQs_table].[Department] FROM FAQs_table GROUP BY [FAQs_table].[Department]; )

The text boxes are also unbound and I use these to enter dates.

The idea is to use the combo boxes to select required data and then add a From date and a To date so that the search would return all the items chosen from the combo box between the dates specified.

The problem I have is getting it to work (obviously)

In the query I have this in the criteria of the combo boxes

Like "*" & [Forms]![Form1]![Department Combo] & "*"

Like "*" & [Forms]![Form1]![Products Combo] & "*"

This for the Keywords

Like "*" & [Forms]![Form1]![Keyword] & "*"

All this works fine and if I only choose data from one combo box it will return the results based on that selection only, if I choose data from two combo boxes etc, it will return the results that match the two choices.

Then I complicate things by adding the date search...

Currently I have this in the Criteria

Like "*" & [Forms]![Form1]![From] & [Forms]![Form1]![To] & "*"

and this in the Or filed

Between [Forms]![Form1]![From] And [Forms]![Form1]![To]

When I only use
Between [Forms]![Form1]![From] And [Forms]![Form1]![To] I have to input two dates or there will be no results and not every entry will have a date, so those that don't have dates will be excluded from the results.

When I only use
Like "*" & [Forms]![Form1]![From] & [Forms]![Form1]![To] & "*"

I might as well not have the date option as it will return all the records regardless of the date or if there is no date...

Could anyone help so that if I don't specify a date the search will continue and show all the results (regardless of date) that match the combo box/search box options and if I specify a date range the results shown will only be between these dates?

I will be happy to email the database if this would help. It is less than 200k (Access2000)

Thanks in advance.

Hi everyone,
I have a normalized table containing the dates and associated monthly performance of several investment funds. The date range is 07/01/1990 to 06/01/2010. Most funds have a performance track record that is less than the maximum 240 observations. Some investment funds also have gaps in their track record. I would like to create a query that selects funds with at least 60 continuous observations between 07/01/1995 and 06/01/2010 (i.e. cut off the first 60 obs + a minimum track record of 60). Asssuming the table is called Performance and the relevant fields are [ID] and [MM_DD_YYYY], I have come up with the following:

SELECT Performance.* FROM Performance WHERE (((Performance.MM_DD_YYYY)>=#7/1/1995# And (Performance.MM_DD_YYYY)= 60;

Sadly, this does not work because it would also include an investment fund, for example, with a history of 12/1/1990 to 12/1/1997. This fund should not be included since the track record of 60 must be between the specified earliest and latest date. Any help in this regard is greatly appreciated.

Hi all,

I've achieved this previously in Excel via VBA, however, I've been tasked with setting the same up in Access. Being 3 weeks new to Access and still learning I've hit my first major hurdle! Have been searching for about a week, as it must be possible to do this, I'm obviously not approaching it correctly.

Data below is just sample data, actual volumes involved are into 00's and 000's each month.

I have a table of dates with the first of each month recorded, along with total counts of members (MemberType1 and MemberType2), so I know how many members were present historically:

(MemberType is a sum of all Members within that type - we have multiple types.)

Datenum # MemberType ...
01/01/2008 # 2
01/02/2008 # 4
01/03/2008 # 4

This table updates / appends new data every month (if no date is present, it appends totals, if date is present, it updates).

I have a second table which is a record of when each Member has had an issue (IssueType is so I can exclude certain types later on):

Date # MemberID # IssueType
01/01/2008 # Member1 # Issue1
05/01/2008 # Member2 # Issue1
16/01/2008 # Member1 # Issue1
03/02/2008 # Member1 # Issue2
04/02/2008 # Member3 # Issue2
22/02/2008 # Member3 # Issue3
22/02/2008 # Member4 # Issue1
05/03/2008 # Member2 # Issue4
15/03/2008 # Member2 # Issue1
01/04/2008 # Member3 # Issue2
09/04/2008 # Member2 # Issue2
21/04/2008 # Member2 # Issue1
04/05/2008 # Member2 # Issue3
17/05/2008 # Member3 # Issue2

I have a query that counts the total number of issues each member has each month, returning results as follows:

DateNum # MemberID # Count of MemberID
01/01/2008 # Member1 # 2
01/01/2008 # Member2 # 1
01/02/2008 # Member1 # 1
01/02/2008 # Member3 # 2
01/02/2008 # Member4 # 1
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 1
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 1
01/05/2008 # Member3 # 1

################# STUCK HERE #################
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvv

What I need to do is get the total number of issues each member has had over the previous three months for that month:

DateNum # MemberID # Sum of Count of MemberID(previous 3 months)
01/01/2008 # Member1 # 0
01/01/2008 # Member2 # 0
01/02/2008 # Member1 # 2
01/02/2008 # Member3 # 0
01/02/2008 # Member4 # 0
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 2
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 3
01/05/2008 # Member3 # 2

################# STUCK HERE #################

The idea is to then count the number of 0's, 1's, 2's and 3+'s each month (I can do this bit), apply a weighting, 0 = 100%, 1 = 66%, 2 = 33%, 3+ = 0%, and calculate a weighted percentage - hence the requirement for the totals to be recorded each month in the first table.

To give a query:
DateNum # 100% # 66% # 33% # 0%
01/01/2008 # 2 # 0 # 0 # 0
01/02/2008 # 3 # 0 # 1 # 0
01/03/2008 # 3 # 1 # 0 # 0
01/04/2008 # 2 # 2 # 0 # 0

100% actually calculated by (MemberType)-(sum of all issues in that month).

To give a final percentage for each month:
DateNum # % # Calculation
01/01/2008 # 100% # (2/2)
01/02/2008 # 83.25% # (3.33/4)
01/03/2008 # 91.5% # (3.66/4)
01/04/2008 # 83% # (3.32/4)

I've tried cross tab queries, but I can't sum the previous three months, as this impacts the next months calculation. Totally self taught, first post on any forum. All advice greatly appreciated.

I'm stuck on the bit highlighted above between vvvv and ^^^^

Apologies for length of post, I wasn't sure how much info to provide.


Having had a conversation with a highly regarded contact we came up with a concept that would/should cut out all queries surrounding dates in tables.

Months missing from crosstab queries
Conversion between DMY and MDY
Week ends or week days only
Bank/public holidays

What we came up with was a table (which I have had to chop the bottom half off, so it would be small enough to post.) This table consists of a wide variety of dimensions sourrounding a given date,

Julien number
date in DMY format
date in MDY format
Day of the week
is it a weekday/weekend
is it a known bank holiday
week day
short/long day
short/long month
short/long year

and many more.

The original table runs from 13/12/1899 to 31/12/2020

the pk is an autonumber which coincidently is the julien day number.

So how does it work?

Lets say you want to run a complicated crosstab report for any given year but only for weekdays and you want to exclude any holiday's that appear in that period.

First you introduce the table to your query and depending on whether your dates are formated dmy or mdy link the actual date in the table to the matching date in the table to be interrogated. If you put a right join on it so all dates from the master dates table appear even if they don't appear in the source table. You then drag down the field FKWeekday and set the where condition to 1 (Yes) drag down the FKBankHoliday Field and set the where condtion to 0 (No).

Finally you apply the date range to the FKDMYDate, FKMDYDate or PKDate field. I suggested the PKDate field because if you pass the parameters as numbers as opposed to actual dates it does not matter what format the date is in.

Make up you row and column headings to suit you needs but if you are using weeks or months for example you need to use the date in the master table as every date will be there. So no missing columns are encountered.

So now we run the query.

buy using this table and applying the relevant condtions there is no need to test for weekends/weekdays/holidays/etc because all that information is already known in the master dates table.

I have not put this to the test yet as I have only created it today. There are fields for financial years elements but have left these blank as you may have a different day one than me. As a developer you could populate them yourselves with the corrsponding data entities.

I will leave this thread open for any comments you may have.


I have an access db that has three tables, in two of the tables all my cities are listed that I need to reflect in my crosstab in crystal. However the remaining table only has one of the four cities listed in it.

Everything works correctly except when I want a grand total for all cities. It excludes the total for the table that only has one city and prints the rest.

If I go to the table where only one city is listed and add dummy records for the other three cities in a date range, all pulls correctly.

There must be a work around. Any ideas

I'm working on a database that collects production and time excluded data per employee. This information is gathered so that employees productivity is based on their available hours at work. If an employee's schedule is 7.5 regular hours and they submitted 2 hours of excluded time for a given that the productivity of that given day is based on their production input (points) and on 5.5 hours. I have two different tables for time excluded requests and production that are not related. They're both related to employee table (employeeID). I need to do a query that based on a date range it should show records from both tables for the same day. If employee did not input time excluded in a day to assume they're available hours are based upon their regular schedule. I've tried running a query combining both tables but I get duplicate values. Any guidance on how I should tackle this will be greatly appreciated.

Hello everyone,

I'm extremely new to all database stuff, so please pardon my ignorance with this question. I've googled about as much as I can trying to figure this out, and I'm hoping you can help here.

I am trying to set up a query that will pull records between two dates, based on month and date only, excluding the year. To explain, I need to query a list of records in my database based on the date associated with them, between 2 dates within one week (the dates change each week, between Sunday to Saturday).

I have had luck using the datepart function to find just the month asking it to prompt me for both months (if needed or I just input the same number if it's within the same month), and separately (without the month) with the datepart function to find just the date asking it to prompt me for the 2 dates, but I am unable to combine the 2 queries to pull 1 complete set of results results.

Also, these records have a many to many relationship with another table, and I find the records without information in the related field (in this case "relationship" field) are not included in the results when I include the "relationship" field , which I need, in the query.

All of this I'd like to set up into a form to view and copy info from each week.

My questions are:

First, is there a way to query the month and date only from a date field, using parameters to prompt me which month range and date range to search for, excluding the year?

Secondly, is there a way to ensure that all records, including those with no information in the 'relationship' field are displayed using the same query?

Here are the formulas I have had some luck with when used separately the other:

DatePart("m",[English DOD]) Between [month?] And [same/different month?]


DatePart("d",[English DOD]) Between [first date?] And [second date?]

Any information you can help with would be greatly appreciated. Thank you for reading!

Wishing you a peaceful weekend,

I was hoping you guys could assist me in figuring this little thing out. I have a form for work request submissions. Here's a highlight of the opportunity.
User opens form User selects a date range via a calendar (used the API calendar) User selects a recap schedule (Daily, Weekly, Bi-Weekly) Recap schedule written to schedule table
So, there's more to the form, but that's the quick and dirty of my issue. I need to figure out how to determine the dates that fall in the selected recap schedule based on the start and end date of the work request.

Also, I would like to be able to keep them from selecting Bi-Weekly (or Weekly) if the start and end date do not allow it. For example, 10/12/08 through 10/18/08 wouldn't allow for a Weekly or Bi-Weekly schedule selection. It would however allow for daily. So, the daily schedule would look like this...


These dates are because the 12th is a Sunday and the 18th is a Saturday. Recap reports are not run on weekends because no one is in the office. So, I also need to exclude all Sunday and Saturday dates from the schedule as well.

Oh, and one more complication to throw in the mix...if the schedule is weekly or bi-weekly, recaps are done on Tuesday's only.

Hopefully someone can give me some direction. My brain seems lazy today. Well...most days, for that matter.

Hoping for some assurance and assistance....

I am needing a database that will help to manage employee leave (holiday) requests. I have one but the current design is a bit...... well, you know.

Leave Limits are (presently) applied by one table:-
workType (twentysix work types some common to all sites, some apply only to one or two sites)
LimitSiteA (three sites)

So, several "faults" are immediately apparent:

LimitAllSites is ALWAYS the sum of LimitSiteA+LimitSiteB+LimitSiteC so
clearly shouldn't be there, and I don't even want to talk about that....

Also , no primary key, repeating columns... uh-oh!
limitID (PK)
payWeek (date/time - always a Wednesday, our payWeek begins on a Wednesday)
workTypeID (FK to new table tblWorkType)
siteID (FK to new table tblSite)
Limit (integer)

However....with just a brief investigation, it transpires that of the 1600+ rows in this (current version) table, there are only 69 unique sets of limits (ran a Unique Values query, excluding the PayWeek).

So, what I do want to talk about is not having to add in a table row for each WorkType for each PayWeek (for each site- with the improved design). I want to apply a general set of limits, with effectiveFrom and effectiveTo dates, per Site/workType combination, which should automatically populate out 80 weeks into the future (historical records are not required - it is just to help plan future leave) and then cater for the exceptions by "adjusting" the general limits in one of two ways:
1) every day over a date range (e.g. every day between 15NOV05 and 15DEC05, decrease the limit for worktype Z in Site A by 10 percent of the BASE* limit).
2) every nominated weekday over a date range (e.g. every Friday in December 2005, increase the limit for worktype Z in Site A by 20 percent of the BASE limit).

*the two adjustment types need to be independent, overlappable, and have a cumulative effect.

e.g. for the above, if the general limit for type Z of site A were 100
14 NOV --> limit 100 = 100 (no adjustment)
15 NOV --> limit 100-10 = 90 (adjustment per #1)
. - repeat concept as above
30 NOV --> limit 100-10 = 90 (adjustment per #1)
01 DEC --> limit 100-10 = 90 (adjustment per #1)
02 DEC --> (Friday) --> limit 100-10+20 = 110 (adjustment per #1 & #2)
03 DEC --> limit 100-10 = 90 (adjustment per #1)
. - repeat concept as above
15 DEC --> limit 100-10 = 90 (adjustment per #1)
16 DEC --> (Friday) --> limit 100+20 = 120 (adjustment per #2)
17 DEC --> limit 100 = 100 (no adjustment)

It gets dirtier !
There are two broad types of leave - general - for which the limit is determined/applied at the (site+workType) level, and "long service" for which the limit is to be applied at just the site level (workType has no bearing).

I was OK up until it got "dirtier" - now I am not so sure....

I was thinking...

tblLimit (base limits)
limitID (PK)
workTypeID (FK to new table tblWorkType)
siteID (FK to new table tblSite)
limit (integer)
effectiveFrom (date/time)
effectiveTo (date/time)

Does my base limit table store WorkTypeID, but as null when the record relates to "long service" leave, and as a valid WorkTypeID when the record relates to "general" leave ? If so, is it of sound design to derive the broad leave type from the "nullness" of the workTypeID, or is it recommended to store that separately?

Similar questions relate to the adjustment table, for both WorkTypeID and WeekdayID...

limitAdjustID (PK)
workTypeID (FK)
siteID (FK)
adjustFactor (double)
WeekdayID (FK)
effectiveFrom (date/time)
effectiveTo (date/time)

I was sort of hoping that writing this out may help clarify my mind on some of the points.... which it did, but only to a limited extent so....

Suggestions / corrections / Pointers ???



I really appreciate if someone could give me a correct sql. Here in my scenario. I have two tables
1. Harvest Details
Harvest Details contains our detailed harvest in our farm for our cultured shimps and it contains the Pond No, Crop No, Harvest Date and Harvest Type, there are only two two "Partial Harvest" and "Final Termination" of the pond since we cannot harvest our farm in one shot due to the size. 10 hectares per pond.

Sample Harvest Details Table:

Harvest Details
t_pcodt_cropt_htypHarvest DateHarvest Type03PC0112213/09/2010Termination03PC0112126/05/2010Partial03PC0112111/06/2010Partial03PC0112104/09/2010Partial03PC0112103/09/2010Partial

in every pond it wil vary, sometime you will find only one line becuase this pond is still in the state of Partial Harvest and sometimes only 1 Partial + Final Terminal or any possible scenario like 3 Partial+Termination and so on.

The second table is the Production Order table where we are recording the material consumed on each pond from the start of pond up to the final termination of the pond. The field Actual Partial total contains the totals of all Item Group but that belongs to different dates within the date range of Harvest Date in my first table "Harvest Details".
I just excluded the Closing Date in my qrySample because I used a GROUP BY SQL that will sum up all ActualPartial used material but there is a filtering for that date from and to behind this grid result.
qrySamplePondCropItem GroupItem DescItem Grp DescCurrencyActual Partial03PC0112258 FERTILIZER- UREA N46% "SABIFertilizerSAR100003PC0112322 MOLASSES (REFINED - CANE)Grow out Raw MaterialsSAR285003PC0112322 HYDRATED LIME - 20KG/BAGGrow out Raw MaterialsSAR300003PC0112322 DAP 18-46-0(DIAMMONIUM PHOSPHGrow out Raw MaterialsSAR003PC0112322 COMPOST(A-GRADE) ORGANICGrow out Raw MaterialsSAR003PC0112801 Standard Starter-3Feed Mill Finished Prod. ItemSAR2987503PC0112801 Standard Starter-1Feed Mill Finished Prod. ItemSAR003PC0112801 Standard Grower-1Feed Mill Finished Prod. ItemSAR40675003PC0112801 Natural FeedFeed Mill Finished Prod. ItemSAR0

Now i need an SQL Subquires that can filter total Actual Qty based on this result. From this result, they can identify how much total they have from the 1 first Partial Harvest down to the last final termination. The Partial Harvest and Final Termination should dynamically adjust based on the records in "Harvest Details". If the scenario is only 1 Partial and 1 Termination, the column should display only that 2 columns not up to 5 partial.


PondCropItem GroupItem Grp DescSub ItemItem DescPartial 1 Actual QtyPartial 2 Actual QtyPartial 3 Actual QtyPartial 4 Actual QtyPartial 5 Actual QtyTotal Actual Qty03PC0112258Fertilizer2580100001FERTILIZER- UREA N46% "SABI200000020003PC0112322Grow out Raw Materials3221101002COMPOST(A-GRADE) ORGANIC00000003PC0112322Grow out Raw Materials3221101003DAP 18-46-0(DIAMMONIUM PHOSPH00000003PC0112322Grow out Raw Materials3221101004HYDRATED LIME - 20KG/BAG602003400060003PC0112322Grow out Raw Materials3221301001MOLASSES (REFINED - CANE)570000057003PC0112801Feed Mill Finished Prod. Item FM010001Standard Starter-100000003PC0112801Feed Mill Finished Prod. Item FM010003Standard Starter-359750000597503PC0112801Feed Mill Finished Prod. Item FM020001Standard Grower-142200622532025008045003PC0112801Feed Mill Finished Prod. Item FM300000Natural Feed000000

I new to the forum and new to Access, so please forgive any transgression or lack of experience. I am constructing a db in Access 2007 for a local food pantry and am having a problem constructing a query that will return those records of clients who have picked up food in the last 30 days. The table has 13 fields (excluding name, ID) that provides the dates in successive order that the client was given food. (We can only give food once every 30 days.) I record these dates in 12 fields for the calendar months and a 13th field to bridge between years. I need to construct a query and/or report that only returns those clients that have gotten food in the past 30 days, so that if someone stops by, we can quickly check if he/she is eligible.
Thanks in advance for your help. How do I write an expression that does this?

I have a table with following fields:
Date,Dept,ename,option1,contractedhrs,workinghrs,h rs1,sickness etc.

in this,option1 stores either permanent or temporary,hrs1 stores total overtime in that day,sickness stores sickness hrs in that day.

I am writing the following query to display total contracted hours,total hoursworked excluding overtime and total hours worked including overtime between a specific date range:

	strSql = "SELECT [Dept],sum(Contractedhrs) as Contractedhours,sum(Total) as [Hoursworked Exc Overtime],sum(Total)+sum(hrs1)
as [Hours Worked including overtime] from tblteamleader where [Date]>=#" & Format(Combo0, "mm/dd/yyyy") & "# and [Date]

Dear All,

I need some help !

I have a table that stores a (percentage) score for each of 10 categories, for evaluations done on employees. The table also details the employee, and the date of the evaluation; such that an extract of the table would look like this:

EmployeeID (long Integer)
EvalDate (Date)
ScoreCategory01 (Single)
ScoreCategory02 (Single)
ScoreCategory03 (Single)
ScoreCategory10 (Single)

Most of the time the scores will be in the range 0 to 100. It is possible for a ScoreCategory to be "Not Applicable" (through no fault of the employee). I very cleverly (or otherwise) decided to store this result as minus one (-1).

Now I would like to report averages on those values, and exclude the -1's from the calculation. Trouble is I cannot exclude entire records just because one of the ScoreCategories in that record is at -1.

Excel has lovely functions "SUMIF" and "COUNTIF" that would make such averaging readily achievable. Is there an easy way to get what I want, (especially in a report) or will I have to spend hours writing and arguing with code

My (AC97) project is way too far gone to redisgn the table structure at this point.

Any help greatly appreciated.



I have a form with StartDate, 3 Comboboxes (Employee, Consumer, Service), and a button. What I'm struggling with is code to do the following:
User enters a start date (mandatory), and values in all of the comboboxes (mandatory), then hits the button. The subform then creates 7 records with those default values, with each record dated according to StartDate, where each record is a day of the week (sun-sat).

I've attached what I have currently, which is hardly anything - I can't figure out how to create the records on the click event. Also attached is the desired result after clicking Go given the values on the main form.

I also need to add a button to submit these records, which on a click will add the records to the table while deleting (or undoing) any records that do not have all field complete.

*note - A consumer can only have a particular service once per day, so there should never be duplicates of Consumer+Service+Date. I figured I could pretty easily add a field to the table that uses these values to create a uniqueID, then prevent duplicates, but I also need the form to display these values if within the range and values (excluding Employee) in the main form.

I have some code (from Stackoverflow) but I'm not sure of how to use the counter/calendar table it suggests, or what field "AddDate" is referencing.

	Private Sub Go_Click()
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sSDate As String
Dim sEDate As String

    sSDate = "#" & Format(Me.StartDate, "yyyy/mm/dd") & "#"
    sEDate = "#" & Format(Me.StartDate + 7, "yyyy/mm/dd") & "#"

    sSQL = "SELECT * FROM tblTimeCardHours WHERE DateWorked Between " & sSDate _
        & " AND " & sEDate

    Set rs = CurrentDb.OpenRecordset(sSQL)

    If rs.RecordCount < 7 Then
        AddRecords sSDate, sEDate
    End If

    Me.frmTimesheetEntrySubform.Form.RecordSource = sSQL
End Sub

Sub AddRecords(sSDate, sEDate)
''Uses counter table with integers from 0 to
''highest required number
''Another useful table is a calendat table, which would
''save some work here.

    sSQL = "INSERT INTO tblTimeCardHours (DateWorked) " _
        & "SELECT AddDate FROM " _
        & "(SELECT " & sSDate _
        & " + [tblTimeCardHours.TimeCardDetailID] AS AddDate " _
        & "FROM [tblTimeCardHours] " _
        & "WHERE " & sSDate _
        & "+ [tblTimeCardHours.TimeCardDetailID] Between " & sSDate _
        & " And " & sEDate & ") a " _
        & "WHERE AddDate NOT In (SELECT DateWorked FROM tblTimeCardHours)"

    CurrentDb.Execute sSQL, dbFailOnError
End Sub

I am looking for assistance with a query which will return all records from a table with a start time within a certain range. All attempts thus far have resulted in either all records being excluded, or an error message stating the expression is too complex.

I am attempting to use Access to pull all of the work activities from my employers scheduling database, then using individual queries select the activities scheduled to start between 6am and 6pm on a given day or 6pm one day and 6am the following day, for each day of the week. I have query criteria which will successfully return all activities on a given date, but cannot seem to develop a working select criteria to refine the results by time period.

The applicable field, EST (early start time), appears to be formatted as Short Time (I get this from the properties window for the column after adding this field to the query selections). I initially attempted to set my selection criteria to

Between #6:00:00# And #17:59:00#

but received an error message "The expression is typed incorrectly or is too complex to be evaluated". Changing the selection criteria to

>#6:00:00# And #6:00:00#

with no improvement. This occurs regardless whether I use the field directly from the table or try to ensure it is in the correct format using TimeValue([EST]). Finally I have an additional field in the same table which converts the time to a simple integer value from 1 through 23, and even attempting to determine whether the value of the number in this field is


has continued to return the same error. This seems like it should be a very simple solution but I am new to Access and trying to teach myself and the answer is escaping me. I appreciate any insights anyone can provide.


Not finding an answer? Try a Google search.