date serial in access query

[trixxnixon]

i want to use the date from a date field that also has the time stamp and calculate it within an access query.

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.

Somefield: Workingdays([Startfield],[Completefield]) 

would there be any other way to get the desired output?

[NeoPa]

Use the DateValue() function Trixx.

[trixxnixon]

so would i apply the date value function directly to the "Date submitted" field? within the expression?

DIH: Workingdays2([datevalue(Date submitted)],[Date Completed])

[NeoPa]

Pretty well, yes. But the brackets ([]) go around the field name only :

DIH: Workingdays2(DateValue([Date Submitted]),[Date Completed])

[trixxnixon]

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.

[trixxnixon]

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.

if isnull(sumofdays)=true then
DIH =Workingdays2(DateValue([Date Submitted]),[Date Completed])  
else
Workingdays2(DateValue([Date Submitted]),[Date Completed])- ([Sumofdays})
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

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?


Thanks!

[NeoPa]

I suspect you want something like :

DIH = Workingdays2(DateValue([Date Submitted]),[Date Completed]) - Nz([SumOfDays], 0)

[trixxnixon]

@NeoPa
like a charm. i had never seen the nz thing before,
thanks for the awesome help.

[NeoPa]

You're welcome of course :)