Getting min of date after formatting 1

[nithink]

Hi,
Very new to MS-Access.
I'm trying to format the date and get the min of date but
its returning a wrong value.

select min(Format(emp.emp_date-DatePart("d",emp_date),'mm/dd/yyyy')) AS emp_date from emp ;

The values in the table are,

10/1/1992
11/1/1992
12/1/1992
1/1/1993
2/1/1993

I'm expecting a value after formatting is 09/30/1992. But its returning 01/31/1993. I think it gets the min of
the "day" part alone. Can you pls help out with the syntax ?
Thanks.

 

[dhookom]

Is there a reason you are attempting to get the min of a text representation of the date? I rarely format any data in a query.
select min(emp.emp_date-DatePart("d",emp_date)) AS emp_date from emp ;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Yes Duane, I'm actually creating an extract(.txt) and the date format they need is mm/dd/yyyy. And by running ur query I'm able to get the correct date but its not formatted and when I try to export to a .txt it comes with
the time also. For ex,
it is like 9/30/1992 0:00:00 but the result should be
09/30/1992.

Thanks,

 

[dhookom]

Try this:
select Top 1 Format(emp.emp_date-DatePart("d",emp_date),'mm/dd/yyyy') AS emp_date
from emp
ORDER BY emp_date-DatePart("d",emp_date);

Otherwise, use my first query as the source for another query that does the formatting.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Duane, Its giving an error saying Syntax Error (missing operator) in query expression 'Top 1...'
Actually the query I'm running has more than 20 fields in which one of the field is this date field which needs fomratting.
Your second option of using ur first query as source for formatting query, how to do that ? Can you pls let me know.
Thanks much.

 

[lespaul]

have you tried using DateValue?


SELECT Min(DateValue(emp_date)) from emp

Leslie

 

[nithink]

I tried Datevalue too. The thing is I need to do formatting after I get the min date. Thats where I'm not able to do.
Here's the whole sql which I'm using to create the extract(.txt). And it is the r_date field which is giving problem. Can you pls help me out in this ? Very much new to Access.

SELECT IIf(IsNumeric([arc].[acct_num]),IIf(Val([arc].[acct_num])>=100000,"20-","22-"),"22-") & [arc].[acct_num] & [sr].[id_1] AS id,
min(Format(arc.r_date-DatePart("d",r_date),'mm/dd/yyyy')) AS p_date,
IIf(IsNull([sr].[id_2]),NZ(Switch([arc.t_cd]="TOTA","TOTAL",
[arc.t_cd]="TOTM","TOTM",
[arc.t_cd]="EQTY","EQTYALL",
[arc.t_cd]="FIXED","FIXEDALL"),[arc.t_cd]),[sr.id_2]) AS p_class,
'F' AS p_curr_code,'N/A' AS s_cur,'N' AS p_cash
Exp(1)^Sum((Log([arc]![ret_pct]/100+1)))*100-100 AS cgtor
FROM d_acct AS da, acct_category AS arc, s_rpt AS sr
WHERE arc.acct_num=[da].[acct_num]
AND sr.acct_num=[da].[acct_num]
AND arc.t_cd = sr.t_cd
AND arc.acct_num = sr.acct_num
AND sr.id_1 IS NOT NULL
group by IIf(IsNumeric([arc].[acct_num]),IIf(Val([arc].[acct_num])>=100000,"20-","22-"),"22-") & [arc].[acct_num] & [sr].[id_1],
IIf(IsNull([sr].[id_2]),NZ(Switch([arc.t_cd]="TOTA","TOTAL",
[arc.t_cd]="TOTM","TOTM",
[arc.t_cd]="EQTY","EQTYALL",
[arc.t_cd]="FIXED","FIXEDALL"),[arc.t_cd]),[sr.id_2])

 

[dhookom]

I would change the date part of the original query and save the query as qFirstQuery. Then create a new query as noted below based on qFirstQuery.

SELECT IIf(IsNumeric([arc].[acct_num]),IIf(Val([arc].[acct_num])>=100000,"20-","22-"),"22-") & [arc].[acct_num] & [sr].[id_1] AS id,
[red]min(arc.r_date-DatePart("d",r_date)) AS p_realdate[/red],
IIf(IsNull([sr].[id_2]),NZ(Switch([arc.t_cd]="TOTA","TOTAL",[arc.t_cd]="TOTM","TOTM", [arc.t_cd]="EQTY","EQTYALL",[arc.t_cd]="FIXED","FIXEDALL"),[arc.t_cd]),[sr.id_2]) AS p_class,
'F' AS p_curr_code,'N/A' AS s_cur,'N' AS p_cash
Exp(1)^Sum((Log([arc]![ret_pct]/100+1)))*100-100 AS cgtor
FROM d_acct AS da, acct_category AS arc, s_rpt AS sr
WHERE arc.acct_num=[da].[acct_num]
AND sr.acct_num=[da].[acct_num]
AND arc.t_cd = sr.t_cd
AND arc.acct_num = sr.acct_num
AND sr.id_1 IS NOT NULL
group by IIf(IsNumeric([arc].[acct_num]),IIf(Val([arc].[acct_num])>=100000,"20-","22-"),"22-") & [arc].[acct_num] & [sr].[id_1],
IIf(IsNull([sr].[id_2]),NZ(Switch([arc.t_cd]="TOTA","TOTAL",
[arc.t_cd]="TOTM","TOTM",
[arc.t_cd]="EQTY","EQTYALL",
[arc.t_cd]="FIXED","FIXEDALL"),[arc.t_cd]),[sr.id_2])

SELECT ID, Format(p_realdate,"mm/dd/yyyy") as p_date, p_curr_code, s_cur, p_cash, cgtor
FROM qFirstQuery;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Thanks Duane. Yes ur a Access MVP and deserve a star.
I didnt know that we can select from that existing query.
Lot of things to learn.

And thanks to Leslie too for ur input.
-Nithink