"Between" Parameter Query not working with DateAdd

I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

--
Christy Wyatt

When prompted what exactly are you typing in? Please provide an example.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Christy Wyatt" wrote:

I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

--
Christy Wyatt

"Christy Wyatt" wrote in message
...
I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

--
Christy Wyatt



Add a criteria for Year(Date())

Tom Lake

On Tue, 9 Jan 2007 10:41:00 -0800, Christy Wyatt
wrote:

I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

Is this a Date/Time field? or are you storing the date in a text
field, or using Format() to cast the date into a string such as
"July"?

Please post the SQL view of your query.

John W. Vinson[MVP]

I indicated a prompt for Between [Type First Pay Date] and [Type Last Pay
Date].
--
Christy Wyatt


"Jerry Whittle" wrote:

When prompted what exactly are you typing in? Please provide an example.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Christy Wyatt" wrote:

I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

--
Christy Wyatt

How would I do that? See the SQL Code below. (What I am doing here is a
first level query so that I can do a month's pay total in a crosstab to
download our disabled workers, we call them consumers, monthly wages to the
Social Security Website). I have a separate table with WkEndingDt, and
WkEndID where all pay is linked to the week by that WkEndID. The pay is also
linked to the consumer by ConID. That way I can use a form to enter by the
week, or enter in a different form by consumer.

SELECT Consumer.Name, Consumer.SSN, WkEndingDt.WkEndDate, Pay.Gross,
Consumer.LOC, DateAdd("d",7,[WkEndDate]) AS PayDay, DatePart("m",[PayDay]) AS
[Month], DatePart("yyyy",[PayDay]) AS [Year]
FROM WkEndingDt INNER JOIN (Consumer INNER JOIN Pay ON Consumer.ConID =
Pay.ConID) ON WkEndingDt.WkEndID = Pay.WkEndID
WHERE (((Consumer.LOC) Not Like "Bly") AND ((DateAdd("d",7,[WkEndDate]))
Between [Type First Pay Date] And [Type Last Pay Date]))
ORDER BY Consumer.Name;



--
Christy Wyatt


"Tom Lake" wrote:

"Christy Wyatt" wrote in message
...
I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

--
Christy Wyatt



Add a criteria for Year(Date())

Tom Lake

It is a date/time field. I even changed the properties of the expression for
the calculated field [PayDay] to shortdate to be sure it treated it as a
date. I gave Tom Lake the SQL Code. I'll repeat it here.

SELECT Consumer.Name, Consumer.SSN, WkEndingDt.WkEndDate, Pay.Gross,
Consumer.LOC, DateAdd("d",7,[WkEndDate]) AS PayDay, DatePart("m",[PayDay]) AS
[Month], DatePart("yyyy",[PayDay]) AS [Year]
FROM WkEndingDt INNER JOIN (Consumer INNER JOIN Pay ON Consumer.ConID =
Pay.ConID) ON WkEndingDt.WkEndID = Pay.WkEndID
WHERE (((Consumer.LOC) Not Like "Bly") AND ((DateAdd("d",7,[WkEndDate]))
Between [Type First Pay Date] And [Type Last Pay Date]))
ORDER BY Consumer.Name;

Consumers are our disabled workers, and I'm sending a monthly total of their
wages to the Social Security Website. The tables are WkEndingDate, Consumer,
Pay and

--
Christy Wyatt


"John Vinson" wrote:

On Tue, 9 Jan 2007 10:41:00 -0800, Christy Wyatt
wrote:

I added 7 days to a WkEndDate to come up with the paydate in a query. When I
use "Between [Type First Day of Month] and [Type Last Day of Month] to get
all the pay days in that month, it gives me the right month, but all the
years in the database are displayed, i.e. July 04, July 05, July 06; when all
I wanted was July of 06. The PayDates sort just fine, and I can even get
proper DatePart "m" or "yyyy" out of them. It treats them like dates in all
respects except the date range.

Is this a Date/Time field? or are you storing the date in a text
field, or using Format() to cast the date into a string such as
"July"?

Please post the SQL view of your query.

John W. Vinson[MVP]

On Wed, 10 Jan 2007 07:44:01 -0800, Christy Wyatt
wrote:

It is a date/time field. I even changed the properties of the expression for
the calculated field [PayDay] to shortdate to be sure it treated it as a
date. I gave Tom Lake the SQL Code. I'll repeat it here.

Try explicitly casting the parameters as date values:

PARAMETERS [Type First Pay Date] DateTime, [Type Last Pay Date]
DateTime;
SELECT Consumer.Name, Consumer.SSN, WkEndingDt.WkEndDate, Pay.Gross,
Consumer.LOC, DateAdd("d",7,[WkEndDate]) AS PayDay,
DatePart("m",[PayDay]) AS
[Month], DatePart("yyyy",[PayDay]) AS [Year]
FROM WkEndingDt
INNER JOIN (Consumer INNER JOIN Pay ON Consumer.ConID = Pay.ConID)
ON WkEndingDt.WkEndID = Pay.WkEndID
WHERE (((Consumer.LOC) Not Like "Bly") AND
((DateAdd("d",7,[WkEndDate]))
Between [Type First Pay Date] And [Type Last Pay Date]))
ORDER BY Consumer.Name;

or possibly use the CDate() function to take whatever the user types
and cast it into a valid date/time:

Between CDate([Type First Pay Date]) And CDate([Type Last Pay Date])))

You may want to consider using a Form to solicit the criteria rather
than prompts - instead of

[Type First Pay Date]

use a Form (let's call it frmSearch) with a textbox txtFirstDate, and
use

BETWEEN [Forms]![frmSearch]![txtFirstDate] AND ...

This would let you use an Input Mask on the unbound textboxes to
ensure that the user doesn't type some string which Access could
misinterpret (which is the likely source of your error message).

John W. Vinson[MVP]

You are the man of the day. That worked just great!!! Thanks.
--
Christy Wyatt


"John Vinson" wrote:

On Wed, 10 Jan 2007 07:44:01 -0800, Christy Wyatt
wrote:

It is a date/time field. I even changed the properties of the expression for
the calculated field [PayDay] to shortdate to be sure it treated it as a
date. I gave Tom Lake the SQL Code. I'll repeat it here.

Try explicitly casting the parameters as date values:

PARAMETERS [Type First Pay Date] DateTime, [Type Last Pay Date]
DateTime;
SELECT Consumer.Name, Consumer.SSN, WkEndingDt.WkEndDate, Pay.Gross,
Consumer.LOC, DateAdd("d",7,[WkEndDate]) AS PayDay,
DatePart("m",[PayDay]) AS
[Month], DatePart("yyyy",[PayDay]) AS [Year]
FROM WkEndingDt
INNER JOIN (Consumer INNER JOIN Pay ON Consumer.ConID = Pay.ConID)
ON WkEndingDt.WkEndID = Pay.WkEndID
WHERE (((Consumer.LOC) Not Like "Bly") AND
((DateAdd("d",7,[WkEndDate]))
Between [Type First Pay Date] And [Type Last Pay Date]))
ORDER BY Consumer.Name;

or possibly use the CDate() function to take whatever the user types
and cast it into a valid date/time:

Between CDate([Type First Pay Date]) And CDate([Type Last Pay Date])))

You may want to consider using a Form to solicit the criteria rather
than prompts - instead of

[Type First Pay Date]

use a Form (let's call it frmSearch) with a textbox txtFirstDate, and
use

BETWEEN [Forms]![frmSearch]![txtFirstDate] AND ...

This would let you use an Input Mask on the unbound textboxes to
ensure that the user doesn't type some string which Access could
misinterpret (which is the likely source of your error message).

John W. Vinson[MVP]

On Wed, 10 Jan 2007 11:38:01 -0800, Christy Wyatt
wrote:

You are the man of the day. That worked just great!!! Thanks.

Great... for my (and others') reference, which of the three proposed
solutions solved the problem?

John W. Vinson[MVP]