SORT BY DAY

[Guest]

I have a query where I'm looking for new data added since a starting date the
user enters. The data in the table comes from a different program and the
format will not allow me to query off it easily.

The table that list the date as 6/8/2006 6:10:35 AM. In a query I have the
following to get this to 6/8/2006 Date: DatePart("m",[RXSTMADD]) & "/" &
DatePart("d",[RXSTMADD]) & "/" & DatePart("yyyy",[RXSTMADD])

In a 2nd query I pull in the 1st query and have a promt to ask for starting
date. I'm looking for all days greater than the starting date. The problem
is 6/2/2006 comes before 6/12/2006 when sorted.
Is it posible in the 1st query to get the date like 6/08/2006 or is there
another way to do this?

 

[Guest]

Hi there

you can use the DATEVALUE() function in access before you do the sort. So,
it will be something like:

SELECT DATEVALUE(DATEPART("m",[RXSTMADD]) ) & "/" & DATEPART("d",[RXSTMADD])
) & "/" & DATEPART("yyyy",[RXSTMADD]) ))
FROM Table1 ORDER BY 1;

or if you want to make it so that it will be in the mm/dd/yyyy format, you
can you

RIGHT('00' & DatePart("m",[RXSTMADD]), 2) & "/" &
RIGHT('00' & DatePart("d",[RXSTMADD]), 2) & "/" & DatePart("yyyy",[RXSTMADD])

Hope that's helpful.

Lucas

 

[MGFoster]

I have a query where I'm looking for new data added since a starting date the
user enters. The data in the table comes from a different program and the
format will not allow me to query off it easily.

The table that list the date as 6/8/2006 6:10:35 AM. In a query I have the
following to get this to 6/8/2006 Date: DatePart("m",[RXSTMADD]) & "/" &
DatePart("d",[RXSTMADD]) & "/" & DatePart("yyyy",[RXSTMADD])

In a 2nd query I pull in the 1st query and have a promt to ask for starting
date. I'm looking for all days greater than the starting date. The problem
is 6/2/2006 comes before 6/12/2006 when sorted.
Is it posible in the 1st query to get the date like 6/08/2006 or is there
another way to do this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

6/2/2006 should come before 6/12/2006 the 2nd day of June is before the
12th day of June.

To get the Date value from a DateTime value use the DateValue()
function. E.g.:

DateValue("6/8/2006 6:10:35 AM") yields the date 6/8/2006

All dates greater than the starting date is:

date_column > [start date]

6/12/2006 is greater than 6/2/2006.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJrupYechKqOuFEgEQIMUwCgvfwkeulaOieYZ96r/B7ych0f5mkAnjqW
w1T+wPg29zDzgwoN111rnriF
=nhgE
-----END PGP SIGNATURE-----

 

[Guest]

Lucas
I used the 2nd idea you suggested and it worked perfectly.
Thanks much
Tim

Hi there

you can use the DATEVALUE() function in access before you do the sort. So,
it will be something like:

SELECT DATEVALUE(DATEPART("m",[RXSTMADD]) ) & "/" & DATEPART("d",[RXSTMADD])
) & "/" & DATEPART("yyyy",[RXSTMADD]) ))
FROM Table1 ORDER BY 1;

or if you want to make it so that it will be in the mm/dd/yyyy format, you
can you

RIGHT('00' & DatePart("m",[RXSTMADD]), 2) & "/" &
RIGHT('00' & DatePart("d",[RXSTMADD]), 2) & "/" & DatePart("yyyy",[RXSTMADD])

Hope that's helpful.

Lucas

I have a query where I'm looking for new data added since a starting date the
user enters. The data in the table comes from a different program and the
format will not allow me to query off it easily.

The table that list the date as 6/8/2006 6:10:35 AM. In a query I have the
following to get this to 6/8/2006 Date: DatePart("m",[RXSTMADD]) & "/" &
DatePart("d",[RXSTMADD]) & "/" & DatePart("yyyy",[RXSTMADD])

In a 2nd query I pull in the 1st query and have a promt to ask for starting
date. I'm looking for all days greater than the starting date. The problem
is 6/2/2006 comes before 6/12/2006 when sorted.
Is it posible in the 1st query to get the date like 6/08/2006 or is there
another way to do this?