• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

need date range for one year but shows all

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
BobRosas
Asked:
BobRosas
  • 4
  • 3
1 Solution
 
digital_thoughtsCommented:
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
 
BobRosasAuthor Commented:
It's like 11/27/07, txtFrom format is set to short date.  Thanks for your reply.
0
 
digital_thoughtsCommented:
Could you post the query you are using? I tested that statement and it worked perfectly for me...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BobRosasAuthor Commented:
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
 
digital_thoughtsCommented:
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
 
BobRosasAuthor Commented:
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
 
BobRosasAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now