Solved

Help with Formula

Posted on 2011-03-17
8
227 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 100

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 100

Accepted Solution

by:
mlmcc earned 250 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 250 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 100

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 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