How to format a field in the select statement?

[Ai_Jun_Zhang]

Hi!

I have a table called myTable and it contains two fields Year and Month. Both are type of integer.

Now in my select statement, I like to combine these two fields in to one and name it as period and it will have the format, for example 200501, 200502, ..., 200512. How can I do that in Access query statement?

I know how to do this in SQL Server 2000. That is:

Select Convert(varchar(4), [Year]) + right('00' + cast([Month] as varchar(2)), 2) as Period from myTable

How can I do that in Access Query statement?

Please advise.

Aijun.

 

[mresann]

Since your format is a rather simplified Text string format using date constants, try:

For current Date:
Period: cstr(Year(Date())) & Format(Month(Date()), "00")

For explicit date (use "#" as the delimiter):
Period: cstr(Year(#April 17, 2005#) & Format(Month(#April 17, 2005#), "00")

For date in query field:
Period: cstr(Year([MyTable].[MyDate]) & Format(Month([MyTable].[MyDate]), "00")

Hope this helps!

 

[Ai_Jun_Zhang]

How to do that in the select statment?

Hi!

Thank you very much for your reply. I think the code you give is vb code. I would like know how can I combine columns in the select statement with the format that I like to have?

Please advise

Aijun.

 

[Ai_Jun_Zhang]

I found the solution.

It is
SELECT Year & format(Month, '00')
FROM myTable;

 

[mresann]

Sorry. I'm sorry, I was using the Design View format, not the SQL statement format.

When you open your query in Design View, you will know what I mean.