DateValue

[Bunky]

I have a query that has been working fine but now is allowing other dates to
be returned. The CompletedDate field is one of the criteria I am using for a
range to get data. By process of elimination, I have tracked the problem to
this: in the query I have DateValue([CompletedDate]) with a criteria of
Between ([Begin Date:]) And DateAdd("d",0,([End Date:])) so I will get all
records that have the Begin Date and those that have the End date. For some
reason, It is returning last years records.

The SQL follows:
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

 

[fredg]

I have a query that has been working fine but now is allowing other dates to
be returned. The CompletedDate field is one of the criteria I am using for a
range to get data. By process of elimination, I have tracked the problem to
this: in the query I have DateValue([CompletedDate]) with a criteria of
Between ([Begin Date:]) And DateAdd("d",0,([End Date:])) so I will get all
records that have the Begin Date and those that have the End date. For some
reason, It is returning last years records.

The SQL follows:
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

What is the purpose of adding 0 days to the End Date?
Does [CompletedDate] include a time value?

If so, what happens if you simply use:

[CompletedDate] Between [Begin Date:] And DateAdd("d",1,[End Date:])

If [CompletedDate] does not have a time value, then try:
[CompletedDate] Between [Begin Date:] And [End Date:]

where [Begin Date:] and [End Date:] are both parameter prompts.
This also assumes that [CompletedDate] is a Date datatype.

Note: when entering the parameter prompt date value, you must use the
U.S. date format of mm/dd/yyyy or the ISO date format of yyyy-mm-dd

 

[Bunky]

The adding of 0, I was told, would negate the time value. However, I tried
both the addition of 1 and the 0 to no avail. I added a Parameter Statement
to the SQL and it appears to be working and I don't need to add the 1 to the
ending date.

PARAMETERS [Begin Date:] DateTime, [End Date:] DateTime;
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE (((DateValue([CompletedDate])) Between [Begin Date:] And [End Date:])
AND (([table1.answer1]=[table2.answer1])=0) AND ((Table2.Module)=[Module
No:]));

Comments are always welcome and THANK YOU for your post.

I have a query that has been working fine but now is allowing other dates to
be returned. The CompletedDate field is one of the criteria I am using for a
range to get data. By process of elimination, I have tracked the problem to
this: in the query I have DateValue([CompletedDate]) with a criteria of
Between ([Begin Date:]) And DateAdd("d",0,([End Date:])) so I will get all
records that have the Begin Date and those that have the End date. For some
reason, It is returning last years records.

The SQL follows:
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

What is the purpose of adding 0 days to the End Date?
Does [CompletedDate] include a time value?

If so, what happens if you simply use:

[CompletedDate] Between [Begin Date:] And DateAdd("d",1,[End Date:])

If [CompletedDate] does not have a time value, then try:
[CompletedDate] Between [Begin Date:] And [End Date:]

where [Begin Date:] and [End Date:] are both parameter prompts.
This also assumes that [CompletedDate] is a Date datatype.

Note: when entering the parameter prompt date value, you must use the
U.S. date format of mm/dd/yyyy or the ISO date format of yyyy-mm-dd

 

[John Spencer]

Try changing the SQL do it knows the two parameter are datetime parameters.


Parameters [Begin Date:] DateTime, [End Date:] DateTime,
[Module No;] Text (255);
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

I can't figure out why you are add zero days to the End Date parameter. That
seems to be a useless calculation to me.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Bunky]

John,
I added the Parameters definition to the SQL query and it appears to work
fine now.

Thank you for your assistance.

Try changing the SQL do it knows the two parameter are datetime parameters.


Parameters [Begin Date:] DateTime, [End Date:] DateTime,
[Module No;] Text (255);
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

I can't figure out why you are add zero days to the End Date parameter. That
seems to be a useless calculation to me.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a query that has been working fine but now is allowing other dates to
be returned. The CompletedDate field is one of the criteria I am using for a
range to get data. By process of elimination, I have tracked the problem to
this: in the query I have DateValue([CompletedDate]) with a criteria of
Between ([Begin Date:]) And DateAdd("d",0,([End Date:])) so I will get all
records that have the Begin Date and those that have the End date. For some
reason, It is returning last years records.

The SQL follows:
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

 

[Bunky]

Since everyone was so helpful on the 1st question; I have another one that is
driving me nuts. The output of the query that I showed in the initial
question goes into a second query with a table. I am now getting the correct
number of people who fit the criteria in the first query. These are people
who missed a question on a test. The second query is to show the question
that was missed. The output of this second query should be the module
number, the number of people who missed this question and the verbage for the
question. The output is showing find until the verbage of the question is to
displayed. It shows as a chinese character (non-printable). What am I doing
wrong? This system has been up and running for a while.

SQL to follow-
SELECT Q3.CountOfUserID, Table1.Question3
FROM Table1, Q3
WHERE (((Table1.Module)=[Module No:]))
GROUP BY Q3.CountOfUserID, Table1.Question3;

Thank you for your knowledge and assistance!

John,
I added the Parameters definition to the SQL query and it appears to work
fine now.

Thank you for your assistance.

Try changing the SQL do it knows the two parameter are datetime parameters.


Parameters [Begin Date:] DateTime, [End Date:] DateTime,
[Module No;] Text (255);
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));

I can't figure out why you are add zero days to the End Date parameter. That
seems to be a useless calculation to me.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a query that has been working fine but now is allowing other dates to
be returned. The CompletedDate field is one of the criteria I am using for a
range to get data. By process of elimination, I have tracked the problem to
this: in the query I have DateValue([CompletedDate]) with a criteria of
Between ([Begin Date:]) And DateAdd("d",0,([End Date:])) so I will get all
records that have the Begin Date and those that have the End date. For some
reason, It is returning last years records.

The SQL follows:
SELECT Count(Table2.UserID) AS CountOfUserID
FROM Table2 INNER JOIN Table1 ON Table2.Module = Table1.Module
WHERE ((([table1.answer13]=[table2.answer13])=0) AND
((Table2.Module)=[Module No:]) AND ((DateValue([CompletedDate])) Between
([Begin Date:]) And DateAdd("d",0,([End Date:]))));