Convert number to text in query

[penwood]

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

 

[ajetrumpet]

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

However, i get other crazy time periods.

Probably because the program is reading a subtraction sign.

Maybe on the Query field box, I need extra coding to convert the number to text?

From what I can interpret of your words, I personally think you are trying to make the program bend in ways that is not practical, but impossible. Sounds like you have two different fields, one for month, and one for year. This is not part of a "normal" setup. If you want to view dates (or any other kind of data for that matter) in a custom format of your choosing, the data has to be setup correctly to compliment that request initially. I don't think you have done this, so you probably shouldn't go any further without correcting it.

If you offer more explanation of what you really want, and why you want it, maybe someone could help you correct the setup problem you have (if there is one)...

 

[pbaldy]

I agree with Adam; you're trying to fit a square peg into a round hole. I suspect the problem is that you're doing a string comparison instead of the numeric comparison you think you're doing. I also agree that you should use an actual date field if you want to do date comparisons, but failing that, if you format your field and criteria like:

200611

your query might work (and don't forget to format single digit months with a leading zero).

 

[penwood]

Yes, i will abandon the idea

You are correct. I was only shooting for this answer because of my boss, who knows little Access, but has opionions. He still thinks we are wrong. So forget him. You are right. Penwood

 

[pbaldy]

My feeling, and I'm lucky enough to have a boss that agrees, is that the boss/users should determine the "what", and the developer determines the "how". IOW, they determine what they need the application to do, and you figure out how to accomplish it.

 

[neileg]

You need to upgrade your Boss. Boss 2.1 has these limitations but you can either apply SP2 to patch this, or preferably upgrade to 3.0