Ok, so I'm sure this is a pretty simple criteria, but I just can't seem to get the syntax right.
I'm trying to allow my user to select any start date and the query will return that date plus the next three days.
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"
Neither of which work ....
In a query i have setup, i work out when a tenant is next due to pay their rent.
Looks like this
Tenant Last Payment Date Payment Terms Next Payment Date
User1 01/07/13 Monthly 01/08/13
User2 01/07/13 Weekly 07/07/13
The next payment date is calculated using an IF statement and DateAdd in the query, so if it is weekly it adds 7 days, if it is monthly it adds 1 month.
What i am trying to do is write sub query of this one which shows which tenants are due to pay within the next 7 days.
But the access query seems to ignore the criteria I'm putting in....in the next payment date criteria i have tried specifying >Now() + 7 or >Date() +7 but neither seem to work, it just shows up every record...
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
View 1 Replies View RelatedI have a large database filled with customer records. Some customers come one time. Other customers come 50 times at year. I want to find all customers that have records that are less than 30 days apart so I can ultimately see the types of products they buy. How do I write the query?
I attached a picture of a sample database. The 30 day (+/-) field doesn't exist but I would like the query results to build it.
I'm trying to build a query that adds up the total sales for a given product per date. The problem I'm running into is some products have $0 sales on a given day and don't appear in the table I'm querying. How would I get the query to add a record for that day for the given product with 0 in the sales field?
To give more details, I currently have one table showing the sales data. It has a record for every sale that includes the product of the sale, the amount, and the date. In the query, I'm summing the sales for a given day and product. Then I plan to export to Excel where I will run additional analysis.
If there are no sales for the product in a day, I'd like it to list the date and product with a $0 in the sales column.
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.
View 7 Replies View RelatedI have a query call courses and field name expiry date I have add additional field within the query to show how many day left and how many days over the expiry date
Code : DayLeft:[expirydate]-now()
but i just get a error when i run the query
I also would like to ask is this possible to be done within the table e.g adding another field dayleft and some how add formula to calculate number days left or over due .
I'm using the following function to calculate date diff in network days (excluding weekends)
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0
[Code] ....
So when using this function in a query to get number of networkdays between 2 date columns ...it works fine but throws a #Error where there is blank entries in either of 2 date columns...
I need to find a way to display Null instead of #Error...
I have tried this expression but no luck...
IIf(IsError(NetWorkdays([Date1],[Date2])),"",NetWorkdays([Date1],[Date2]))
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!!!
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!!!
I'm running a query that has a field where I need two criteria show up (regular collections and special collections). Another field I have selects for the date (10/1/2014). The last field is the sum of collections. My problem is that the query is generating regular collections and special collections with each date and a sum of collections. I need the total sum of both collections for the date which would be 4.
AGENCY_CODELINE_ITEMPCA_DATESumOfCOLLECTIONS/ACCOUNTSxxxRegular Collections10/1/20142xxxxspecial collections10/1/20142
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C
123456 AAAA 1/8/13
123456 BBBBI 1/8/13
123456 CCCC 1/8/13
123456 DDDD 1/8/13
123456 EEEEEE 3/10/13
123456 FFFFFF 3/10/13
123456 GGGG 3/10/13
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
I have tried the max & last functions, yet no success.
I have a list of dates on which tasks were performed.
I want to be able to see if there is a pattern, i.e. lots done on a Monday
Can I extract the Day from the date, and return a value Monday, Tuesday, Wednesday etc??
How do you return the most recent date of multiple columns.
I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.
The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.
We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that works?
Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.
I've managed to do it in SQL using GREATEST() but that isn't an option in Access.
Basically, i have a table ("Transaction") with payment date and another table ("Control") with accounting dates and corresponding year/month.
Objective: I need to know which accounting year or month these payment date fall under.
Example: If the payment date is 18 Dec 2013, the accounting year should read as 2013 and the accounting month should read as 12.
In excel, this is very simple using vlookup.
I tried for hours using access dlookup query and i'm still stucked ..
I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.
To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.
Code:
EventsTable
```````````
EpisodeID | EventID | EventDate
-------------+---------------+-------------
1 | 001 | 01/02/2010
1 | 023 | 05/10/2009
1 | 103 | 12/02/2010
2 | 004 | 02/03/2013
2 | 102 | 12/10/2014
3 | 546 | 04/05/2012
3 | 100 | 08/08/2013
3 | 034 | 10/10/2012
3 | 066 | 02/03/2013
4 | 777 | 05/07/2014
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
Code:
ExpectedOutput
``````````````
EpisodeID | EventID | EventDate
-------------+-------------------+---------------
1 | 023 | 05/10/2009
2 | 004 | 02/03/2013
3 | 546 | 04/05/2012
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
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.
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.
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?
I've been trying to create a query that will take a date and return the week number of the date.
My original date is formatted m/d/yyyy
I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year
I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)
but the I get a result of 53 in this query, when I expected/need it to be 1.
Today is 13 May and this code is supposed to return the date of next Monday (19 May)
IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))
but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)
My begin week is Sunday (1) ,
Trying to import some data from a linked Excel spreadsheet into a local table. One of the fields is a Date/Time type and is recorded in EST (Eastern Standard Time). I want to keep this field for posterity but also add a separate field with the corresponding time as per BST
For clarity, daylight savings time comes into effect this year on 26th Oct in the UK and 2nd Nov in the US. So generally, there is a 5 hour difference between the two time zones, apart from the period between these two dates, when it is only 4 hours.Here is my query - I am using a SWITCH function to create the BST field
Code:
INSERT INTO tblTransactions
SELECT ltbPayments.ID AS Reference, ltbPayments.VALUEDATE AS ValueDate, ltbPayments.LOCALAMOUNT AS Amount, ltbPayments.USDAMOUNT AS AmountUSD, tblAccounts.AccountID AS AccountID, ltbPayments.TRANSACTIONTIME AS TransactionTimeEST,
SWITCH(DateValue(ltbPayments.TRANSACTIONTIME) < DateSerial(2014,10,26) Or DateValue(ltbPayments.TRANSACTIONTIME) >= DateSerial(2014,11,2),
[code]....
So - how do I explicitly specify the output of the SWITCH function to be in Date/Time format (I presume, by default, it's returning Text, which contradicts the table properties of tblTransactions & the TransactionTimeBST field?...)
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM