Parameter Query

D

D

Hi:

Can you please tell me how do I change the date format to enter just yyyymm
in the parameter [Rpt_date] in the below qry; now I have to enter mmddyyyy.

Thanks,

Dan

PARAMETERS [Rpt_Date] DateTime;
SELECT Table1.P_SUBTP, Table1.BOOK, Table1.SOURCE_GL, Table1.HEDGE_TP,
Table1.POS, Table1.POS_SUBCAT, Table1.TAX_CLS, Table1.RE_PL, Table1.PTFOLIO,
Table1.DEALNO, Table1.USD, Table1.GBP, Table1.EUR, Table1.HKD, Table1.JPY,
Table1.SGD, Table1.CHF, Table1.AUD, Table1.ILS, Table1.CAD, Table1.ACTIVE_CD,
Table1.DEAL_TP, Table1.INSTR_TP, Table1.TRANS_TP, Table1.BOA, Table1.ORG,
Table1.GL_ACCT, Table1.S_ACCT, Table1.PRODUCT, Table1.COUNTR_NO,
Table1.COUNTR_NM, Table1.TRADE_DATE, Table1.START_DATE,
Table1.LT_MATURITY_DATE, Table1.MATURITY_DATE
FROM Table1
WHERE (((Table1.TRADE_DATE)<=[Rpt_Date]) AND
((Table1.MATURITY_DATE)>[Rpt_Date]));
 
D

Douglas J. Steele

WHERE Format(Table1.TRADE_DATE, "yyyymm")<=[Rpt_Date]
AND Format(Table1.MATURITY_DATE, "yyyymm")>[Rpt_Date];
 
D

D

Hi Doug:

Thanks! it is not working; I think it is about the first line of the query?

Dan

Douglas J. Steele said:
WHERE Format(Table1.TRADE_DATE, "yyyymm")<=[Rpt_Date]
AND Format(Table1.MATURITY_DATE, "yyyymm")>[Rpt_Date];


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


D said:
Hi:

Can you please tell me how do I change the date format to enter just
yyyymm
in the parameter [Rpt_date] in the below qry; now I have to enter
mmddyyyy.

Thanks,

Dan

PARAMETERS [Rpt_Date] DateTime;
SELECT Table1.P_SUBTP, Table1.BOOK, Table1.SOURCE_GL, Table1.HEDGE_TP,
Table1.POS, Table1.POS_SUBCAT, Table1.TAX_CLS, Table1.RE_PL,
Table1.PTFOLIO,
Table1.DEALNO, Table1.USD, Table1.GBP, Table1.EUR, Table1.HKD, Table1.JPY,
Table1.SGD, Table1.CHF, Table1.AUD, Table1.ILS, Table1.CAD,
Table1.ACTIVE_CD,
Table1.DEAL_TP, Table1.INSTR_TP, Table1.TRANS_TP, Table1.BOA, Table1.ORG,
Table1.GL_ACCT, Table1.S_ACCT, Table1.PRODUCT, Table1.COUNTR_NO,
Table1.COUNTR_NM, Table1.TRADE_DATE, Table1.START_DATE,
Table1.LT_MATURITY_DATE, Table1.MATURITY_DATE
FROM Table1
WHERE (((Table1.TRADE_DATE)<=[Rpt_Date]) AND
((Table1.MATURITY_DATE)>[Rpt_Date]));
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, I missed the parameter declaration. Since a Date value must be a
proper date, you have to change the declaration to Text:

PARAMETERS [Rpt_Date] Text;


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


D said:
Hi Doug:

Thanks! it is not working; I think it is about the first line of the
query?

Dan

Douglas J. Steele said:
WHERE Format(Table1.TRADE_DATE, "yyyymm")<=[Rpt_Date]
AND Format(Table1.MATURITY_DATE, "yyyymm")>[Rpt_Date];


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


D said:
Hi:

Can you please tell me how do I change the date format to enter just
yyyymm
in the parameter [Rpt_date] in the below qry; now I have to enter
mmddyyyy.

Thanks,

Dan

PARAMETERS [Rpt_Date] DateTime;
SELECT Table1.P_SUBTP, Table1.BOOK, Table1.SOURCE_GL, Table1.HEDGE_TP,
Table1.POS, Table1.POS_SUBCAT, Table1.TAX_CLS, Table1.RE_PL,
Table1.PTFOLIO,
Table1.DEALNO, Table1.USD, Table1.GBP, Table1.EUR, Table1.HKD,
Table1.JPY,
Table1.SGD, Table1.CHF, Table1.AUD, Table1.ILS, Table1.CAD,
Table1.ACTIVE_CD,
Table1.DEAL_TP, Table1.INSTR_TP, Table1.TRANS_TP, Table1.BOA,
Table1.ORG,
Table1.GL_ACCT, Table1.S_ACCT, Table1.PRODUCT, Table1.COUNTR_NO,
Table1.COUNTR_NM, Table1.TRADE_DATE, Table1.START_DATE,
Table1.LT_MATURITY_DATE, Table1.MATURITY_DATE
FROM Table1
WHERE (((Table1.TRADE_DATE)<=[Rpt_Date]) AND
((Table1.MATURITY_DATE)>[Rpt_Date]));
Click to expand...
Click to expand...
Click to expand...
 
D

D

Hi Doug:

Thanks a lot! I will try this too! I do not get te right result; can you
please try?

Dan

Here is the table:

ID org trade_date maturity_date amt
1 1 6/1/2008 6/5/2099 100
2 2 6/13/2008 3/5/2008 200
3 3 6/9/2008 4/5/2008 300
4 4 6/8/2008 9/5/2008 400

***

qry:


parameters [rpt_date] text;
SELECT Table1.ID, Table1.org, Table1.trade_date, Table1.maturity_date,
Table1.amt
FROM Table1
WHERE Format(Table1.TRADE_DATE, "yyyymm")<=[Rpt_Date]
AND Format(Table1.MATURITY_DATE, "yyyymm")>[Rpt_Date];



Douglas J. Steele said:
Sorry, I missed the parameter declaration. Since a Date value must be a
proper date, you have to change the declaration to Text:

PARAMETERS [Rpt_Date] Text;


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


D said:
Hi Doug:

Thanks! it is not working; I think it is about the first line of the
query?

Dan

Douglas J. Steele said:
WHERE Format(Table1.TRADE_DATE, "yyyymm")<=[Rpt_Date]
AND Format(Table1.MATURITY_DATE, "yyyymm")>[Rpt_Date];


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi:

Can you please tell me how do I change the date format to enter just
yyyymm
in the parameter [Rpt_date] in the below qry; now I have to enter
mmddyyyy.

Thanks,

Dan

PARAMETERS [Rpt_Date] DateTime;
SELECT Table1.P_SUBTP, Table1.BOOK, Table1.SOURCE_GL, Table1.HEDGE_TP,
Table1.POS, Table1.POS_SUBCAT, Table1.TAX_CLS, Table1.RE_PL,
Table1.PTFOLIO,
Table1.DEALNO, Table1.USD, Table1.GBP, Table1.EUR, Table1.HKD,
Table1.JPY,
Table1.SGD, Table1.CHF, Table1.AUD, Table1.ILS, Table1.CAD,
Table1.ACTIVE_CD,
Table1.DEAL_TP, Table1.INSTR_TP, Table1.TRANS_TP, Table1.BOA,
Table1.ORG,
Table1.GL_ACCT, Table1.S_ACCT, Table1.PRODUCT, Table1.COUNTR_NO,
Table1.COUNTR_NM, Table1.TRADE_DATE, Table1.START_DATE,
Table1.LT_MATURITY_DATE, Table1.MATURITY_DATE
FROM Table1
WHERE (((Table1.TRADE_DATE)<=[Rpt_Date]) AND
((Table1.MATURITY_DATE)>[Rpt_Date]));
Click to expand...
Click to expand...
Click to expand...
 
D

D

Sorry Doug I think now it is fine;

Thank you very much!

Dan

Douglas J. Steele said:
Sorry, I missed the parameter declaration. Since a Date value must be a
proper date, you have to change the declaration to Text:

PARAMETERS [Rpt_Date] Text;


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


D said:
Hi Doug:

Thanks! it is not working; I think it is about the first line of the
query?

Dan

Douglas J. Steele said:
WHERE Format(Table1.TRADE_DATE, "yyyymm")<=[Rpt_Date]
AND Format(Table1.MATURITY_DATE, "yyyymm")>[Rpt_Date];


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi:

Can you please tell me how do I change the date format to enter just
yyyymm
in the parameter [Rpt_date] in the below qry; now I have to enter
mmddyyyy.

Thanks,

Dan

PARAMETERS [Rpt_Date] DateTime;
SELECT Table1.P_SUBTP, Table1.BOOK, Table1.SOURCE_GL, Table1.HEDGE_TP,
Table1.POS, Table1.POS_SUBCAT, Table1.TAX_CLS, Table1.RE_PL,
Table1.PTFOLIO,
Table1.DEALNO, Table1.USD, Table1.GBP, Table1.EUR, Table1.HKD,
Table1.JPY,
Table1.SGD, Table1.CHF, Table1.AUD, Table1.ILS, Table1.CAD,
Table1.ACTIVE_CD,
Table1.DEAL_TP, Table1.INSTR_TP, Table1.TRANS_TP, Table1.BOA,
Table1.ORG,
Table1.GL_ACCT, Table1.S_ACCT, Table1.PRODUCT, Table1.COUNTR_NO,
Table1.COUNTR_NM, Table1.TRADE_DATE, Table1.START_DATE,
Table1.LT_MATURITY_DATE, Table1.MATURITY_DATE
FROM Table1
WHERE (((Table1.TRADE_DATE)<=[Rpt_Date]) AND
((Table1.MATURITY_DATE)>[Rpt_Date]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorry! Parameter query / Again 16

Top