By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

date serial in access query

P: 98
i want to use the date from a date field that also has the time stamp and calculate it within an access query.
Expand|Select|Wrap|Line Numbers
  1. WHERE Day(DateSerial(<d>, <m>, <y>))=<d>
would it be possible to only include the date serial dd/mm/yy of the startfield, and leave the time stamp out using the function above in the function below.
Expand|Select|Wrap|Line Numbers
  1. Somefield: Workingdays([Startfield],[Completefield]) 
would there be any other way to get the desired output?
Feb 18 '09 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 15k+
P: 31,419
Use the DateValue() function Trixx.
Feb 18 '09 #2

P: 98
so would i apply the date value function directly to the "Date submitted" field? within the expression?
Expand|Select|Wrap|Line Numbers
  1. DIH: Workingdays2([datevalue(Date submitted)],[Date Completed])
Feb 18 '09 #3

Expert Mod 15k+
P: 31,419
Pretty well, yes. But the brackets ([]) go around the field name only :
Expand|Select|Wrap|Line Numbers
  1. DIH: Workingdays2(DateValue([Date Submitted]),[Date Completed])
Feb 18 '09 #4

P: 98
with out you guys, there are alot of people(including me) who would not get their work done and would lose their jobs.

for a site with this much knowledge and the people who have the most knowledge are so willing to help, to not be add supported is amazing.
its like creating art for the purpose of creating art.

bravo friends.

i send people in my company here all the time.
Feb 18 '09 #5

P: 98
i have to introduce a new field now, but i need it to calculate based on a condition being true. the new field is Sumofdays.
the calculation should not calculate if sumofdays
here is my logic. but i know this syntax is way off.
Expand|Select|Wrap|Line Numbers
  1. if isnull(sumofdays)=true then
  2. DIH =Workingdays2(DateValue([Date Submitted]),[Date Completed])  
  3. else
  4. Workingdays2(DateValue([Date Submitted]),[Date Completed])- ([Sumofdays})
  5. end if
when i try to use an iif statement, even a simple one i get a

here is what im using in the query design view

Expand|Select|Wrap|Line Numbers
  1. NDIH: IIf(IsNull([sumofdays]),Workingdays2(DateValue([Date Submitted]),[Date Completed]),Workingdays2(DateValue([Date Submitted]),[Date Completed])-[(sumofdays])
but i keep getting the paramater input request. what might i be doing wrong?

Feb 19 '09 #6

Expert Mod 15k+
P: 31,419
I suspect you want something like :
Expand|Select|Wrap|Line Numbers
  1. DIH = Workingdays2(DateValue([Date Submitted]),[Date Completed]) - Nz([SumOfDays], 0)
Feb 19 '09 #7

P: 98
like a charm. i had never seen the nz thing before,
thanks for the awesome help.
Feb 19 '09 #8

Expert Mod 15k+
P: 31,419
You're welcome of course :)
Feb 19 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.