Date -Data Type

[D]

Hi:

Can you please tell me how to change the Binary data type for date to
Date/Time data type.

I have the below qry. (make table) where I enter the report dates (start and
end); the data type for these two dates in the resulting table is Binary; in
another qry I am using those two fields [RS] and [RE] for calculations and I
don not what to enter again the dates; If I am using the fields like [RS] and
[RE] itself does not work?

Thanks,

Dan

1: SELECT [Rpt_Start] AS RS, [Rpt_End] AS RE, tbl_Database.PTFOLIO,
tbl_Database.DEALNO, tbl_Database.USD, tbl_Database.GBP, tbl_Database.EUR,
tbl_Database.HKD, tbl_Database.JPY, tbl_Database.SGD, tbl_Database.CHF,
tbl_Database.AUD, tbl_Database.CAD, tbl_Database.ORG, tbl_Database.COUNTR_NM,
tbl_Database.TRADE_DATE, tbl_Database.START_DATE, tbl_Database.MATURITY_DATE,
tbl_Database.CCY, tbl_Database.CCY_AGAINST, tbl_Database.TRANS_AMT,
tbl_Database.VS_AMT, tbl_Database.FX_SPOT_RATE, tbl_Database.FX_FWD_RATE,
IIf([trade_date]<=[Rpt_Start] And [maturity_date] Between [Rpt_Start] And
[Rpt_End],"A") AS A, IIf(Right([dealno],1)="X",IIf([trade_date] Between
[Rpt_start] And [Rpt_end],IIf([maturity_date] Between [Rpt_start] And
[Rpt_end],"B1"))) AS B1, IIf(Right([dealno],1)<>"X" And [trade_date] Between
[Rpt_start] And [Rpt_end] And [maturity_date] Between [Rpt_start] And
[Rpt_end],"B2") AS B2, IIf(Right([dealno],1)<>"X",IIf([trade_date] Between
[Rpt_start] And [Rpt_end],IIf([maturity_date] Between [Rpt_start] And
[Rpt_end],"B3"))) AS B3, IIf(Right([dealno],1)="X",IIf([trade_date] Between
[Rpt_start] And [Rpt_end],IIf([maturity_date] Between [Rpt_start] And
[Rpt_end],"C"))) AS C, IIf(Right([dealno],1)<>"X" And [trade_date] Between
[Rpt_Start] And [Rpt_End] And
[maturity_date]>=[Rpt_End],"D1",IIf(Right([dealno],1)<>"X" And
[trade_date]=[maturity_date],"D1")) AS D1,
IIf([maturity_date]>[Rpt_end],"D2") AS D2, IIf([maturity_date] Between
[Rpt_Start] And [Rpt_End],"Yes") AS Matured, IIf(Right([dealno],1)="X","L 1")
AS Leg, IIf(Right([dealno],1)="X","P") AS Pairs INTO Test
FROM tbl_Database
GROUP BY [Rpt_Start], [Rpt_End], tbl_Database.PTFOLIO, tbl_Database.DEALNO,
tbl_Database.USD, tbl_Database.GBP, tbl_Database.EUR, tbl_Database.HKD,
tbl_Database.JPY, tbl_Database.SGD, tbl_Database.CHF, tbl_Database.AUD,
tbl_Database.CAD, tbl_Database.ORG, tbl_Database.COUNTR_NM,
tbl_Database.TRADE_DATE, tbl_Database.START_DATE, tbl_Database.MATURITY_DATE,
tbl_Database.CCY, tbl_Database.CCY_AGAINST, tbl_Database.TRANS_AMT,
tbl_Database.VS_AMT, tbl_Database.FX_SPOT_RATE, tbl_Database.FX_FWD_RATE,
IIf([trade_date]<=[Rpt_Start] And [maturity_date] Between [Rpt_Start] And
[Rpt_End],"A"), IIf(Right([dealno],1)="X",IIf([trade_date] Between
[Rpt_start] And [Rpt_end],IIf([maturity_date] Between [Rpt_start] And
[Rpt_end],"B1"))), IIf(Right([dealno],1)<>"X" And [trade_date] Between
[Rpt_start] And [Rpt_end] And [maturity_date] Between [Rpt_start] And
[Rpt_end],"B2"), IIf(Right([dealno],1)<>"X",IIf([trade_date] Between
[Rpt_start] And [Rpt_end],IIf([maturity_date] Between [Rpt_start] And
[Rpt_end],"B3"))), IIf(Right([dealno],1)="X",IIf([trade_date] Between
[Rpt_start] And [Rpt_end],IIf([maturity_date] Between [Rpt_start] And
[Rpt_end],"C"))), IIf(Right([dealno],1)<>"X" And [trade_date] Between
[Rpt_Start] And [Rpt_End] And
[maturity_date]>=[Rpt_End],"D1",IIf(Right([dealno],1)<>"X" And
[trade_date]=[maturity_date],"D1")), IIf([maturity_date]>[Rpt_end],"D2"),
IIf([maturity_date] Between [Rpt_Start] And [Rpt_End],"Yes"),
IIf(Right([dealno],1)="X","L 1"), IIf(Right([dealno],1)="X","P"),
IIf([Dealno] Like "FX*X","X"), IIf(IIf(Right([dealno],1)="X","L 1")="Leg 1"
And IIf([Dealno] Like "FX*X","X")="X",(IIf(Right([dealno],1)="X","L 1")="Leg
2")), tbl_Database.P_SUBTP
HAVING (((tbl_Database.ORG) In ("99130","90126","91685")) AND
((tbl_Database.MATURITY_DATE)>=[Rpt_Start]) AND ((tbl_Database.P_SUBTP) Like
"FWD"))
ORDER BY tbl_Database.DEALNO;
***
2: UPDATE Test SET Test.B1 = "B1"
WHERE (((Test.TRADE_DATE) Between [sd] And [ed]) AND ((Test.MATURITY_DATE)
Between [sd] And [ed]) AND ((Test.Leg)="L 1"));

 

[John Spencer]

Are Rpt_Start and Rpt_End parameters? If so, try declaring them at the
beginning of the QUERY.

PARAMETERS [Rpt_Start] DateTime, [Rpt_End] DateTime;
SELECT [Rpt_Start] AS RS
, [Rpt_End] AS RE

Or as an alternative
SELECT CDate([Rpt_Start]) AS RS
, CDate([Rpt_End]) AS RE

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi:

Can you please tell me how to change the Binary data type for date to
Date/Time data type.

I have the below qry. (make table) where I enter the report dates (start
and
end); the data type for these two dates in the resulting table is Binary;
in
another qry I am using those two fields [RS] and [RE] for calculations and
I
don not what to enter again the dates; If I am using the fields like [RS]
and
[RE] itself does not work?

***
2: UPDATE Test SET Test.B1 = "B1"
WHERE (((Test.TRADE_DATE) Between [sd] And [ed]) AND ((Test.MATURITY_DATE)
Between [sd] And [ed]) AND ((Test.Leg)="L 1"));

 

[D]

Thanks a LOT, John!!!

The first way worked!

Dan

Are Rpt_Start and Rpt_End parameters? If so, try declaring them at the
beginning of the QUERY.

PARAMETERS [Rpt_Start] DateTime, [Rpt_End] DateTime;
SELECT [Rpt_Start] AS RS
, [Rpt_End] AS RE

Or as an alternative
SELECT CDate([Rpt_Start]) AS RS
, CDate([Rpt_End]) AS RE

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi:

Can you please tell me how to change the Binary data type for date to
Date/Time data type.

I have the below qry. (make table) where I enter the report dates (start
and
end); the data type for these two dates in the resulting table is Binary;
in
another qry I am using those two fields [RS] and [RE] for calculations and
I
don not what to enter again the dates; If I am using the fields like [RS]
and
[RE] itself does not work?

***
2: UPDATE Test SET Test.B1 = "B1"
WHERE (((Test.TRADE_DATE) Between [sd] And [ed]) AND ((Test.MATURITY_DATE)
Between [sd] And [ed]) AND ((Test.Leg)="L 1"));