473,287 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

query using dates

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
Nov 13 '05 #1
4 2834
Answered in m.p.a.queries
Nov 13 '05 #2
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
Nov 13 '05 #3
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

Nov 13 '05 #4
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
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Lukelrc | last post by:
Hi. I have a table (websitehits) which holds statistics about websites. This table has a date field (datecounted). What I need is to create a query which returns a list of dates between two date...
6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
1
by: Mike Cooper | last post by:
Hi everyone, This is a tough one. I have a database full of solicitations, identifying a customer and recording initial call, first followup, second followup, etc. My boss want to be able to...
2
by: carl.barrett | last post by:
Hi, I'm back with the same question as I still can't get it to display my data the way I want it to. The table lists information about a perpetrator involved with an anti social behaviour...
6
by: Bill R via AccessMonster.com | last post by:
I have a query: SELECT tblCalendar.CalendarDay AS LastSunday FROM tblCalendar WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
2
by: Mark Roughton | last post by:
I have a form where the users need to view records for various criteria, one of which is a date field on which they may wish to view all related data for the selected date, for all dates upto and...
5
by: veaux | last post by:
I'm thinking this is easy but can't get it. I have a table with following: Table1 Date 1/1/2007 Table2 Type 0107 (This is MMYY of above) So I'm having trouble using a query to turn the...
2
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year data. I have been creating the Access database...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.