Convert number into text in query 2

[molly]

I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".

We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.

FiscalMonth and FiscalYear are both Long Integer number fields in the table.

Query field concatenated:
MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]

criteria line:
Between [Month-Year START DATE] And [Month-Year END DATE]

However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?

I assume that a Dash sign between the month and year are fine.
Thanks

 

[dhookom]

This is another good reason why you should never use parameter prompt queries. IMHO, they are not good user interface faq701-6763.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[LonnieJohnson]

Create a form to force the user to put in what you want.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[PHV]

In order to have the Between operator work properly use YearMonth instead ...

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

 

[molly]

Thanks. You are both correct. This was a bad question that my boss asked me. I shouldn't have given in to him.
Molly