I've attached a stripped down version of a small order database I'm working on.
A user would enter an order, the amount and the date the order is required by.
As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.
What I'm trying to build are two queries:
1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)
2. A totals query which shows the total order amounts by season
I am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.
I want to get dsum of pamount specific for each client with date criteria for example if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I'm working on a query ("Target Date of Completion") that takes the initial date started (from Step 1 of date started) and adds the EC (Estimated Completion) which is just shown in days. This will give an EST (Estimated Start time) for the next step in date format, which I would need the new column. Also, As you can see, the piece parts all have a different amount of steps, so this calculation would need to know when it's a different part.
I have a db tracking vacation times for staff. One of the fields tracks a members vacation start and end dates. I need a way to pull a report to see who is on vacation based on Date() (today).
For example:
A record for John Doe has him start vacation 08/19/2014 and end vacation on 8/28/2014. If I wanted a report that shows who is on vacation today, 08/25/2014, using the date() function), how would I do this?
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.
Code:
Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working. Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
I have a text box named "scheduled_start" on my "JobsF" form.
Now what I'm trying to do is use two append query's to move the start date contained within the text box.
*The first append query will be on the before update and put this date in the "notesT" in the "old_date" field. *The second append query will be on the After update and put this date in the "notesT" in the "New_date" field.
I'm trying to track date changes and this part of the process isn't working. For a start I don't know how to run the query on an event. I can open the query using 'openquery' but I don't wish to open it. Re query is also an option but the query I made isn't listed when that's selected.
I manage training for a large organization and am able to get a data dump that contains class end dates and class hours. Based off of those two, is it possible to determine a start date (excluding non-business days)?
For example, a 2 day class would be 16 'Hours' with an 'End Date' of Friday the 15th. Is there a formula that would give me Thursday the 14th as a 'Start Date?'
More importantly, if 'Hours' is 80 and the 'End Date' Friday the 15th, would it be able to give me a 'Start Date' of Monday the 4th? Can Access account for holidays?
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Need sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
This makes two prompts pop up one at a time. I was wondering if there was a way to combine both start and end date boxes in one prompt so I can see what dates I decide to type side by side. Is this possible?
i am trying to run a query from a form which will bring up the no of days difference between the start and end date also on the same form. The query doesn't bring back any results can someone please guide in what i am doing wrong. Here is the query SELECT DateDiff('d',[start date],[end date]) AS [no of days] FROM [booked property] WHERE ((([booked property]![start date])=[forms]![booking]![booked property]![start date]) AND (([booked property]![end date])=[forms]![booking]![booked property]![end date]));
I have a form which has a start date field & end date field i have a query that works out how many days from start date to end date that excludes weekends now my boss wants it to exclude any holidays we may have such as bank holidays etc can any help me with this i think i need a module and a new table with all the holidays in it i am right or iam i going crazy?
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
I’m looking for some help being able to let the end user enter there own dates. I currently have it set up in a query like this “[Please enter date]”. I would like to let them be able to enter a start date and an end date. Can anyone help me with this one?
How would you make two prompt boxes which ask for the start and end date so you can select all the data between the two dates...
[Start Date?] And [End Date?]
E.g., 01/01/2006 And 07/01/2006 So I can select all the data between that week. I also need to use this for the remaining weeks in January and February. This is really simple...gah. Thanks guys.
I have a start date on a form. The fields data type is short date I need to create a validation rule so that when a user enters a date it can only be a saturday date which is entered.
I created a module to generate the correct date to begin manufacturing. There are two sets of criteria. If the job's prefinish is a certain code then manufacturing takes 7 days. If the job's door style is a certain code then manufacturing takes 5 to 6 days. Otherwise the date range is 4. Prefinish takes precedence over door style. I have units that have the special prefinish and one of the door styles listed then it gets the door style days instead of 7 days. Any ideas?
Function CreateWOSDDate(ByVal PreFin As String, ByVal DrStyle As String, ByVal DelDate As Date) As Date
Dim intNumDays As Integer
Select Case PreFin
Case "BR17", "BR28", "WH06", "BR06", "BR11", "WH17", "BR00", "BR22" intNumDays = 7 Case Else intNumDays = 4 End Select
I have a Continuous form where I am trying to add a calculated feild that allows us to see how many days a client was in hospital. There is a start and end date. I want to see the following: If start date and end date are the same, this should show client in hospital for 1 day, not the default of 0. If start date 5/1/13 and end date is 5/3/13 it should show 3 days in hospital. If start date is 5/1/13 and there is no end date, this should show 7 dates (from start date to today's date or current date). Currently I have
I am trying to set a date filter that filters between two dates (Start/End) after I have selected filters from other combo boxes. This is what I Have so far and is a bit of a mess.
I have two text boxes - txtStartDate and txtEndDate
If Nz(Me.txtStartDate.Text) = "" Then Me.Form.Filter = "" Me.FilterOn = False ElseIf Me.cboCity.ListIndex <> -1 Then Me.Form.Filter = Me.Form.Filter = "NextCallDate
[Code] .....
So the idea is I will filter all records by my name then by status then by city and then by start date and end date.
