DateSerial Problem

[Juan Schwartz]

I am using the following to convert a string to a date.

[string] = "10/14/2006"

DateSerial(Right([string],4),Mid([string],4,2),Left([string],2))

The problem is it's returning 2/10/2007 which doesn't make sense. If I
echo out the substrings I'm using in the date serial command, they are
correct and if I put those numbers in the DateSerial function, I get
the correct date. Am I missing something here?

Jimmy

 

[Juan Schwartz]

Here's the test query I'm using.

SELECT CSP.CSPName, CSP.CSGID, "Between " &
DateSerial(Right([string],4),Mid([string],4,2),Left([string],2)) & "
And " &
DateSerial(Right([string],4),Mid([string],4,2),Left([string],2)) AS
Test
FROM CSP
WHERE (((CSP.CSGID)="KMJ"));

I am just trying to echo the dates out since they aren't working.
That's the only purpose of this query.

 

[Allen Browne]

The problem could be due to String() being a function name.

Could use use CDate() instead of parsing and manipulating with DateSerial,
e.g.:
SELECT CSP.CSPName, CSP.CSGID, CVDate(DSP.[string]) AS TheDate FROM ...

 

[David F Cox]

14 months added onto 2006 gives the 2nd month of 2007, and it is returning
the date in USA format.

Alas, you get what you ask for.

 

[Juan Schwartz]

Thanks. I'm new to Access and mainly use PHP/MySQL/MSSQL.

14 months added onto 2006 gives the 2nd month of 2007, and it is returning
the date in USA format.

Alas, you get what you ask for.

I am using the following to convert a string to a date.

[string] = "10/14/2006"

DateSerial(Right([string],4),Mid([string],4,2),Left([string],2))

The problem is it's returning 2/10/2007 which doesn't make sense. If I
echo out the substrings I'm using in the date serial command, they are
correct and if I put those numbers in the DateSerial function, I get
the correct date. Am I missing something here?

Jimmy

 

[John Spencer]

Your problem is that you need to give dateSerial the year, the month, and
the day in that order. You are giving it the Year, the day, and the month
in that order.

Try
DateSerial(Right([string],4),Left([string],2),Mid([string],4,2))