Queries :: Using Working Days In Search Criteria

Apr 22, 2013

I have a query where I use the search criteria of =Date()+1.

However I really need this to add on a working day (i.e. excluding weekends).

Is there an easy way to do this?

View Replies


ADVERTISEMENT

Trouble Implementing The Regular Days And Sunday Working Days?

Dec 14, 2004

Hello everyone,

I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?

The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.

How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks

Tim

Below is the code i'm using for defining Regular days and Sunday Working Days:


Private Sub LogDate_AfterUpdate()
Dim rst As DAO.Recordset, rs As DAO.Recordset
Dim db As DAO.Database
Dim dteToday As Date, dtePrior As Date
Dim i As Integer

dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

rst.FindFirst "[HolidayDate] = #" & dteToday & "#"
'See if the date entered is a holiday
If rst.NoMatch Then
Holiday.Value = 0
Else
Holiday.Value = 1
End If
If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if
Dim strSQL As String 'it will be a regular day or a Sunday

strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"

'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
i = rs.RecordCount 'Count the number of days between the two dates

If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If

Else
Sunday.Value = 0
End If

DayofWeek.Value = Format(dteToday, "dddd")

End Sub

View 1 Replies View Related

Date Add Problem (calendar Days Vs Working Days)

Dec 4, 2006

I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!

View 8 Replies View Related

Date Add Problem (calendar Days Vs Working Days)

Dec 4, 2006

I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!

View 6 Replies View Related

Queries :: Using (days) Droplist As Criteria Against Daysdiff Expression?

May 27, 2013

I have a query that uses two criteria from a user input form. On the form, the user would input a customers last name and use a dropdown list (based on a table 30,60,90,120,365 days) to select how many days previous they want to search. Unfortunately there is something that I'm over looking and can't figure out.

Let's say I have multiple records that match the following criteria: Last name "James", days since contracted all less than 30 days. For some reason when I put in "James" as the last name in my form and select "30" from the droplist, it only lists the most current one which is zero days. Even if I select "60" from the droplist, it still only shows the zero day contract.

If I go back and select "90" from the droplist is brings up 3 - 3 day old contracts and my 1 zero day old contract. If I select 120, it picks up another contract that is 4 days old. If I select 365 it picks up a couple more, but still not everything. Here is my SQL code.

Code:
SELECT tblAllData.[F&I Manager], tblAllData.[Reviewing BOA], tblAllData.Date,
tblAllData.[Stock#], tblAllData.[Deal Date], tblAllData.[Cust First Name], tblAllData.[Cust Last Name],
Round(DateDiff("d",[deal date],Now()))/1 AS Days, tblAllData.[Bank Name],

[Code] ....

View 2 Replies View Related

Calculate Working Days Between 2 Days

Mar 14, 2006

Dear All,

I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.

Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.

'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
'xxxxxxxxxxxxxxxxxxxxxxxx

Can someone please direct me to the right path.

Your help is highly appreciated.

qwerty70

View 1 Replies View Related

Queries :: Passing From Form Criteria Is Not Working

Feb 2, 2014

This is my passing criteria to query field:

IIf([Forms]![FrmUserSelection]![PricingType]=3,1 Or 2,[Forms]![FrmUserSelection]![PricingType])

My field is Pricing Type and holds records 1 or 2..I would like to pass value from my group option from a form.It works fine if group option has a value 1 or 2 but it displays no records if option value is 3 What is wrong with my code.

View 4 Replies View Related

Queries :: Criteria Referencing Control On Form Not Working

Oct 10, 2013

I have a form (named frmAddSession) with a combo box (named Band_Name) and lets say i have it displaying the band "Band A"

I have a query where the criteria is [Forms]![frmAddSession]![Band_Name]. when i run the query the results are blank. but if i type ="Band A" it works just fine.

why won't my reference to the combo box on the form work?

In a series of deductions to try and figure this out, my form only has the one combo box, and my query is based on only one table and only has the one field.

View 5 Replies View Related

Queries :: Counting Days Between Dates - Vacation Period And Work Days

Sep 11, 2014

I have a form called subfrm_vactions

rowsource is a query called qryVacations

the query should calculated two things

1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)

2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)

the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want

simply i want to calculate the work days.

View 5 Replies View Related

Queries :: Number Of Days Between Two Dates With Irregular Days Worked

Apr 14, 2014

I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.

I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.

View 1 Replies View Related

Queries :: Missing Days - Creating Table With 31 Days Of Zero Values

Apr 3, 2013

Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.

I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?

View 4 Replies View Related

Working Days

May 10, 2005

Can anyone help
I have one field that you enter a start date
the next field is the start date that adds 28 days to give a due date
but this includes saturdays and sundays which i dont want i only want the working week/day.
Please help

View 3 Replies View Related

Working Days

May 22, 2005

In my queries i have two fields

1st Field is called Termination Date
2nd Field is called Due Date:
3rd Field is called Date Completed:
4th Field is called (+/-):

Termination Date = I enter a date such as 20/05/05
Due Date = Automatically fills in using the expression below 17/06/05
The Due Date has an expression builder i.e.
Termination Notice-Due Date: [Termination Date]+28

Date Completed = I enter a data such as 18/06/05
(+/-) = Automatically fills in with -1

The problem is that Termination Notice-Due Date Automatically filled in using the above expression but it counts saturdays and sundays i dont want saturdays and sundays counted in the +28

Can this be done or iam i going nuts

please see attached this might help


Hazel

View 4 Replies View Related

Working Days

May 16, 2007

I recently ran into a problem calculating working days, i now have it working however i have a couple of other issues that i would like some assistance with.
I am looking to introduce a seperate field which would say eitehr YEs or NO if the entry is more than 5 days old. SO i know how many are over 5 days. But whenever i use > 5 i get Data type mismatch. How do i get round this. :mad:

View 1 Replies View Related

Add Only Working Days

Nov 16, 2005

Help please, im using this code

Private Sub Target_Combo_AfterUpdate()
Select Case Target_Combo
Case "Emergency"
Me.TARGET_COMPLETION_DATE = Me.ISSUE_DATE
Case "3 Days"
Me.TARGET_COMPLETION_DATE = DateAdd("d", 3, Me.ISSUE_DATE)
Case "3 Weeks"
Me.TARGET_COMPLETION_DATE = DateAdd("d", 21, Me.ISSUE_DATE)
Case "28 Days"
Me.TARGET_COMPLETION_DATE = DateAdd("d", 28, Me.ISSUE_DATE)
Case "2 Months"
Me.TARGET_COMPLETION_DATE = DateAdd("m", 2, Me.ISSUE_DATE)
Case "4 Months"
Me.TARGET_COMPLETION_DATE = DateAdd("m", 4, Me.ISSUE_DATE)
Case "6 Months"
Me.TARGET_COMPLETION_DATE = DateAdd("m", 6, Me.ISSUE_DATE)
End Select
End Sub




But i want it to add only working days (monday to friday) so if i added 3 working days on to fridays date then the date would be for wednesday

View 2 Replies View Related

Search For Queries That Use Specific Criteria

Jun 6, 2005

Hi,

Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.

Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.

I hope my explanation is clear.
Thanks upfront for any suggestions!

BJS

View 4 Replies View Related

Queries :: 2 Search Criteria On Same Field

May 14, 2013

I need to be able to print records for certain students showing their best and worst results for each exercise. Must be printed in portrait A4. Each student must be on a new page. I am trying to create a query which will find the results for studentID AA111 and DS1119. For each student:

- Find the best and worst RepsOrTime for each exercise
- Display studentID, StudentSurname, StudentForename, ExerciseID, Description, Best and worst RepsOrTime
- Name best records "best"
- Name worst records "worst"

Please see attached document.

I can find the result for one student id but when i try putting both student ids in it returns no result, also, I do not know how to rename best records best and worst records worst.For this i have two headings shown as RepsOrTime and it shows the max and min value for each exercise.

View 12 Replies View Related

Queries :: Multi Criteria Search

Aug 12, 2015

I have an events database with a description field which I would like to be able to search for multi criteria which would be determined by the user. For example, the user might want to search for all events which are Merchant Navy, Dieppe and Vimy related or they may want to search for only Veterans Week related events.

I've tried creating a form with text boxes that the user would put the criteria in and then basing the query on those text boxes using a like statement.My problem is that I don't know how many criteria the user will be using so I can have too many or too few text boxes. If too few, the user is not getting all of the records that they are looking for. If too many, the user ends up getting all the records in the database as the system uses the bank boxes to bring back all values, so basically all records.

is there anyway that I can have only one text box where users would enter in as many or as few crietia words seperated either by a , ; space etc., and the system would return all records which contain any of those words.

View 1 Replies View Related

Working Days In A Month

Feb 16, 2006

Does any one know how to calculate how many working days there are in a month
IE
Total number of days in a month minus Saturdays and Sundays

All contributions gratefully received

Thanks

View 5 Replies View Related

How To Get Working Days In The Query?

Nov 1, 2006

Hi, i have a problem
I need to create a query that shows me only days which r:
1. working days
2. which r not in the table on which the query is based
Thanks in advance

View 1 Replies View Related

Calculate Working Days

May 11, 2007

I hope you can help. I have a database and I am trying to work out the working days between the start date and the end date of a programme. This will not take into account any weekends or bank holidays. I have created a separate table called tblHolidays which has a list of the Bank Holidays. The majority of it works but out of 1700 records 50 don’t. These are ones in which it starts on 1 month finished on another or has a bank holiday. It works with certain bank holidays but not all of them. Bank holidays go back to the beginning of 2006
The following is the code I have used:
Public Function WorkingDays2(Start_Date As Date, End_Date As Date) As Integer

On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

Start_Date = Start_Date + 1

intCount = 0

Do While Start_Date <= End_Date

rst.FindFirst "[HolidayDate] = #" & Start_Date & "#"
If Weekday(Start_Date) <> vbSunday And Weekday(Start_Date) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

Start_Date = Start_Date + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

I then used the SQL

Select WorkingDays2 ([start_Date], [End_Date]) As CountDays

This is driving me crazy!!:mad:

View 12 Replies View Related

DatDiff - Working Days Only?

Sep 20, 2007

Hi,

is there any alternative of the DatDiff function, which counts only working days between two given dates?

Regards,
Diana

View 6 Replies View Related

Count Working Days Between 2 Dates

Nov 5, 2007

I've had it before, but can't find it.

basically something like this --

DateDiff("w", StartingDate, EndingDate)


that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?

View 1 Replies View Related

Working Days Expression In My Form

Jul 21, 2005

Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.

Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?

=DateDiff("d",[Date Query Raised],[Date Query Resolved])



Macro

IIf([Query Duration]<="28","Yes","No")

View 1 Replies View Related

Working Days Calculation On Form

Jun 27, 2006

Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !

I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.

I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.

Suggestions please !

Steve



Private Sub cmdDate_Click()
Dim FirstDate As Date ' Declare variables.
Dim Number As Integer
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim lnginterimdate As Long
Dim intdays As Integer

Dim Msg
FirstDate = InputBox("Enter a date - dd/mm/yy")
Number = 20

dtinterimday = FirstDate
intdays = Number
Do Until intcount = Abs(intdays)

If intdays &gt; 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1

lnginterimdate = dtinterimday

If WeekDay(dtinterimday, 2) &lt;&gt; 6 And WeekDay(dtinterimday, 2) &lt;&gt; 7 Then

intcount = intcount + 1
End If
End If

Loop


Msg = "New date: " & dtinterimday
MsgBox Msg
End Sub

View 5 Replies View Related

Working Days Query Problem

Jan 11, 2005

Date Recieved: Date letter in

SLA: combo box: 7 days
15 days
20 days

SLA Date: Date recieved plus 7, 15 or 20 working days.

Any ideas on how I would do this?

View 2 Replies View Related







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