Improve company productivity with a Business Account.Sign Up

x
?
Solved

Help with Access query

Posted on 2013-12-30
22
Medium Priority
?
196 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 41

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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

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


in this case is working
0
 
LVL 41

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 41

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 41

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 50

Expert Comment

by:Dale Fye
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 200 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 50

Expert Comment

by:Dale Fye
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 50

Expert Comment

by:Dale Fye
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 50

Assisted Solution

by:Dale Fye
Dale Fye earned 200 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 50

Expert Comment

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

Accepted Solution

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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

584 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