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
Solved

Help with Formula

Posted on 2011-03-17
8
226 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
  • 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
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.

 
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 34

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 34

Expert Comment

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

 James
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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