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

veaux

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

Jan 5 '07 #1

Rick Brandt

ve***@aol.com wrote:

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

Are you storing text or numeric data that "represents" Date data or are
these actually DateTime types formatted differently. If the former, bad
idea. If the latter then the dates are already the same because formatting
doesn't affect how dates are stored.

If your actual question is "how do I display a date as MMYY then use a
format property of "mmyy" or use the format function...

=Format([DateField], "mmyy")

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jan 5 '07 #2

veaux

Rick Brandt wrote:

ve***@aol.com wrote:

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

Are you storing text or numeric data that "represents" Date data or are
these actually DateTime types formatted differently. If the former, bad
idea. If the latter then the dates are already the same because formatting
doesn't affect how dates are stored.

If your actual question is "how do I display a date as MMYY then use a
format property of "mmyy" or use the format function...

=Format([DateField], "mmyy")

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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.

Jan 5 '07 #3

Rick Brandt

ve***@aol.com wrote:

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.

But, what is the purpose of the 9 queries? Are you importing the data with
the mmyy format into tables with real dates? Going in the other direction?
Are you trying to create a query that joins the two tables on the differing
date fields?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jan 5 '07 #4

veaux

Rick Brandt wrote:

ve***@aol.com wrote:

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.

But, what is the purpose of the 9 queries? Are you importing the data with
the mmyy format into tables with real dates? Going in the other direction?
Are you trying to create a query that joins the two tables on the differing
date fields?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Going in other direction. The values with dates are going into tables
with text fields "mmyy".

Jan 5 '07 #5

Rick Brandt

ve***@aol.com wrote:

>
Going in other direction. The values with dates are going into tables
with text fields "mmyy".

And you are doing this with an append query? If so you just use the Format
fucntion like I posted earlier. Instead of IIf() use...

Format(DateField, "mmyy")

That will return a string in the format 0107 for this month.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jan 5 '07 #6