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 Access query

Posted on 2013-12-30
22
185 Views
Last Modified: 2013-12-30
How to add 12 hours to  existing Date
0
Comment
Question by:rfedorov
  • 10
  • 5
  • 5
  • +1
22 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39746205
dateadd("h", 12, Yourdate)
0
 

Author Comment

by:rfedorov
ID: 39746214
Thanks for your fast respond, it is not so easy.  I did not finish the question yet...


My StartDateA  returns: 12/10/2013 9:01:00 AM  or 12/10/2013 8:37:00 AM
when i use the formula  New: DateAdd("h",12,[StartDateA])--- I am getting 12/10/2013 12:00:00 PM regardless
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39746224
Looks like its being c0nverted from Datetime to date... then adding the 12 hours.

Is this in a query where startdateA is already an expression?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:rfedorov
ID: 39746225
DateAdd("h",12,Format([StartDate],"mm/dd/yyyy"))


in this case is working
0
 
LVL 40

Expert Comment

by:als315
ID: 39746230
Can you upload sample table? I can't reproduce your case:
dateadd
0
 

Author Comment

by:rfedorov
ID: 39746240
Nope, still does not work,  
SELECT
DataExtr.StartDate al Old,
DateAdd("h",12,Format([StartDate],"mm/dd/yyyy")) AS New
FROM DataExtr

Old                                        New
1/23/2013 3:44:00 PM          1/23/2013 12:00:00 PM
5/14/2013 8:46:00 AM          5/14/2013 12:00:00 PM
0
 
LVL 40

Expert Comment

by:als315
ID: 39746245
Format([StartDate],"mm/dd/yyyy") removes time from your date/value, so it is expected result.
DateAdd("h",12,[StartDate]) AS New
should work
0
 

Author Comment

by:rfedorov
ID: 39746269
To: als315
TestA.accdb
0
 
LVL 40

Expert Comment

by:als315
ID: 39746273
Addate is working as expected:
queryresult
0
 

Author Comment

by:rfedorov
ID: 39746275
I see, thank you, but i want that, if time is less then 12:00PM it shold be StartDate, it
time is bigger or equal 12:00PM it shold be StartDate+12 hours
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39746329
Now I'm confused.   If TimeValue([StartDate]) < #12:00#, then you want [StartDate], but if it is after noon , you want to add 12 hours?

New: iif(TimeValue([StartDate]) <= #12:00#, [StartDate], DateAdd("h", 12, [StartDate]))
0
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 50 total points
ID: 39746337
you can get time by subtracting the integer of the date from itself:

[StartDate] - Int([StartDate])

not sure if you want to work with it as a decimal or a time number...you can probably do

=iff( format("hh",[StartDate] - Int([StartDate])) > 12,DateAdd("h",12,[StartDate]),[StartDate])
0
 

Author Comment

by:rfedorov
ID: 39746350
none of the last comments are working,
0
 

Author Comment

by:rfedorov
ID: 39746358
IIf(Format([StartDate],"Short Time")< #12:00 PM#,[StartDate],DateAdd("h",12,StartDateA))

it gives me an error in the bolded part
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39746366
Once you use the Format() function, the return value will be a string, not a date.  That is why I used the TimeValue() function.

 iif(TimeValue([StartDate]) <= #12:00#, [StartDate], DateAdd("h", 12, [StartDate]))
0
 

Author Comment

by:rfedorov
ID: 39746373
Error message
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39746393
That syntax worked for me, what version of Access are you using?

Work your way up to it to figure out where the problem is.

StartTime: TimeValue([StartDate])
0
 

Author Comment

by:rfedorov
ID: 39746413
I have database submitted, Access 2010
there is no problem with StartTime: TimeValue([StartDate]) , the problem with IIF statement
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39746420
You will need to give that computed field in your query a name

New:  iif(TimeValue([StartDate]) <= #12:00#, [StartDate], DateAdd("h", 12, [StartDate]))
0
 

Author Comment

by:rfedorov
ID: 39746434
I am really sorry for asking, but have you tried in my database, i have the same error
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39746470
That code works fine for me (A2007)  using your database.
0
 
LVL 40

Accepted Solution

by:
als315 earned 400 total points
ID: 39746505
Dale's code is working in your sample DB (Access 2010)
TestA.accdb
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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