Solved

need date range for one year but shows all

Posted on 2007-11-27
7
214 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 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