Using "MM" Date Format in Query When Data is "M" format

Quote:

Quote:

I'm thinking this is easy but can't get it. I have a table with
following:

Table1
Date 1/1/2007

Table2
Type 0107 (This is MMYY of above)

So I'm having trouble using a query to turn the date from Table 1 into
the Type from Table 2. I know when the dates are >=10, I can just use
Left,2 and Right, 2. I don't want to have to put a long condition
that says "If month <10, then use '0'&Left,1, else use Left,2"
Though I'd think this will work, it's a little awkward. Setting the
format type in the query only changes the display, doesn't let me
query off of it. (i.e. format mm/dd/yyyy displays 01/01/2007 but I
can't use Left,2 and get "01" from that.)

Thanks!
Darrell

Quote:

Quote:

The dates are coming in as dates. The other are text values. No my
question isn't what you describe. And now I actually want to add a
follow up.

Table 1
Name Effective Expiration
Bob 1/1/2006 9/30/2006

I have a table that shows all of the months "Bob" was effective. The
table looks like:

Bob 0106 (text)
Bob 0206
Bob 0306

So this is why I want to append the leading 0 to the month. My follow
up though is:

Previously to do the above, I had 9 queries which said:
1. If effective >=1/1/2006 and <=1/31/2006 then ="0106"
2. If effective >=2/1/2006 and <=2/28/2006 then ="0206", etc.

I have to cycle through 3 years and add a new query every time we move
to a different month. There must be an easier way to either cycle
through or set this up. The output is later used in another query. I
need unique values for the other query for each month and year person
is eligible.

Quote: