Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

need date range for one year but shows all

Posted on 2007-11-27
7
219 Views
Last Modified: 2010-04-21
The following code which I have in my query is showing all years even tho txtFrom and txtTo show only the year 2007.  Can someone please tell me what I am doing wrong?
Thanks in advance!

Between (Format([Forms]![frmPieces]![txtFrom],"mm/dd/yyyy")) And (Format([Forms]![frmPieces]![txtTo],"mm/dd/yyyy"))
0
Comment
Question by:BobRosas
  • 4
  • 3
7 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20360122
Is the value of txtFrom and txtTo 2007 or like 11/27/2007? If its only 2007 then the format may not be formatting correctly...
0
 

Author Comment

by:BobRosas
ID: 20360157
It's like 11/27/07, txtFrom format is set to short date.  Thanks for your reply.
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20360382
Could you post the query you are using? I tested that statement and it worked perfectly for me...
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:BobRosas
ID: 20360472
Gladly.  Thanks so much!

SELECT DISTINCT Format([TimeIn],"mm/dd/yyyy") AS Expr1
FROM dbo_MasterJobCodeList INNER JOIN dbo_EmployeeHours ON dbo_MasterJobCodeList.JobCode = dbo_EmployeeHours.JobCode
GROUP BY Format([TimeIn],"mm/dd/yyyy")
HAVING (((Format([TimeIn],"mm/dd/yyyy")) Between (Format([Forms]![frmPieces]![txtFrom],"mm/dd/yyyy")) And (Format([Forms]![frmPieces]![txtTo],"mm/dd/yyyy"))));
0
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 125 total points
ID: 20360808
Ok, I checked over the query and even tested in a similar data model and it worked just fine. I would say its not the query that's the problem. Have you verified the text boxes have the values expected for the query?

Also, have you tried the query with just "hard coded" dates?

SELECT DISTINCT Format([TimeIn],"mm/dd/yyyy") AS Expr1
FROM dbo_MasterJobCodeList INNER JOIN dbo_EmployeeHours ON dbo_MasterJobCodeList.JobCode = dbo_EmployeeHours.JobCode
GROUP BY Format([TimeIn],"mm/dd/yyyy")
HAVING (((Format([TimeIn],"mm/dd/yyyy")) Between (Format(#1/1/2006#,"mm/dd/yyyy")) And (Format(#12/31/2007#,"mm/dd/yyyy"))));

Last question, is there a particular reason you are using a GROUP BY with a HAVING statment? You could just have a WHERE clause and get the same result.

SELECT DISTINCT Format([TimeIn],"mm/dd/yyyy") AS Expr1
FROM dbo_MasterJobCodeList INNER JOIN dbo_EmployeeHours ON dbo_MasterJobCodeList.JobCode = dbo_EmployeeHours.JobCode
WHERE (((Format([TimeIn],"mm/dd/yyyy")) Between (Format([Forms]![frmPieces]![txtFrom],"mm/dd/yyyy")) And (Format([Forms]![frmPieces]![txtTo],"mm/dd/yyyy"))));
0
 

Author Closing Comment

by:BobRosas
ID: 31411229
The reason I used Group By instead of Where is becasue honestly I didn't think it mattered, but thanks for the tip.  Also, you were right, the text boxes were not displaying what I expected.  I should be able to troubleshoot and fix it now.
Thanks so much for your help!!
0
 

Author Comment

by:BobRosas
ID: 20361364
The reason I used Group By instead of Where is becasue honestly I didn't think it mattered, but thanks for the tip.  Also, you were right, the text boxes were not displaying what I expected.  I should be able to troubleshoot and fix it now.
Thanks so much for your help!!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 40
SQL Syntax: How to force case sensitive query? 2 44
sql server query 12 24
Current Month Filter in Visual Studio 10 21
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question