Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Access query

Posted on 2013-12-30
22
Medium Priority
?
189 Views
Last Modified: 2013-12-30
How to add 12 hours to  existing Date
0
Comment
Question by:rfedorov
[X]
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
  • 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

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 48

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 48

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 48

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 48

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 48

Expert Comment

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

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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