Solved

Help with Access query

Posted on 2013-12-30
22
183 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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