Order by help with DateSerial function

[eerich]

Hello everyone,

I'm having trouble retrieving and sorting by the date column in a query that I've designed to convert julian dates. My query is as follows:

SELECT USER, EVENTDESCR, (DateSerial(1900+(SHUPMJ\1000),1,SHUPMJ Mod 1000)) AS ConvertedDate
FROM HISTORY
WHERE (DateSerial(1900+(SHUPMJ\1000),1,SHUPMJ Mod 1000))>=[Enter the beginning period date - mm/dd/yy]
ORDER BY 3, 1;

While the query runs, it doesnt produce the desired results, in that I receive all of the dates regardless of what is entered in the parameter prompt; i.e., if I enter '10/01/11', I will get the full date range.

Any help is appreciated.

 

[eerich]

One correction, need help with the WHERE clause, not the Order By.

thanks.

 

[PHV]

Replace this:
10/01/11
with this:
2011-10-01

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

Hi PHV,

I tried changing the input as you suggested, but it didn't work. Would a subquery be required for this type of function?

Your help is appreciated.

 

[eerich]

Any other ideas on this query so that when the parameter is entered, it will just pull the specified criteria?

 

[dhookom]

I expect this might be a data type conversion issue. I would set the Query->Parameters:
[Enter the beginning period date - mm/dd/yy] Date/Time

PARAMETERS [Enter the beginning period date - mm/dd/yy] DateTime;
SELECT USER, EVENTDESCR, (DateSerial(1900+(SHUPMJ\1000),1,SHUPMJ Mod 1000)) AS ConvertedDate
FROM HISTORY
WHERE (DateSerial(1900+(SHUPMJ\1000),1,SHUPMJ Mod 1000))>=[Enter the beginning period date - mm/dd/yy]
ORDER BY 3, 1;

BTW: IMO parameter prompts are never a good user interface. Always use controls on forms.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

^Dhookom,

that worked!

thanks a bunch.