Solved

Help with Access query

Posted on 2013-12-30
22
182 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 39

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
 

Author Comment

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


in this case is working
0
 
LVL 39

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 39

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 39

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
DevOps Toolchain Recommendations

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

 
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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now