Date Range Prompt

C

Charess

Hello,

I have a query in which I have a prompt on last day. For some odd reason
when I enter in any range containing the date 10/1/09. I don't get the full
range of data however, when I enter 9/30/09 I get everything. For example I
enter start day as 9/1/2005 and end date as of 9/30/2009 I get 24 rows of
data, but if I enter 9/1/2005 to 10/1/09 I get 3 rows and I have no records
with the 10/1/09 last day.

I am very confused and any help is appreciated...
 
K

KARL DEWEY

Post the SQL by opening the query in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
 
C

Charess

Karl, here is the sql code:

SELECT TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1) AS
Categories, Count(TblGrpReason.UserPri_SecReasons) AS
CountOfUserPri_SecReasons, TblGrpReason.bigSix, TblGrpReason.levelTwo,
TblGrpReason.levelThree, TblGrpReason.MgmtLevel, TblGrpReason.Race,
TblGrpReason.Gender, TblGrpReason.hireDate, Right([hireDate],4) AS [Hire
Year], TblGrpReason.DOB, IIf(CDate([DOB])>=#1/1/2003#,"Gen
Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen
X",IIf(CDate([DOB])>=#1/1/1945#,"Baby Boomers","Traditionalist")))) AS [Date
of Birth], TblGrpReason.EmployeeLastDay
FROM TblGrpReason
GROUP BY TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1),
TblGrpReason.bigSix, TblGrpReason.levelTwo, TblGrpReason.levelThree,
TblGrpReason.MgmtLevel, TblGrpReason.Race, TblGrpReason.Gender,
TblGrpReason.hireDate, Right([hireDate],4), TblGrpReason.DOB,
IIf(CDate([DOB])>=#1/1/2003#,"Gen Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen
Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen X",IIf(CDate([DOB])>=#1/1/1945#,"Baby
Boomers","Traditionalist")))), TblGrpReason.EmployeeLastDay
HAVING (((TblGrpReason.EmployeeLastDay) Between [Start Date] And [End Date]));
 
K

KARL DEWEY

I expect that you are using text fields for your dates as you convert DOB
before applying criteria -- IIf(CDate([DOB])>=#1/1/1984#,

So try this --
HAVING (((CVDate(TblGrpReason.EmployeeLastDay)) Between CVDate([Start Date])
And CVDate([End Date])));


--
Build a little, test a little.


Charess said:
Karl, here is the sql code:

SELECT TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1) AS
Categories, Count(TblGrpReason.UserPri_SecReasons) AS
CountOfUserPri_SecReasons, TblGrpReason.bigSix, TblGrpReason.levelTwo,
TblGrpReason.levelThree, TblGrpReason.MgmtLevel, TblGrpReason.Race,
TblGrpReason.Gender, TblGrpReason.hireDate, Right([hireDate],4) AS [Hire
Year], TblGrpReason.DOB, IIf(CDate([DOB])>=#1/1/2003#,"Gen
Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen
X",IIf(CDate([DOB])>=#1/1/1945#,"Baby Boomers","Traditionalist")))) AS [Date
of Birth], TblGrpReason.EmployeeLastDay
FROM TblGrpReason
GROUP BY TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1),
TblGrpReason.bigSix, TblGrpReason.levelTwo, TblGrpReason.levelThree,
TblGrpReason.MgmtLevel, TblGrpReason.Race, TblGrpReason.Gender,
TblGrpReason.hireDate, Right([hireDate],4), TblGrpReason.DOB,
IIf(CDate([DOB])>=#1/1/2003#,"Gen Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen
Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen X",IIf(CDate([DOB])>=#1/1/1945#,"Baby
Boomers","Traditionalist")))), TblGrpReason.EmployeeLastDay
HAVING (((TblGrpReason.EmployeeLastDay) Between [Start Date] And [End Date]));

KARL DEWEY said:
Post the SQL by opening the query in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
Click to expand...
Click to expand...
 
K

KARL DEWEY

What do you get entering hard dates like this --
HAVING (((CVDate(TblGrpReason.EmployeeLastDay)) Between #1/1/2000# And
#10/1/2009#));

and ---
HAVING (((CVDate(TblGrpReason.EmployeeLastDay)) Between #1/1/00# And
#10/1/09#));

--
Build a little, test a little.


Charess said:
Hi Karl,

The data type for employee's last day is "date/time" when I enter in the CV
formula I get an error message that says,"the expression is typed
incorrectly, or is too complex to be evaluated."

KARL DEWEY said:
I expect that you are using text fields for your dates as you convert DOB
before applying criteria -- IIf(CDate([DOB])>=#1/1/1984#,

So try this --
HAVING (((CVDate(TblGrpReason.EmployeeLastDay)) Between CVDate([Start Date])
And CVDate([End Date])));


--
Build a little, test a little.


Charess said:
Karl, here is the sql code:

SELECT TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1) AS
Categories, Count(TblGrpReason.UserPri_SecReasons) AS
CountOfUserPri_SecReasons, TblGrpReason.bigSix, TblGrpReason.levelTwo,
TblGrpReason.levelThree, TblGrpReason.MgmtLevel, TblGrpReason.Race,
TblGrpReason.Gender, TblGrpReason.hireDate, Right([hireDate],4) AS [Hire
Year], TblGrpReason.DOB, IIf(CDate([DOB])>=#1/1/2003#,"Gen
Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen
X",IIf(CDate([DOB])>=#1/1/1945#,"Baby Boomers","Traditionalist")))) AS [Date
of Birth], TblGrpReason.EmployeeLastDay
FROM TblGrpReason
GROUP BY TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1),
TblGrpReason.bigSix, TblGrpReason.levelTwo, TblGrpReason.levelThree,
TblGrpReason.MgmtLevel, TblGrpReason.Race, TblGrpReason.Gender,
TblGrpReason.hireDate, Right([hireDate],4), TblGrpReason.DOB,
IIf(CDate([DOB])>=#1/1/2003#,"Gen Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen
Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen X",IIf(CDate([DOB])>=#1/1/1945#,"Baby
Boomers","Traditionalist")))), TblGrpReason.EmployeeLastDay
HAVING (((TblGrpReason.EmployeeLastDay) Between [Start Date] And [End Date]));

:

Post the SQL by opening the query in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

--
Build a little, test a little.


:

Hello,

I have a query in which I have a prompt on last day. For some odd reason
when I enter in any range containing the date 10/1/09. I don't get the full
range of data however, when I enter 9/30/09 I get everything. For example I
enter start day as 9/1/2005 and end date as of 9/30/2009 I get 24 rows of
data, but if I enter 9/1/2005 to 10/1/09 I get 3 rows and I have no records
with the 10/1/09 last day.

I am very confused and any help is appreciated...
Click to expand...
Click to expand...
Click to expand...
 
C

Charess

Hi Karl,

The data type for employee's last day is "date/time" when I enter in the CV
formula I get an error message that says,"the expression is typed
incorrectly, or is too complex to be evaluated."

KARL DEWEY said:
I expect that you are using text fields for your dates as you convert DOB
before applying criteria -- IIf(CDate([DOB])>=#1/1/1984#,

So try this --
HAVING (((CVDate(TblGrpReason.EmployeeLastDay)) Between CVDate([Start Date])
And CVDate([End Date])));


--
Build a little, test a little.


Charess said:
Karl, here is the sql code:

SELECT TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1) AS
Categories, Count(TblGrpReason.UserPri_SecReasons) AS
CountOfUserPri_SecReasons, TblGrpReason.bigSix, TblGrpReason.levelTwo,
TblGrpReason.levelThree, TblGrpReason.MgmtLevel, TblGrpReason.Race,
TblGrpReason.Gender, TblGrpReason.hireDate, Right([hireDate],4) AS [Hire
Year], TblGrpReason.DOB, IIf(CDate([DOB])>=#1/1/2003#,"Gen
Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen
X",IIf(CDate([DOB])>=#1/1/1945#,"Baby Boomers","Traditionalist")))) AS [Date
of Birth], TblGrpReason.EmployeeLastDay
FROM TblGrpReason
GROUP BY TblGrpReason.UserPri_SecReasons,
Left([UserPri_SecReasons],InStr(1,[UserPri_SecReasons] & "-","-")-1),
TblGrpReason.bigSix, TblGrpReason.levelTwo, TblGrpReason.levelThree,
TblGrpReason.MgmtLevel, TblGrpReason.Race, TblGrpReason.Gender,
TblGrpReason.hireDate, Right([hireDate],4), TblGrpReason.DOB,
IIf(CDate([DOB])>=#1/1/2003#,"Gen Z",IIf(CDate([DOB])>=#1/1/1984#,"Gen
Y",IIf(CDate([DOB])>=#1/1/1965#,"Gen X",IIf(CDate([DOB])>=#1/1/1945#,"Baby
Boomers","Traditionalist")))), TblGrpReason.EmployeeLastDay
HAVING (((TblGrpReason.EmployeeLastDay) Between [Start Date] And [End Date]));

KARL DEWEY said:
Post the SQL by opening the query in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

--
Build a little, test a little.


:

Hello,

I have a query in which I have a prompt on last day. For some odd reason
when I enter in any range containing the date 10/1/09. I don't get the full
range of data however, when I enter 9/30/09 I get everything. For example I
enter start day as 9/1/2005 and end date as of 9/30/2009 I get 24 rows of
data, but if I enter 9/1/2005 to 10/1/09 I get 3 rows and I have no records
with the 10/1/09 last day.

I am very confused and any help is appreciated...
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

Error Handler displaying message when no error 5
Date Range within a date range - try again 2
count cells 4
date range within a date range 2
Mid-Month function help 3
Date range within a date range 2
How do I find a match for three variables to determine the result? 4
Counter 1

Top