query using dates

[C White]

Hi

I am having problems with running a query that does the following

there are 5 fields in a table that the query is based on, the first four
are simple enough and all that happens is that the fields need to match,
for that I was able to do the following to get records out of the
database (does that make sense?)

In (SELECT [SERIAL_NUM] FROM [TABLE] As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)>1 And
[SR_SUB_AREA] = [TABLE].[SR_SUB_AREA] And [DETAIL] = [TABLE].[DETAIL]
And [SUB_DETAIL] = [TABLE].[SUB_DETAIL])

The fifth field is a date field that is formatted as follows:
"11/08/2004 10:06:04 PM"

what I am having problem with is getting the query to pull the records
that are within 2 days of eachother.

Simply put the query need to pull out the records where the first four
fields match and they were created within 2 days of eachother.

I'm probably using the wrong terms when I try to search for an
answer/tutorial on how to this as I have found nothing to date.

Any help, would be appreciated.

Thanks

[Allen Browne]

Answered in m.p.a.queries

[Salad]

C White wrote:

Hi

I am having problems with running a query that does the following

there are 5 fields in a table that the query is based on, the first four
are simple enough and all that happens is that the fields need to match,
for that I was able to do the following to get records out of the
database (does that make sense?)

In (SELECT [SERIAL_NUM] FROM [TABLE] As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)>1 And
[SR_SUB_AREA] = [TABLE].[SR_SUB_AREA] And [DETAIL] = [TABLE].[DETAIL]
And [SUB_DETAIL] = [TABLE].[SUB_DETAIL])

The fifth field is a date field that is formatted as follows:
"11/08/2004 10:06:04 PM"

what I am having problem with is getting the query to pull the records
that are within 2 days of eachother.

Simply put the query need to pull out the records where the first four
fields match and they were created within 2 days of eachother.

I'm probably using the wrong terms when I try to search for an
answer/tutorial on how to this as I have found nothing to date.

Any help, would be appreciated.

Thanks

You can use the DateDiff function.
DiffDays : Abs(DateDiff("d",Date1,Date2))
I used ABS() so I didn't care which date I used. Now, in the criteria
row enter
<=2

For future reference. If you store Date() to a field, the value is the
date with a time of 00:00:00. If you store Now() to a field, the value
is the date and time. If you are querying data for all records equal to
Date() and the value contains a Now() value, you most likely will end up
with zero records...or only records with a time of 00:00:00. In this
case you'd want to get records between today and tomorrow..between
date() and Date()+1

[C White]

Salad wrote:

C White wrote:

Hi

I am having problems with running a query that does the following

there are 5 fields in a table that the query is based on, the first
four are simple enough and all that happens is that the fields need to
match, for that I was able to do the following to get records out of
the database (does that make sense?)

In (SELECT [SERIAL_NUM] FROM [TABLE] As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)>1
And [SR_SUB_AREA] = [TABLE].[SR_SUB_AREA] And [DETAIL] =
[TABLE].[DETAIL] And [SUB_DETAIL] = [TABLE].[SUB_DETAIL])

The fifth field is a date field that is formatted as follows:
"11/08/2004 10:06:04 PM"

what I am having problem with is getting the query to pull the records
that are within 2 days of eachother.

Simply put the query need to pull out the records where the first four
fields match and they were created within 2 days of eachother.

I'm probably using the wrong terms when I try to search for an
answer/tutorial on how to this as I have found nothing to date.

Any help, would be appreciated.

Thanks

You can use the DateDiff function.
DiffDays : Abs(DateDiff("d",Date1,Date2))
I used ABS() so I didn't care which date I used. Now, in the criteria
row enter
<=2

For future reference. If you store Date() to a field, the value is the
date with a time of 00:00:00. If you store Now() to a field, the value
is the date and time. If you are querying data for all records equal to
Date() and the value contains a Now() value, you most likely will end up
with zero records...or only records with a time of 00:00:00. In this
case you'd want to get records between today and tomorrow..between
date() and Date()+1

Thanks for your suggestion, unfortunately it returned the same amount of
records as I had before :( I am not an access user, I'm only doing this
as there is no one else remotely close to being able to do so

what I have right now is this:

1. some, info, here, 10/11/2004
2. some, info, here, 10/12/2004
3. more, info, here, 08/12/2004
4. more, info, here, 09/02/2004

what I need the query to do is only display records 1 and 2 as they fall
within the date range

[Salad]

C White wrote:

Salad wrote:

C White wrote:

Hi

I am having problems with running a query that does the following

there are 5 fields in a table that the query is based on, the first
four are simple enough and all that happens is that the fields need
to match, for that I was able to do the following to get records out
of the database (does that make sense?)

In (SELECT [SERIAL_NUM] FROM [TABLE] As Tmp GROUP BY
[SERIAL_NUM],[SR_SUB_AREA],[DETAIL],[SUB_DETAIL] HAVING Count(*)>1
And [SR_SUB_AREA] = [TABLE].[SR_SUB_AREA] And [DETAIL] =
[TABLE].[DETAIL] And [SUB_DETAIL] = [TABLE].[SUB_DETAIL])

The fifth field is a date field that is formatted as follows:
"11/08/2004 10:06:04 PM"

what I am having problem with is getting the query to pull the
records that are within 2 days of eachother.

Simply put the query need to pull out the records where the first
four fields match and they were created within 2 days of eachother.

I'm probably using the wrong terms when I try to search for an
answer/tutorial on how to this as I have found nothing to date.

Any help, would be appreciated.

Thanks

You can use the DateDiff function.
DiffDays : Abs(DateDiff("d",Date1,Date2))
I used ABS() so I didn't care which date I used. Now, in the criteria
row enter
<=2

For future reference. If you store Date() to a field, the value is
the date with a time of 00:00:00. If you store Now() to a field, the
value is the date and time. If you are querying data for all records
equal to Date() and the value contains a Now() value, you most likely
will end up with zero records...or only records with a time of
00:00:00. In this case you'd want to get records between today and
tomorrow..between date() and Date()+1

Thanks for your suggestion, unfortunately it returned the same amount of
records as I had before :( I am not an access user, I'm only doing this
as there is no one else remotely close to being able to do so

what I have right now is this:

1. some, info, here, 10/11/2004
2. some, info, here, 10/12/2004
3. more, info, here, 08/12/2004
4. more, info, here, 09/02/2004

what I need the query to do is only display records 1 and 2 as they fall
within the date range

Allen Browne has been answering your question in
microsoft.public.access.queries. Allen is one sharp dude, so you must
be missing something in his responses.

Do you know how to program? Write code? If so, you could create a
function. You'd pass the ID and date of the current record to the
function. THe function could select all records that are not that ID
that have a date withing 2 days, plus/minus of the current date. If
there are records that match then it is true and is to be displayed.

Ex:
ShowThis : DisplayThisRec(Id, DateFld)

In the criteria row, set this to True, no need to display.

Your function would be (field names/tables names unknown by me)
Function DisplayThisRec(varId, varDateFld) As Boolean
DIm strSQL As STring
Dim rst As REcordset
'you'll need to fiddle with this line
strSQL = "Select Top 1 ID From TableName Where " & _
"ID <> " & varID & " And " & _
"Abs(DateDiff('d',#" & DateField & "#,#" & _
varDateFld & "#)) < 2"
set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
DisplayThisRec = rst.recordcount > 0
end function