CASE statement in ACCESS VBA Help?

[blakey2]

Hello all,

I have read many posts and am only slightly less confused than before. Could someone please give me a definitave answer as to wether or not SQL CASE statments can be used within VBA (Access) using the JET SQL engine?

I have a table in my db which I imported. This table contains a field 'PostCode' (zip code for the non australians).

With the knowledge that all Australian States have a specific postcode range, I would like to create a new field in the table, and enter a text value indicating the state.

Here is my code:

Public Function generateStateColumn(tableName As String)

    'tableName is the name of the table to which the field
    'is to be added & populated

    Dim db As DAO.Database
    Dim db As DAO.Database
    Dim strSQL As String
        
    Set db = CurrentDb

    'Generate a new column to store the generated state
    'in
    strSQL = "ALTER TABLE " & tableName & " " _
             "ADD COLUMN genSTATE TEXT(5) ;"
    
    DoCmd.RunSQL strSQL

    ' Now generate codes for the states
    ' NT  : 0800 - 0899
    ' ACT : 2000 - 2999 -- Same as NSW so omit!
    ' NSW : 2000 - 2999
    ' VIC : 3000 - 3999
    ' QLD : 4000 - 4999
    ' SA  : 5000 - 5999
    ' WA  : 6000 - 6999
    ' TAS : 7000 - 7999


    strSQL = "UPDATE " & tableName & " " & _
             "CASE " & _
             " WHEN Postcode BETWEEN 0800 AND 0899 THEN" & _
             " SET genSTATE= 'NT' " & _
             " WHEN Postcode BETWEEN 2000 AND 2999 THEN" & _
             " SET genSTATE= 'NSW' " & _
             .
             .
             . 

             " ELSE SET genSTATE = 'NULL' " & _
             " END ;"

    Debug.Print strSQL
                
    DoCmd.RunSQL strSQL
                
End Function

I realise I can do away with the 'BETWEEN' statements (assuming condition matches only in first instance), but they are not the problem (not at this stage...)

Is my CASE synax correct??

After reading the other posts suggesting that CASE statements were indeed not supported, I tried to use IFF statements. eg:

strSQL = " Select " & _
         "IIF(PostCode < 0900, NT, NULL) as genSTATE, " & _
         "FROM " & tableName & " ;"

But I have no idea how to traverse for all instances, yet place the results into the singular 'genPOST' field.

Apologies for the extended ramble, I am very new to access and all things VBA, and the syntax and lack of documentation are driving me bananas

Thanks in advance!

 

[blakey2]

Well I worked out how to use the switch statement and my code is here:

         ' Code to connect to db, declarations etc.


           strSQL = " Select switch " & _
                    "(PostCode < 0900, 'NT', " & _
                    "PostCode < 2999, 'NSW', " & _
                    "PostCode < 3999, 'VIC', " & _
                    "PostCode < 4999, 'QLD', " & _
                    "PostCode < 5999, 'SA', " & _
                    "PostCode < 6999, 'WA', " & _
                    "PostCode < 7999, 'TAS') " & _
                    "AS genSTATE " & _
                    "FROM " & tableName & " ;"

    Debug.Print strSQL
                
    DoCmd.RunSQL strSQL

Only Problem is that when i run this code from within my function, I get an error:
" Runtime error '2342': "
" A RunSQL action requires an argument consisting of"
" an SQL statement "

But when I cut and paste the strSQL statement from the immediate window into a query it works fine -- Although it is only displaying a single 'genSTATE' column.

I believe I read somewhere that the RunSQL method (?) only works with certain types of action SQL statements. Is it possible that this is my error?

Thanks.

 

[KenReay]

Hi

docmd.runSQL only works with action queries, which do not return a recordset

If you thing about it, using docmd.runsql "SELECT...", where would the returned record set go?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[blakey2]

Hey KenReay,

Thanks for the reply, but to ask a silly question, "How or what do I alter to achieve the outcome?"

As mentioned earlier I have never used VBA or Access until 3 days ago. I have a very limited understanding of alternate methods.

Cheers.

 

[KenReay]

Provided you include a reference to DAO library, you could say

Dim Db as DAO.Database
Dim rs as DAO.recordset

set Db = currentDB()
set Rs = db.Openrecordset(strSQL)
if rs.recordcount > 0 then
.blah blah

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[blakey2]

Hello All,

If anyone ever gets to this point and wonders what the outcome was, click on this link:

http://www.tek-tips.com/viewthread.cfm?qid=942709&page=1

Thankyou to all who took the time to answer a newbie's question!

- blakey2.