Scripting a New Table based on Existing Data

[javaman12]

Hey everyone. This may be a little long winded so bear with me.

I want to generate a table that pulls data from an existing one in access. In the existing table there are 5 columns:

Part
Description
Flag
Range Low
Range High

The Range columns either contain a numeric range (ex Low is 100, High is 300), or a single number in both columns that denotes an unspecified range (ex. 5X00). The letter "X" in this column means it can be any specific number.

The new table I want to create needs to pull all the data from each of those columns, except that for the Range, I need to output each individual number within that range. So for example, if the Low is 100 and the High is 200, it would have to output 100, 101, 102, 103.....200.

I created some code in VB that works for the most part, except it runs into a problem when it finds the character "X" in the Range column. In that case, it only outputs the first number and eliminates all the numbers including and after the X.

So for example if the Range is 5X000, my code would just output 5.

This is what I have so far:



Public Function getData(source As String, target As String) As Boolean
Dim db As Database
Dim sSQL As String
Dim i As Double
Dim k As Integer

Set db = CurrentDb()

DoCmd.SetWarnings False
DTable (target)

sSQL = "CREATE TABLE " & target & " (Part char(50), Description char(50), Flag char(50), Range char(50));"
DoCmd.RunSQL sSQL

Set rs = db.OpenRecordset("Select [Part], [Description], [Flag], [Range_Low], [Range_High] FROM " & source & ";")
If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst

k = 1

While k < 500
k = k + 1

If Not rs!Segment5_Low Like "X" Then
i = Val(rs!Range_Low)
While i <= Val(rs!Range_high)
sSQL = "INSERT INTO " & target & " VALUES ('" & rs![Part] & "', '" & rs![Description] & "', '" & rs![Flag] & "', " & i & ");"
Debug.Print sSQL
DoCmd.RunSQL sSQL
i = i + 1

Wend

End If
rs.MoveNext
Wend
End If


getData = True
DoCmd.SetWarnings True
End Function




Can someone help me out with this?

[CreganTur]

Welcome to the forum- always good to have new members!

If this is a one time thing, then I would skip the DAO and just use the Make Table Query from Access' query builder. You can choose fields and set criteria like any normal query, but it allows you to create a new table based on your specifications.


Your Code:

What data type are your range fields? If it contains the character "x" then I'm guessing it's a string data type. Your variable i is double . The error is that "X" is not a valid character for a double data type- that's why it's not getting pulled in correctly.

Also, I don't reccomend using the While...Wend looping structure- it's only included in VBA for backwards compatability. Take a look at my Looping Structure article for more info on the other structures available for VBA; one may suit your purposes better.

[javaman12]

Thanks for the quick reply. I got that part of my code working. However now I want to not include the rows that have a Description with a NULL value. So for that I inputted the following:



While k < 500
k = k + 1

If rs!Description = NULL Then
rs.MoveNext

Else



and then immediately afterwards would be my If Not rs!Segment5_Low Like "X" Then statement

Access accepts the code, but for some reason it ignores my rs.MoveNext command and still puts the record in anyway. How can I fix that?