|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 13th, 2009, 10:27 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
Date Range Prompt
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... 
|
|
#2
October 13th, 2009, 10:58 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Date Range Prompt
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. "Charess" wrote: 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...
|
|
#3
October 13th, 2009, 11:04 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Date Range Prompt
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" wrote: 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. "Charess" wrote: 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...
|
|
#4
October 13th, 2009, 11:41 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Date Range Prompt
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" wrote: 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" wrote: 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. "Charess" wrote: 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...
|
|
#5
October 15th, 2009, 04:56 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Date Range Prompt
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. "Charess" wrote: 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...
|
|
#6
October 15th, 2009, 05:35 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Date Range Prompt
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" wrote: 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" wrote: 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" wrote: 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. "Charess" wrote: 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...
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
