dynamic sql statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am trying to do something like this
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")
temp = "GROUP" & num
queryString = Eval("rstGlobalStateCertifications![" & temp & "]")
im trying to access the value in the record's by a certain field or column
but part of the column name i need to be dynamic(num is a variable) but im
not figuring out how to do this, can anyone help me out?
 
Try this
' If the variable supplied and it's number
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName =" & MyVariable)

' If the variable supplied and it's string
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName ='" & MyVariable & "'")

' If also the field name supplied then
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where " & MyFieldName & " = " & MyVariable)
 
AmyNeedsHelp said:
i am trying to do something like this
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")
temp = "GROUP" & num
queryString = Eval("rstGlobalStateCertifications![" & temp & "]")
im trying to access the value in the record's by a certain field or column
but part of the column name i need to be dynamic(num is a variable) but im
not figuring out how to do this, can anyone help me out?
Click to expand...


Instead of Eval, use this kind of reerence:

queryString = rstGlobalStateCertifications(temp)

which is shorthand for

queryString = rstGlobalStateCertifications.Fields(temp)
 
well what im trying to do is take data from a table and put it into a
different table and so im selecting a record from the "oldTable" and then the
way they set up that table is messed up. they have 47 yes/no columns, ex.
GROUP2, GROUP3, GROUP5, GROUP90, so im trying to dynamically get the column
name and then get the value in that column for that current record

Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")

rstGlobalStateCertifications.MoveFirst

'Loop through the GlobalStateCertifications table records
Do Until rstGlobalStateCertifications.EOF

'check "group" fields for -1 and add to providerstates table
For intNum = 0 To 46
num = numArray(intNum)

temp = "GROUP" & num

queryString = "rstGlobalStateCertifications![" & temp & "]"

Next

rstGlobalStateCertifications.MoveNext
Loop

so im trying to figure out how to evaluate, test it to see if it has a 0 or
-1 in it
is there a way to do this or am i going to have to just make 47 different if
statements to test this (if rstGlobalStateCertifications![GROUP2] = -1
then)???
please help if ya can

Ofer said:
Try this
' If the variable supplied and it's number
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName =" & MyVariable)

' If the variable supplied and it's string
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName ='" & MyVariable & "'")

' If also the field name supplied then
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where " & MyFieldName & " = " & MyVariable)


AmyNeedsHelp said:
i am trying to do something like this
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")
temp = "GROUP" & num
queryString = Eval("rstGlobalStateCertifications![" & temp & "]")
im trying to access the value in the record's by a certain field or column
but part of the column name i need to be dynamic(num is a variable) but im
not figuring out how to do this, can anyone help me out?
Click to expand...
Click to expand...
 
Sounds like you need to use the Fields collection of the recordset. Once
you open the recordset using OpenRecordset, you can access the various
fields and fieldnames using the fields collection as in

crm.fields.count 'number of fields in the recordset
crm.fields(0).name 'name of the first field in the recordset. the fields
'are numbered 0 to count-1
crm.fields(0)
crm.fields(0).value'accesses the specific value in the field
well what im trying to do is take data from a table and put it into a
different table and so im selecting a record from the "oldTable" and then the
way they set up that table is messed up. they have 47 yes/no columns, ex.
GROUP2, GROUP3, GROUP5, GROUP90, so im trying to dynamically get the column
name and then get the value in that column for that current record

Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")

rstGlobalStateCertifications.MoveFirst

'Loop through the GlobalStateCertifications table records
Do Until rstGlobalStateCertifications.EOF

'check "group" fields for -1 and add to providerstates table
For intNum = 0 To 46
num = numArray(intNum)

temp = "GROUP" & num

queryString = "rstGlobalStateCertifications![" & temp & "]"

Next

rstGlobalStateCertifications.MoveNext
Loop

so im trying to figure out how to evaluate, test it to see if it has a 0 or
-1 in it
is there a way to do this or am i going to have to just make 47 different if
statements to test this (if rstGlobalStateCertifications![GROUP2] = -1
then)???
please help if ya can

:

Try this
' If the variable supplied and it's number
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName =" & MyVariable)

' If the variable supplied and it's string
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName ='" & MyVariable & "'")

' If also the field name supplied then
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where " & MyFieldName & " = " & MyVariable)


:

i am trying to do something like this
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")
temp = "GROUP" & num
queryString = Eval("rstGlobalStateCertifications![" & temp & "]")
im trying to access the value in the record's by a certain field or column
but part of the column name i need to be dynamic(num is a variable) but im
not figuring out how to do this, can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
thanks so much for all the ideas, i have it working now!!!!!!!!

David C. Holley said:
Sounds like you need to use the Fields collection of the recordset. Once
you open the recordset using OpenRecordset, you can access the various
fields and fieldnames using the fields collection as in

crm.fields.count 'number of fields in the recordset
crm.fields(0).name 'name of the first field in the recordset. the fields
'are numbered 0 to count-1
crm.fields(0)
crm.fields(0).value'accesses the specific value in the field
well what im trying to do is take data from a table and put it into a
different table and so im selecting a record from the "oldTable" and then the
way they set up that table is messed up. they have 47 yes/no columns, ex.
GROUP2, GROUP3, GROUP5, GROUP90, so im trying to dynamically get the column
name and then get the value in that column for that current record

Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")

rstGlobalStateCertifications.MoveFirst

'Loop through the GlobalStateCertifications table records
Do Until rstGlobalStateCertifications.EOF

'check "group" fields for -1 and add to providerstates table
For intNum = 0 To 46
num = numArray(intNum)

temp = "GROUP" & num

queryString = "rstGlobalStateCertifications![" & temp & "]"

Next

rstGlobalStateCertifications.MoveNext
Loop

so im trying to figure out how to evaluate, test it to see if it has a 0 or
-1 in it
is there a way to do this or am i going to have to just make 47 different if
statements to test this (if rstGlobalStateCertifications![GROUP2] = -1
then)???
please help if ya can

:

Try this
' If the variable supplied and it's number
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName =" & MyVariable)

' If the variable supplied and it's string
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where MyFieldName ='" & MyVariable & "'")

' If also the field name supplied then
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications Where " & MyFieldName & " = " & MyVariable)


:


i am trying to do something like this
Set rstGlobalStateCertifications = crm.OpenRecordset("SELECT * FROM
GlobalStateCertifications")
temp = "GROUP" & num
queryString = Eval("rstGlobalStateCertifications![" & temp & "]")
im trying to access the value in the record's by a certain field or column
but part of the column name i need to be dynamic(num is a variable) but im
not figuring out how to do this, can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top