Help with Access query

How to add 12 hours to  existing Date
Roman FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
dateadd("h", 12, Yourdate)
Roman FAuthor 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
Simon BallChief information OfficerCommented:
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?
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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


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

it gives me an error in the bolded part
Dale FyeOwner, Dev-Soln LLCCommented:
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]))
Roman FAuthor Commented:
Error message
Dale FyeOwner, Dev-Soln LLCCommented:
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])
Roman FAuthor Commented:
I have database submitted, Access 2010
there is no problem with StartTime: TimeValue([StartDate]) , the problem with IIF statement
Dale FyeOwner, Dev-Soln LLCCommented:
You will need to give that computed field in your query a name

New:  iif(TimeValue([StartDate]) <= #12:00#, [StartDate], DateAdd("h", 12, [StartDate]))
Roman FAuthor Commented:
I am really sorry for asking, but have you tried in my database, i have the same error
Dale FyeOwner, Dev-Soln LLCCommented:
That code works fine for me (A2007)  using your database.
als315Commented:
Dale's code is working in your sample DB (Access 2010)
TestA.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.