Add new field to existing table? 2

[blakey2]

Hello,

I am struggling to grasp what exactly recordsets are? My problem is that I have a SQL statement which runs fine as a query, but when I run it with the doCmd.runSQL method, I get an error:
" Runtime error '2342': "
" A RunSQL action requires an argument consisting of"
" an SQL statement "

in an earlier thread :

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

It was suggested to me that I use a recordset. I have read many similar threads but cannot get it to work.

Public Function generateStateColumn(tableName As String)

    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    
    strSQL = " Select switch " & _
                    "(PostCode < 0900, 'NT', " & _
                    "PostCode < 2999, 'NSW', " & _
                    "PostCode < 3999, 'VIC', " & _
                    "PostCode < 4999, 'QLD', " & _
                    "PostCode < 5999, 'SA', " & _
                    "PostCode < 6999, 'WA', " & _
                    "PostCode < 7999, 'TAS', " & _
                    "PostCode > 7999, 'INVALID' ) " & _
                    "AS genSTATE " & _
                    "FROM " & tableName & " ;"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)

    '*********** Something goes in here ??? *******
    '*********** to make it work ??? **************
    'If (Not rs.BOF And Not rs.EOF) Then
    '    rs.Fields("genSTATE").Value
    'End If
    '**********************************************
  
    rs.Close
    Set rs = Nothing

    Debug.Print strSQL

End Function

Essentially all I would like to do is add an extra column/field to the table, and based on the value in the 'PostCode' field assign it a shortcode. The SQL query works fine by itself, the problem I have is executing it within VBA.

Any help/thoughts are greatly appreciated. Thanks!

 

[dhookom]

It isn't clear wht you are trying to do. Do you want to update a field value in a table or just return a value based on your Switch() function.

Is your PostCode field text or numeric? Your value 0900 suggests text but you don't have it in quotes.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hey Duane,

Sorry if I was unclear on what I was trying to do. Initially I posted a thread regarding CASE statements in VBA/Jet SQL under the link:

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

(It is explained pretty thoroughly there)

Essentially though I have a table, which I imported from excel. All good there. This table has a field 'PostCode' (ZipCode for the non Australians). this is a number field but due to its nature it will always have four digits (hence the leading zero 0900). Every commonwealth state in Australia has a shortcode, ie NSW for New South Wales, VIC for Victoria etc.

Now, every state has a postcode range:eg: 3000 to 3999 for VIC.

When I run my "Select switch ...." sql statement as a query it works! But the problem is that when I try and run it from within a VBA function/module the doCmd.runSQL method will not execute it as it is not an action statement.

It was suggested to me by KenReay (hope I spelt ur name right Ken ) that I need to use a recordset.

What I am hoping for is that someone can tell me how I can get the VBA code to execute the SQL statement stored as strSQL. I am a relative newbie to all things access and VBA so I apologise if this is a stupid thing to ask.

Thanks for taking the time to answer, sorry about the ramble but I hope I explained myself a bit better.

- blakey2.

 

[dhookom]

I still don't understand what you want to do with your resulting records. Do you want to use it as a form's record source or simply display it in datasheet view or what? When you set out on this journey, where did you want to go?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hey Duane,

I'll try and explain myself:

I have a table. It has fields/columns. I would like to add another column/field. I would like to populate this field/column based on data from the same table. I would like to do this for each record in the table. This field is called 'State' (or genState in my previous code).

For example my table looks like this:

tblCompanyInfo:

Address City PostCode
5 Raymond St Melbourne 3000
29 Burra Rd Toowomba 2453
149 Nick St Coburg 3058

Now to this table I want to add another column which lists the state in which the company is located.

tblCompanyInfo:

Address City PostCode State
5 Raymond St Melbourne 3000 VIC
29 Burra Rd Toowomba 2453 NSW
149 Nick St Coburg 3058 VIC

I physically want to add this 'State' field to my table:
ie: tblCompanyInfo( Address, City, PostCode)
becomes:
tblCompanyInfo( Address, City, PostCode, State)

Every state has a postcode range of 999. ie Victoria (VIC) lies in the range 3000 to 3999. NSW is between 2000 and 2999.

I worte a SQL query which will, when run seperately as a query, correctly generate a column and list all of the 'State' codes. It does not however store this extra 'State' field and data in my initial 'tblCompanyInfo table.

My problem lies in my lack of knowledge as to how I can embed my SQL query in VBA and get it to create and polulate the tblCompanyInfo. -- given that the DoCmd.runSQL method does not work with a select statement.

Question: Do I actually need to even worry about a record set? I ask this as if my sql query were an action query then I could simply run DoCmd.runSQL and this would alter/update/whatever my tblCompanyInfo.

I am new to Access and VBA. I have been reading many other posts in the meantime and it would appear that if I use recordsets, then I can ignore all SQL and traverse my recordset (tblCompanyInfo) to achieve this.

Conversely, I also read that SQL is a much faster/better method of working with relational db s.

Thankyou for taking the time to read this.

-blakey2.

 

[PHV]

1) In table design view add the State field
2) Use an action query like this:
UPDATE tblCompanyInfo SET State = Switch(PostCode....)

If you want an all VBA solution, then take a look at the ALTER TABLE sql instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

If these ranges will [red]always be valid[/red] then I would create a function as below. Then, you would never need to store the state anywhere since you have a function that returns the state. However, if you want to store the state, use an update like:

   Dim strSQL as String
   strSQL = "UPDATE tblCompanyInfo SET State = GetState(PostCode)"
   DoCmd.RunSQL strSQL

Public Function GetState(pvarPostCode As Variant) As String
    Dim lngPostCode As Long
    'convert to numeric
    If Not IsNumeric(pvarPostCode) Then
        lngPostCode = 9999
     Else
        lngPostCode = Val(Nz(pvarPostCode, 9999))
    End If
    
    Select Case lngPostCode
        Case Is < 900
            GetState = "NT"
        Case Is < 2999
            GetState = "NSW"
        Case Is < 3999
            GetState = "VIC"
        Case Is < 4999
            GetState = "QLD"
        Case Is < 5999
            GetState = "SA"
        Case Is < 6999
            GetState = "WA"
        Case Is < 7999
            GetState = "TAS"
        Case Is > 7999
            GetState = "INVALID"
    End Select

End Function

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PHV]

Duane, what happen if lngPostCode=7999 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Ooops. I just copied and pasted from the original post. Ask Blakey2, not me

I think it should be against the law to build expressions such as IIf(), Switch(),... with more than 3 possible return values. These are too difficult to maintain and should be either a user-defined function or modeled in data in a lookup table.

The final Case should be:

        Case Else
            GetState = "INVALID"

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Thanks Duane n PH!

Initially I was hoping to use the CASE functionality of SQL, but all that I could gather on the subject is that JET SQL does not support it. If you are curious feel free to read and add to an earlier post of mine:

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

Cheers - blakey2.

 

[JBaileys]

It is my understanding that the ms jet engine does not support the use of the alter table statement. Have you been able to get the statement to work?

 

[PHV]

JetSQL 4.0 supports some DDL instructions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244