Queries :: When Date Null Return Today's Date

Aug 8, 2013

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 Replies


Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

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

Between [StartDate:] And [EndDate:]


Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Queries :: Pull Between Today's Date Minus A Week?

Feb 21, 2014

I am trying to pull between the current date minus a week in my Access query and I do not know what formula to use.

View 8 Replies View Related

Modules & VBA :: Find Date Which Is 6 Months Back From Today Date

Jan 27, 2015

I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.

View 1 Replies View Related

Today Or Before Today Date Criteria

Feb 23, 2007

what do you put in the criteria in a date field to show records with a date = to today or less?

View 3 Replies View Related

Queries :: Return All Records With Last / Max Date

Apr 10, 2013

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.

View 1 Replies View Related

Queries :: Extract Day From The Date And Return A Value

Feb 20, 2014

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??

View 3 Replies View Related

Queries :: Return Max Date Of Multiple Columns

Jul 28, 2014

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.

View 6 Replies View Related

Queries :: Return Selected Date Plus Next Three Days

Mar 22, 2015

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.

View 7 Replies View Related

Queries :: How To Lookup Date And Return Value From Another Table

Jul 30, 2015

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 ..

View 4 Replies View Related

Queries :: Return Entire Rows With Min Date Only

Apr 15, 2015

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.

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

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

View 13 Replies View Related

Queries :: Return Week Number On The Basis Of Date

Jan 23, 2014

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:


but the I get a result of 53 in this query, when I expected/need it to be 1.

View 4 Replies View Related

Queries :: Return Date Of Next Monday - Routine Not Working

May 13, 2014

Today is 13 May and this code is supposed to return the date of next Monday (19 May)


but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)

My begin week is Sunday (1) ,

View 8 Replies View Related

Queries :: Convert EST To BST - Using SWITCH To Return A Date / Time

Nov 14, 2014

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

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),


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?...)

View 1 Replies View Related

Look Up Today's Date?

Aug 15, 2005


Is there anyway to retrieve the value of today's date?


View 1 Replies View Related

Today's Date

Mar 14, 2005

Hi I have a form that I use to capture information. The "DateReceived" field prefills with todays date. I also have a "DateResolved" field that I would like to prefill with the current date, however that date would be different from the Date Received date. The reason for this is because user logs information then goes back into the form and closes the case by entering a Date Resolved. Thank you

View 3 Replies View Related

Age Using Date Of Birth And Today.

Jan 28, 2005

Hi i know it is simple but can someone tell me how to work out an age of someone using their date of birth and the date now please. thanks i cannot remember the formula.

View 1 Replies View Related

Queries :: How Filter Date Is Null Using Check Box

Jan 13, 2014

I have form that user can filter the records and generate a report but I have difficult trying filter null date.

If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...

View 14 Replies View Related

Modules & VBA :: Set Datatype As Date / Time In The Form - Update Table With Null Date

Mar 12, 2014

I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.

I have set the datatype as Date/Time in the form for Date of Death.

Private Sub Update_DOD()
Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset
Dim DOD As String
rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection

[Code] ....

However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.

CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey

View 5 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

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.

View 7 Replies View Related

Automatically Update To Today's Date

Oct 24, 2006


I have a table which has a date field in it, where the default value is Now().

How do I get this field to automatically update in the table to "today's date"?



View 12 Replies View Related

Date Field Find Today

Aug 21, 2007

I have a date/time format field.

"8/21/2007 10:02:34 PM" is what my column shows.

What would I add to the criteria to the query to find all records added today?

at the end of the day my user needs to run a query that will show everything from today. Thanks.

View 5 Replies View Related

Having A Calendar Show Today's Date

May 4, 2005


I have placed a calender object in Access 2003 on a form of mine and would like it to show the current date. Is there a way to set a default? IF it is code, where does it go?

View 2 Replies View Related

Update All Records With Today's Date

Oct 11, 2006


I have a form that people have to fill in to report when someone is off sick.

The first notification they have is that the person is off sick - so they can only enter a start date on the form, and have to leave the end date blank

I want the end date to always be "today" - and to automatically update to "today" until an end date is entered by the user. To enter the end date, the user will go back to the original record where they put the start date in, and then enter the end date.

Any ideas... using date () will put today's date in, but then when I go in tomorrow, it will say yesterday's date...


View 4 Replies View Related

Queries :: Date Range Query And Null Records

Mar 5, 2014

I have two questions, both the same query.

I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?

This is what I have currently in my query

CO_resp_rcvd (date field)

Criteria: Between [Start Date] And [End Date]

(I need null values as well because there will be some if the CO has not responded yet but needs to)


This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?

Not sure how to combine it to work - the wrapper is a bus day function

This is what I have so far in the query

CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())

View 6 Replies View Related

Queries :: Date Range Query - Return Lines Where Field Is Blank In Table

Aug 15, 2013

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.

View 14 Replies View Related

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