[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Help with Access query

How to add 12 hours to  existing Date
0
rfedorov
Asked:
rfedorov
  • 10
  • 5
  • 5
  • +1
3 Solutions
 
als315Commented:
dateadd("h", 12, Yourdate)
0
 
rfedorovAuthor Commented:
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
 
Simon BallCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
rfedorovAuthor Commented:
DateAdd("h",12,Format([StartDate],"mm/dd/yyyy"))


in this case is working
0
 
als315Commented:
Can you upload sample table? I can't reproduce your case:
dateadd
0
 
rfedorovAuthor Commented:
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
 
als315Commented:
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
 
rfedorovAuthor Commented:
To: als315
TestA.accdb
0
 
als315Commented:
Addate is working as expected:
queryresult
0
 
rfedorovAuthor Commented:
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
 
Dale FyeCommented:
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
 
Simon BallCommented:
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
 
rfedorovAuthor Commented:
none of the last comments are working,
0
 
rfedorovAuthor Commented:
IIf(Format([StartDate],"Short Time")< #12:00 PM#,[StartDate],DateAdd("h",12,StartDateA))

it gives me an error in the bolded part
0
 
Dale FyeCommented:
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
 
rfedorovAuthor Commented:
Error message
0
 
Dale FyeCommented:
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
 
rfedorovAuthor Commented:
I have database submitted, Access 2010
there is no problem with StartTime: TimeValue([StartDate]) , the problem with IIF statement
0
 
Dale FyeCommented:
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
 
rfedorovAuthor Commented:
I am really sorry for asking, but have you tried in my database, i have the same error
0
 
Dale FyeCommented:
That code works fine for me (A2007)  using your database.
0
 
als315Commented:
Dale's code is working in your sample DB (Access 2010)
TestA.accdb
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 10
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now