Datevalue

Jul 24, 2007

In Excel there is a function called Datevalue() that will return the dates computer serial number. See below example:
Formula Description (Result)
=DATEVALUE("8/22/2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("22-AUG-2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("2008/02/23") Serial number of the text date, using the 1900 date system (39501)
=DATEVALUE("5-JUL") Serial number of the text date, using the 1900 date system, and assuming the computer's built-in clock is set to 2008 (39634)

Is there a function in Access that will do the same thing?

View Replies


ADVERTISEMENT

Syntax Problem With DateValue() In Query

Apr 24, 2008

I have a select query with a number of expressions and I cannot seem to get the expressions to work. Access keeps giving me syntax error on the following code:


SELECT Run.Test_Case,

Sum(IIf(DateValue([Attempted_Actual]) < DateValue([Attempted_Actual]),[Points],0))/Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0))*100 AS ActualAttempted,
Sum(IIf(DateValue([Completed_Actual]) < DateValue([Completed_Actual]),[Points],0))/Sum(IIf(Not IsNull([Completed_Actual]),[Points],0))*100 AS ActualCompleted,
Sum(IIf(DateValue([Verified_Actual]) < DateValue([Verified_Actual]),[Points],0))/Sum(IIf(Not IsNull([Verified_Actual]),[Points],0))*100 AS ActualClosed,


Sum(IIf(DateValue([Attempted_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Attempted_Planned]),[Points],0))*100 AS PlannedAttempted,
Sum(IIf(DateValue([Completed_Planned]) < DateValue(Date()]),[Points],0))/Sum(IIf(Not IsNull([Completed_Planned]),[Points],0))*100 AS PlannedCompleted,
Sum(IIf(DateValue([Verified_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Verified_Planned]),[Points],0))*100 AS PlannedClosed


FROM Run INNER JOIN Task ON Run.Run=Task.Group
GROUP BY Run.Test_Case;
HAVING (((Run.Test_Case)=IIf(IsNull([Forms]![Status]![ComboStatusTestCase]),[Test_Case],[Forms]![Status]![ComboStatusTestCase])));



I am sort of confused on where the error could because it just says there is a syntax error. My assumption is that I am using DateValue() in the wrong context. If anyone has any pointers or sees my error please let me know.

Thanks,

View 3 Replies View Related

Queries :: Using DateValue Function With Query

Aug 1, 2014

How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.

Code:
SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate

[Code] ....

View 8 Replies View Related

Queries :: Using Datevalue As Parameter From A Form

Apr 23, 2013

In my QBE, I have a field call [startDate]

In my criteria, I am passing a parameter from a form.

Currently, I am using: DateValue([forms]![myForm]![txtStartDate])

From myFORM, the value of the text box is 3/1/2013

When I run the query, it runs as expected and returns all records with the 3/1/2013 dates.

But now I want to make it where if the parameter is null.

How do I change that criteria if that parameter I am passing is null from my form's textbox? I want it to return all records that have a null entry in the [startDate].

View 5 Replies View Related

Datevalue( ' Thedate') When 'thedate' Is Empty

Feb 2, 2007

Hi guys , i've got a webpage where i'm essentially joining five tables with different data fields in.

I've done this by creating a tempary table putting all the information in fields that are like and then displaying it.

Thing is to insert the dates of various things i've used the datevalue('thedate') in my insert statement , however if the field 'thedate' has nothing in it I get an error message data type missmatch.

I'm pretty sure this is because the datevalue function it trying to do datevalue('') .

Is there anyway I could do a IfIsnull("", Datevalue('thedate'))

within the SQL statement of an ASP page

i'm not too sure of the syntax but basically i want to be able to do datevalue('thedate') if there is a value for 'thedate' but if there isn't do nothing....

hope that makes sense ...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved