Solved

need date range for one year but shows all

Posted on 2007-11-27
7
233 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
[X]
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
  • 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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

695 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