Solved

need date range for one year but shows all

Posted on 2007-11-27
7
204 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
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now