?
Solved

Help with Formula

Posted on 2011-03-17
8
Medium Priority
?
229 Views
Last Modified: 2012-05-11
Hi,

I have a field called UTIMEOD which contains date and time in the following format.  The field captures what date and time the record was submitted to our website.

2011-03-17 10:15:00

Im trying to write a report which identifies records which are submitted between midnight and midday and also between midday and midnight.

What would be the syntax to query this field and allow me to pick up the records base on the time frame?
0
Comment
Question by:daiwhyte
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35155720
You can get two groups just by testing the hour.

If Hour({table.datetimefield} <12 then
""Group1"
else
"Group2"


You can then group the report on date and/or group.
To get the date on its own you can do
Datevalue({table.datetimefield})
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35156693
Is the field a datetime or string field?

If string how does it show PM times?

mlmcc
0
 

Author Comment

by:daiwhyte
ID: 35156861
Its a string field and it uses the 24hr clock.
0
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 35159221
Try this.  You can change the result to whatever words or value you want

If Mid({YourStringField},12,2) < 12 then
   "Morning"
Else
   "Afternoon"

mlmcc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 35171488
You need to change the first line of mlmcc's formula, because it's trying to compare a string (Mid) to a number (12).  But how you change it may depend on the format of the time in your field.  Does the hour have a leading 0 or space?  For example, would 8 AM be

2011-03-17 08:00:00
 or
2011-03-17  8:00:00    (with an extra space in front of the 8)
 or
2011-03-17 8:00:00

 If it has a leading 0 or space, you can just make 12 a string:

If Mid({YourStringField},12,2) < "12" then

 If it doesn't have a leading 0 or space, then a string comparison won't work, but converting the hour to a number probably will:

If Val (Mid({YourStringField},12,2)) < 12 then


 Technically, Val would also work if the hours included a leading 0 or space.  It's just not necessary in that case.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35171782
An even better way to test the hour would be this since there is no need to worry on the exact format

if Val(Split(Split({YourStringField},' ')[2],':')[1]) < 12 then
   "Morning"
Else
   "Afternoon"

mlmcc

0
 

Author Closing Comment

by:daiwhyte
ID: 35187842
Thank you both, this was a great help and the problem is now solved.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35187871
You're welcome.  Glad I could help.

 James
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month9 days, 16 hours left to enroll

762 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