Queries with Special date restrictors

C

ChuckW

Hi,

I want to create two different queries against a
transactions table with a date field. One will get all
transaction between the first and the 15th of the month.
The second will get all transactions between the 15th and
the 31st of the month (or the 30th or the 28th for
February). Can anyone help?

Thanks,

Chuck
 
B

Brian Camire

To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) Between 1 And 15

To get the transactions for the remainder of the month, you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) > 15
 
C

ChuckW

Brian,

I guess I wasn't as clear as I could have been. I wanted
to get all transactions from the current month that were
between the 1st and the 15th. The query you gave me
gives all months. Is there a way to restrict it to the
current month? Also, what would the query look like for
all transactions between the 16th and the 31st of this
month?

Thanks,

Chuck

-----Original Message-----
To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) Between 1 And 15

To get the transactions for the remainder of the month, you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) > 15


Hi,

I want to create two different queries against a
transactions table with a date field. One will get all
transaction between the first and the 15th of the month.
The second will get all transactions between the 15th and
the 31st of the month (or the 30th or the 28th for
February). Can anyone help?

Thanks,

Chuck
Click to expand...


.
Click to expand...
 
B

Brian Camire

OK. Try this...

To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date()), Month(Date()),
1)
AND
[Your Table].[Transaction Date] < DateSerial(Year(Date()), Month(Date()),
16)

To get the transactions for the remainder of the month, you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date()), Month(Date()),
16



ChuckW said:
Brian,

I guess I wasn't as clear as I could have been. I wanted
to get all transactions from the current month that were
between the 1st and the 15th. The query you gave me
gives all months. Is there a way to restrict it to the
current month? Also, what would the query look like for
all transactions between the 16th and the 31st of this
month?

Thanks,

Chuck

-----Original Message-----
To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) Between 1 And 15

To get the transactions for the remainder of the month, you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) > 15


Hi,

I want to create two different queries against a
transactions table with a date field. One will get all
transaction between the first and the 15th of the month.
The second will get all transactions between the 15th and
the 31st of the month (or the 30th or the 28th for
February). Can anyone help?

Thanks,

Chuck
Click to expand...


.
Click to expand...
Click to expand...
 
C

ChuckW

Brian,

I appreciate your help with this issue. The date
restrictor you gave for the first 15 days of the month
worked perfectly. The second restrictor got everything
after the 15th of the month but also include records for
December as well. I wanted to get everything between the
16th and the 31st or the 30th or the 28th for February.
I tried the following date restrictor for this:

WHERE ProdReorder3.Reorder_Date between DateSerial(Year
(Date()), Month(Date()),16)
AND DateSerial(Year(Date()), Month(Date()),31)

The problem is that there are 30 days in November so my
query returned transactions for December 1st. My guess
is that this will happen for all months with 30 days.
Also, it will return the first 3 days for March when the
query is run in February. Is there a restrictor that
will simply get the last day of the month regardless of
the date (28th, 30th or 31st)?

Thanks,

Chuck
-----Original Message-----
OK. Try this...

To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date ()), Month(Date()),
1)
AND
[Your Table].[Transaction Date] < DateSerial(Year(Date ()), Month(Date()),
16)

To get the transactions for the remainder of the month, you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date ()), Month(Date()),
16



Brian,

I guess I wasn't as clear as I could have been. I wanted
to get all transactions from the current month that were
between the 1st and the 15th. The query you gave me
gives all months. Is there a way to restrict it to the
current month? Also, what would the query look like for
all transactions between the 16th and the 31st of this
month?

Thanks,

Chuck

-----Original Message-----
To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) Between 1 And 15

To get the transactions for the remainder of the
Click to expand...
month,
you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) > 15


"ChuckW" <[email protected]> wrote
Click to expand...
in
message
Hi,

I want to create two different queries against a
transactions table with a date field. One will get all
transaction between the first and the 15th of the month.
The second will get all transactions between the
Click to expand...
15th
and
the 31st of the month (or the 30th or the 28th for
February). Can anyone help?

Thanks,

Chuck


.
Click to expand...
Click to expand...


.
Click to expand...
 
B

Brian Camire

Sorry. The query for the second part of the month should have been:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date()), Month(Date()),
16)
AND
[Your Table].[Transaction Date] < DateSerial(Year(Date()), Month(Date()) +
1, 1)

As you observed DateSerial "wraps around". For example, DateSerial(2003,
11, 31) returns December 1, 2003.

I recommend you avoid using Between...And with Date/Time fields unless you
are certain that the dates you are dealing with have no time of day
component. Otherwise, you may not get the results you might expect. For
example, if for some record in [Your Table], [Transaction Date] has the
value #11/30/2003 04:00 PM# then the criteria

WHERE
[Your Table].[Transaction Date]
BETWEEN
#11/16/2003#
AND
#11/30/2003#

would exclude this record, since #11/30/2003# is midnight on the morning of
November 30, which is before 4:00 PM.


ChuckW said:
Brian,

I appreciate your help with this issue. The date
restrictor you gave for the first 15 days of the month
worked perfectly. The second restrictor got everything
after the 15th of the month but also include records for
December as well. I wanted to get everything between the
16th and the 31st or the 30th or the 28th for February.
I tried the following date restrictor for this:

WHERE ProdReorder3.Reorder_Date between DateSerial(Year
(Date()), Month(Date()),16)
AND DateSerial(Year(Date()), Month(Date()),31)

The problem is that there are 30 days in November so my
query returned transactions for December 1st. My guess
is that this will happen for all months with 30 days.
Also, it will return the first 3 days for March when the
query is run in February. Is there a restrictor that
will simply get the last day of the month regardless of
the date (28th, 30th or 31st)?

Thanks,

Chuck
-----Original Message-----
OK. Try this...

To get the transactions between the 1st and 15th, you might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date ()), Month(Date()),
1)
AND
[Your Table].[Transaction Date] < DateSerial(Year(Date ()), Month(Date()),
16)

To get the transactions for the remainder of the month, you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Transaction Date] >= DateSerial(Year(Date ()), Month(Date()),
16



Brian,

I guess I wasn't as clear as I could have been. I wanted
to get all transactions from the current month that were
between the 1st and the 15th. The query you gave me
gives all months. Is there a way to restrict it to the
current month? Also, what would the query look like for
all transactions between the 16th and the 31st of this
month?

Thanks,

Chuck


-----Original Message-----
To get the transactions between the 1st and 15th, you
might use a query
whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) Between 1 And 15

To get the transactions for the remainder of the month,
you might use a
query whose SQL look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
Day([Your Table].[Transaction Date]) > 15


message
Hi,

I want to create two different queries against a
transactions table with a date field. One will get all
transaction between the first and the 15th of the
month.
The second will get all transactions between the 15th
and
the 31st of the month (or the 30th or the 28th for
February). Can anyone help?

Thanks,

Chuck


.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare current month totals with last months totals 1
Query for records before or after paydays 3
Current Month and Previous Month 8
Report sums based on record dates 1
Problem with group by query over date range 1
Date Calculation 4
Create a query that examines separate groups, not rows, within a t 8
Date parameter queries 2

Top