Format A Field In A SQL UNION Query

[Guest]

Could anybody please instruct me on how to format a field in a SQL UNION
query? My SQL statement is like the following:

SELECT
#1/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
Union Select
#2/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
etc.......;

I want to format the period field like yyyy mm (2005 01 for 1/1/05 and 2005
02 for 2/2/05). How do I edit the SQL statement to accomplish this?

Thanks,
Bill Horton

 

[Guest]

SELECT Format(#1/1/2005#,"yyyy mm") As Period,Q_UNION_AR&Sales1.*
etc.

 

[Guest]

Dennis,

Thanks for the response. I did try your suggestion and it does format the
field the way that I want. However, it changes it to a String data type and
now my other queries that work off of this query and join on the Period
(date) field do not work because of a type mismatch.

Is there a way to format the Period the field the way that I want but still
have it remain as a date data type.

When I checked the Help files on the format function it does say that it
returns a String value so I won't be able to use this function.

Thanks,
Bill Horton

SELECT Format(#1/1/2005#,"yyyy mm") As Period,Q_UNION_AR&Sales1.*
etc.

Could anybody please instruct me on how to format a field in a SQL UNION
query? My SQL statement is like the following:

SELECT
#1/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
Union Select
#2/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
etc.......;

I want to format the period field like yyyy mm (2005 01 for 1/1/05 and 2005
02 for 2/2/05). How do I edit the SQL statement to accomplish this?

Thanks,
Bill Horton

 

[Guest]

I don't see how you can do what you want. Could you keep your Period as it
was and have an extra column formatted as yyyy mm ?

Dennis,

Thanks for the response. I did try your suggestion and it does format the
field the way that I want. However, it changes it to a String data type and
now my other queries that work off of this query and join on the Period
(date) field do not work because of a type mismatch.

Is there a way to format the Period the field the way that I want but still
have it remain as a date data type.

When I checked the Help files on the format function it does say that it
returns a String value so I won't be able to use this function.

Thanks,
Bill Horton

SELECT Format(#1/1/2005#,"yyyy mm") As Period,Q_UNION_AR&Sales1.*
etc.

Could anybody please instruct me on how to format a field in a SQL UNION
query? My SQL statement is like the following:

SELECT
#1/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
Union Select
#2/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
etc.......;

I want to format the period field like yyyy mm (2005 01 for 1/1/05 and 2005
02 for 2/2/05). How do I edit the SQL statement to accomplish this?

Thanks,
Bill Horton

 

[Guest]

Thanks Dennis. That is basically what I wound up doing. I was hoping there
was a way to avoid that but maybe there isn't one. Thanks for your help.

Bill Horton

I don't see how you can do what you want. Could you keep your Period as it
was and have an extra column formatted as yyyy mm ?

Dennis,

Thanks for the response. I did try your suggestion and it does format the
field the way that I want. However, it changes it to a String data type and
now my other queries that work off of this query and join on the Period
(date) field do not work because of a type mismatch.

Is there a way to format the Period the field the way that I want but still
have it remain as a date data type.

When I checked the Help files on the format function it does say that it
returns a String value so I won't be able to use this function.

Thanks,
Bill Horton

SELECT Format(#1/1/2005#,"yyyy mm") As Period,Q_UNION_AR&Sales1.*
etc.


:

Could anybody please instruct me on how to format a field in a SQL UNION
query? My SQL statement is like the following:

SELECT
#1/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
Union Select
#2/1/2005# As Period,Q_UNION_AR&Sales1.*
FROM [Q_UNION_AR&Sales1]
etc.......;

I want to format the period field like yyyy mm (2005 01 for 1/1/05 and 2005
02 for 2/2/05). How do I edit the SQL statement to accomplish this?

Thanks,
Bill Horton