Extracting records from a table using a date

[Guest]

Is it possible to extract records from a table that are closest to a date
entered on a form? For example, the table I have is shown below (although
I've not shown all the fields) and I want to extract the records immediately
before the user defined date (let's say 22/01/2006)

IssueID ActivityID IssueNo Date
1 1 1 19/01/2006
2 1 1 20/01/2006
3 1 2 20/01/2006
4 1 2 25/02/2006

The result I'm looking for is for the middle two records to be extracted
i.e. because their dates are the highest before 22/01/2006 for their IssueNo.


I hope this makes and that someone can throw some light on this.

 

[Tom Ellison]

Dear PRH:

The dates in the table, if they are in the date/time datatype, are no
problem.

For use in a query, the string in your control is a problem. For use in the
query, the date in string form must be MM/DD/YYYY. Yours are DD/MM/YYYY.

You could write a function that changes the date strings from DD/MM/YYYY
into the date/type datatype:

Public Function DDMMYY(InDate As String) As Date
Dim MyDate As String, N1 As Integer, N2 As Integer

N1 = InStr(InDate, "/")
N2 = InStr(N1 + 1, InDate, "/")
MyDate = Mid(InDate, N1 + 1, N2 - N1 - 1) & "/" & Left(InDate, N1 - 1) &
"/" & Mid(InDate, N2 + 1)
DDMMYY = CDate(MyDate)
End Function

Does this work in your query?

Tom Ellison

 

[Vincent Johns]

Here's one way to do that. (Sorry, on my computer it's easier to show
dates in mm/dd/yyyy format, but the idea is the same.)

[Issue] Table Datasheet View:

IssueID ActivityID IssueNo Date
------- ---------- ------- ---------
1 1 1 1/19/2006
2 1 1 1/20/2006
3 1 2 1/20/2006
4 1 2 2/25/2006

First, calculate the date you want to display.

[Q_MaxDate] SQL:

SELECT Max(Issue.Date) AS MaxOfDate
FROM Issue
WHERE (((Issue.Date)<[User defined date]));

If you enter 1/22/06 for [User defined date], you'll get this result:

[Q_MaxDate] Query Datasheet View:

MaxOfDate
---------
1/20/2006

Then you can use the date returned by this Query as the basis for
another Query.

[Q_ClosestDates] SQL:

SELECT Issue.*
FROM Issue INNER JOIN Q_MaxDate
ON Issue.Date = Q_MaxDate.MaxOfDate
ORDER BY Issue.IssueID;

.... and this returns the records that I think you want to see.

[Q_ClosestDates] Query Datasheet View:

IssueID ActivityID IssueNo Date
------- ---------- ------- ----
2 1 1 1/20/2006
3 1 2 1/20/2006

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

You should not use Date as a field name. It is an Access reserved word and
can cause problems. If you have used a reserved word as a field name, you
will have to surround it with brackets as I have below:

SELECT TOP 1sometable.IssueId, sometable.ActivityId, sometable.IssueNo,
sometable.[Date]
FROM sometable
WHERE sometable.[Date] <= Date()
ORDER BY sometable.[Date] DESC;

You can modify this to put in the date you want to use where I have the
Date() function that will use the current date to do the selection. Note
this assumes sometable.[Date] is a DateTime data type.
Because I have the ORDER BY on the date in descending order, the most recent
date will be the first rows. Using the TOP 1 will return all rows that are
the closest date to the current date if there are no rows for the current
date.

 

[John Spencer]

Two query approach:

SELECT IssueNo, Max([Date]) as MaxDate
FROM YourTable
WHERE [Date] < #2006/02/25#

Save that as qMaxIssueDate
SELECT T.*
FROM YourTable as T
INNER JOIN qMaxIssueDate as Q
ON T.IssueNo = Q.IssueNo
AND T.[Date] = Q.MaxDate

Single query is possible in a couple of ways.
SELECT T.*
FROM YourTable as T
WHERE T.[Date] =
(SELECT Max(Temp.Date)
FROM YourTable As Temp
WHERE Temp.[Date] < #2006/02/25#
AND Temp.IssueNo = T.IssueNo)

 

[Guest]

Thanks very much for this.

I ran it as you suggested and it returned the records. However, if I change
the 'User Defined Date' to 26/02/2006 (i.e. higher than all the records) I
was hoping to get the following result:

2 1 1 1/20/2006
4 1 2 2/25/2006

But, it only returns 1 record (the one dated 25/2/2006)

Is there a way of dealing with this?

Thanks again.

Here's one way to do that. (Sorry, on my computer it's easier to show
dates in mm/dd/yyyy format, but the idea is the same.)

[Issue] Table Datasheet View:

IssueID ActivityID IssueNo Date
------- ---------- ------- ---------
1 1 1 1/19/2006
2 1 1 1/20/2006
3 1 2 1/20/2006
4 1 2 2/25/2006

First, calculate the date you want to display.

[Q_MaxDate] SQL:

SELECT Max(Issue.Date) AS MaxOfDate
FROM Issue
WHERE (((Issue.Date)<[User defined date]));

If you enter 1/22/06 for [User defined date], you'll get this result:

[Q_MaxDate] Query Datasheet View:

MaxOfDate
---------
1/20/2006

Then you can use the date returned by this Query as the basis for
another Query.

[Q_ClosestDates] SQL:

SELECT Issue.*
FROM Issue INNER JOIN Q_MaxDate
ON Issue.Date = Q_MaxDate.MaxOfDate
ORDER BY Issue.IssueID;

.... and this returns the records that I think you want to see.

[Q_ClosestDates] Query Datasheet View:

IssueID ActivityID IssueNo Date
------- ---------- ------- ----
2 1 1 1/20/2006
3 1 2 1/20/2006

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Is it possible to extract records from a table that are closest to a date
entered on a form? For example, the table I have is shown below (although
I've not shown all the fields) and I want to extract the records immediately
before the user defined date (let's say 22/01/2006)

IssueID ActivityID IssueNo Date
1 1 1 19/01/2006
2 1 1 20/01/2006
3 1 2 20/01/2006
4 1 2 25/02/2006

The result I'm looking for is for the middle two records to be extracted
i.e. because their dates are the highest before 22/01/2006 for their IssueNo.


I hope this makes and that someone can throw some light on this.

 

[Guest]

Tom:

The European short date format is not a problem provided the parameter is
declared as DATETIME in the query. The US short date format (or an otherwise
internationally unambiguous format) is only necessary in the case of date
literals so if an SQL statement is being built in code and includes date
literals the date must be formatted appropriately. The Format function will
do this very easily.

Up until Access 95 this requirement for date literals only applied to SQL in
Access. In code the local date format was respected. With 95 however the
requirement was extended to VBA in order to standardise it. Consequently its
also necessary on systems using a non-US date format to use the Format
function when doing things such as building a string expression as the
criterion for a DLookup function call etc.

Where the US short date format is the system short date format its not
actually necessary to format the value as it will be entered in US format
(visiting Yurpeans etc excepted!) but doing so regardless
'internationalises' the application.

One situation where you should not format the value as a date literal is
when setting the DefaultValue property of a control. This property is a
string expression regardless of the data type of the control's underlying
data, so if you want to set a date control's default value to the last
entered date in a form since it was opened you should use:

Me.MyDateControl.DefaultValue = """" & Me.MyDateControl & """"

in the form's AfterInsert event procedure. This will work regardless of the
local date format in use as it simply inserts the value from the control as a
string into the control for the new record. I have seen it recommended that
it be done with:

Me.MyDateControl.DefaultValue = "#" & Me.MyDateControl & "#"

That is wrong as on systems using European date formats it would change 4
July to 7 April!

If you don't delimit the value at all, then if the value in the control is
in short date format such as 15/03/2006 that would be interpreted as an
arithmetical expression evaluating to 0.00249252243270189 which is the value
Access stores for the date/time of 30 December 1899 00:03:35, so that would
be inserted into the control for the new record, which would surprise the
user somewhat! The same might happen with parameters in a query, which is
another reason why its important to declare parameters as DATETIME when
appropriate.

Ken Sheridan
Stafford, England

 

[Vincent Johns]

Well, my understanding was that you wanted only records with dates equal
to the latest date before the one you specified. How many dates do you
want? You could ask for "TOP 2" or "TOP 10%", or whatever number will
give you what you want. Warning: "TOP 2" may not give you two records.
Maybe your Table contains only one record, so one record is what
you'll get. Or maybe there are 16 records with the second-latest date,
so you'll get 17 records instead of two.

Also, maybe I should have used

(Issue.Date)<=[User defined date]

to allow returning records with the same date as what you specified,
rather than only records that are strictly earlier.

Klatuu (in another message) had a good point about the name of the
field. I don't have a problem with using brackets around a name like
[Date], and its being a reserved word (being used also as a function
name) is immaterial when you use brackets. But it's not very
descriptive -- date of what? I suggest you use a field name which
leaves as little doubt as possible about what data are in that field,
consistent with not letting it get so long it's a nuisance to type and read.

If you want to use a single Query, as John Spencer suggested (in another
message), that might be more efficient for Access to run, but I usually
prefer to break Queries up into pieces with names. I think that this
makes them easier to write, to understand, and if necessary to debug
(for which I use Query Design View pretty heavily). After they're
working, you can copy the SQL from the inner Query into the SQL of the
other one, and the result ought to work OK, but having done that you'll
no longer have access to the nice graphic Query Design View for that
inner Query.

Thanks very much for this.

I ran it as you suggested and it returned the records. However, if I change
the 'User Defined Date' to 26/02/2006 (i.e. higher than all the records) I
was hoping to get the following result:

2 1 1 1/20/2006
4 1 2 2/25/2006

But, it only returns 1 record (the one dated 25/2/2006)

Is there a way of dealing with this?

Thanks again.

:

Here's one way to do that. (Sorry, on my computer it's easier to show
dates in mm/dd/yyyy format, but the idea is the same.)

[Issue] Table Datasheet View:

IssueID ActivityID IssueNo Date
------- ---------- ------- ---------
1 1 1 1/19/2006
2 1 1 1/20/2006
3 1 2 1/20/2006
4 1 2 2/25/2006

First, calculate the date you want to display.

[Q_MaxDate] SQL:

SELECT Max(Issue.Date) AS MaxOfDate
FROM Issue
WHERE (((Issue.Date)<[User defined date]));

If you enter 1/22/06 for [User defined date], you'll get this result:

[Q_MaxDate] Query Datasheet View:

MaxOfDate
---------
1/20/2006

Then you can use the date returned by this Query as the basis for
another Query.

[Q_ClosestDates] SQL:

SELECT Issue.*
FROM Issue INNER JOIN Q_MaxDate
ON Issue.Date = Q_MaxDate.MaxOfDate
ORDER BY Issue.IssueID;

.... and this returns the records that I think you want to see.

[Q_ClosestDates] Query Datasheet View:

IssueID ActivityID IssueNo Date
------- ---------- ------- ----
2 1 1 1/20/2006
3 1 2 1/20/2006

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

PRH wrote:

Is it possible to extract records from a table that are closest to a date
entered on a form? For example, the table I have is shown below (although
I've not shown all the fields) and I want to extract the records immediately
before the user defined date (let's say 22/01/2006)

IssueID ActivityID IssueNo Date
1 1 1 19/01/2006
2 1 1 20/01/2006
3 1 2 20/01/2006
4 1 2 25/02/2006

The result I'm looking for is for the middle two records to be extracted
i.e. because their dates are the highest before 22/01/2006 for their IssueNo.


I hope this makes and that someone can throw some light on this.