Using the Case Expression in Access

[Guest]

Hi Guys.

I was wondering if someone could help me. I am currently importing files
from excel into an access database. However, a lot of the data is old and has
to be cleaned up. I am going to have to do this now and anytime I import new
data into the DB.

I am trying to create a macro that will go through the tables and update the
necessary information. I added a RunSQL task to take care some of the edit. I
figure a bunch update statements will do the trick. However I find the RunSQL
task to be rather limited in what it can do... and more importantly I cannot
get it to use a CASE statement.

I am trying to do:

UPDATE testHours
SET GL_month = CASE
WHEN GL_month='1/1/2005' THEN '1'
WHEN GL_month='2/1/2005' THEN '2'
WHEN GL_month='3/1/2005' THEN '3'
WHEN GL_month='4/1/2005' THEN '4'
WHEN GL_month='5/1/2005' THEN '5'
WHEN GL_month='6/1/2005' THEN '6'
END
----------------------

When I run this, I get a missing operator error, but I know the syntax is
correct... Can anyone help me figure this out? Or is there a better way to be
doing this?

I appreciate any assistance you could offer.

Thanks,

-Michael

 

[Pat Hartman\(MVP\)]

I don't recognize the syntax so it must not be standard Access syntax. In
Access, you would use the IIf() function since the select Case isn't
available in SQL.

In any event, if the column is presently defined as a date data type, you
cannot change its value to a single text character. Dates are not stored
internally as text strings. They are stored as double precision numbers.

To convert a date to just a month, add a new column (define it as integer)
to the table and call it GL_Month (first rename the existing column to
GL_Date).
Then the SQL would be:

UPDATE testHours
SET GL_month = Month(GL_Date);

Once the new month field has been populated, you can delete the date field.
I'm pretty sure that the Month() function is a generic SQL function. If it
isn't, then use what ever function you can to extract the month from a date
field.

 

[Douglas J Steele]

A literal translation would be:

UPDATE testHours
SET GL_month = SWITCH(
GL_month='1/1/2005', '1'
GL_month='2/1/2005', '2'
GL_month='3/1/2005', '3'
GL_month='4/1/2005', '4'
GL_month='5/1/2005', '5'
GL_month='6/1/2005', '6'
)

This assumes that GL_month is a text field (if it's a date field, you won't
be able to put a string 1, 2, etc into it).

If so, there's an easier way:

UPDATE testHours
SET GL_month = Left(GL_month, InStr(GL_month, "/") -1)

In other words, look for the first / in the string, and take everything
that's in front of it.