rs![] question

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

Guest

I'd like to insert my own variable (string) between []. How can I do it?
My recordset is introduced as follows:
Dim rs As New ADODB.Recordset
 
I'd like to insert my own variable (string) between []. How can I do it?
My recordset is introduced as follows:
Dim rs As New ADODB.Recordset
Click to expand...

rs.fields(variable)

Cheers,
JAson Lepack
 
Thanks fo the answer! I tried that and I'm getting an error message:
Run-time error '3265'
Item cannot be found in the collection corresponding to the requested name
or odrinal

Does this mean that there is something wrong with my string (which should be
the same as a name of a column) or what does this mean? The string I use is
actually made of text and variables. I made a string that contains a name of
a column "in text" (so there are no variables in the string), and it gives me
the same error message.
 
I created an ms access database at c:\import\db1.mdb. In it I created
a table called table1 with fields "field name" and "field2".

I just ran this code:

Public Sub trythis()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim x As ADODB.Field

conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open ("c:\import\db1.mdb")

rs.Open "table1", conn

Do While Not rs.EOF
For Each x In rs.Fields
Debug.Print x.Name & " " & rs.Fields(x.Name).Value
Next
rs.MoveNext
Loop
End Sub

If you still have issues please post back with the exact code that you
are using so that I can see what you're talking about.

Cheers,
Jason Lepack
 
hi,
Thanks fo the answer! I tried that and I'm getting an error message:
Run-time error '3265'
Item cannot be found in the collection corresponding to the requested name
or odrinal
Click to expand...
Try

rs.Fields("[" & yourFieldName & "]")

as your field name may contain special characters.



mfG
--> stefan <--
 
In
Stefan Hoffmann said:
hi,
Thanks fo the answer! I tried that and I'm getting an error message:
Run-time error '3265'
Item cannot be found in the collection corresponding to the
requested name or odrinal
Click to expand...
Try

rs.Fields("[" & yourFieldName & "]")

as your field name may contain special characters.
Click to expand...

The brackets shouldn't be necessary with this syntax.
 
In
Beginner said:
Thanks fo the answer! I tried that and I'm getting an error message:
Run-time error '3265'
Item cannot be found in the collection corresponding to the requested
name or odrinal

Does this mean that there is something wrong with my string (which
should be the same as a name of a column) or what does this mean? The
string I use is actually made of text and variables. I made a string
that contains a name of a column "in text" (so there are no variables
in the string), and it gives me the same error message.
Click to expand...

You probably did make a mistake in building or using your string index.
Please post your code.
 
For some reason I cannot make it work, so here is the code I'm using. But
first I'll explain my database a bit. At this moment, I have one table with
thousands of records. Each record contains information about a contract. All
the contracts have a startdate and an enddate. For example contract could
start 1.1.2007 and end 31.1.2007, or start 1.4.2007 and end 30.6.2007.

I fetch the records from a server once a day. After putting the records into
the table I create columns to the table starting from the first day of the
first month of the year (so now the first added column is '1.1.2007') and
continuing for 6 years. So I have a table that contains tens of columns:
contract information (for example name, startdate, enddate), '1.1.2007',
'1.2.2007', ..., '1.12.2012'

The month-columns (like '1.1.2007') are made using sql like this:

Dim pvmnyt As Date 'current date
Dim vuosi As Integer 'year
Dim jono As String 'string that contains column name information
Dim kuukausi As Integer 'month
Dim i As Integer
Dim strSql As String

pvmnyt = Date
vuosi = Year(pvmnyt)
kuukausi = 1

jono = "1/" & kuukausi & "/" & vuosi

'here I create the columns
For i = 0 To 71
strSql = "ALTER TABLE Sopimustaulu ADD ' " & jono & "' double);"
DoCmd.RunSQL (strSql)

'and here I change the "date" of the column
If kuukausi = 12 Then
kuukausi = 1
vuosi = vuosi + 1
Else
kuukausi = kuukausi + 1
End If
jono = "1/" & kuukausi & "/" & vuosi
Next

Ok, so now I have the columns. For some reason sql puts '' around the column
name, so my jono string is 1.1.2007 but the column name is '1.1.2007'.

Then I need to put information into these columns. I'm just starting to do
this, so my sub isn't finished yet. The idea is that I pick the start date
and the end date of the contract and as long as the start date isn't the end
date I put price information into the right cells. My sub is really simple
right now, but there are prices in different currencies, so I need to do lots
of other things to the sub too. And of course the contracts are of different
lenghts. In this code I don't have to change price information, because the
currency I'm looking at is euro.

Sub Sopimustaulu_With_Prices()
'price information into table Sopimustaulu

Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim salku As Date 'starting date
Dim sloppu As Date 'ending date (for the contract)
Dim shinta As Double 'price for the contract
Dim svaluutta As String 'currency information (what currency)
Dim strjono As String

'I open my contract table
rs.Open "Sopimustaulu", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
'and then I open a table where i can find prices (but I don't need this yet,
later I do)
rs2.Open "Hintataulu", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

rs.MoveFirst

Do While rs.EOF = False
'I pick the information I need from the recordset
salku = rs![STARTDATE]
sloppu = rs![ENDDATE]
shinta = rs![PRICE]
svaluutta = rs![CURRENCYCODE]

'if the contract is valid for only one month
If Year(salku) = Year(sloppu) And Month(salku) = Month(sloppu) Then

If svaluutta = "EUR" Then
shinta = rs![PRICE]

strjono = "'1/" & Month(salku) & "/" & Year(salku) & "'"
'easier to see the ': strjono = " '1/ " & Month(salku) & "/" & Year(salku) &
" ' "
'these don't work
'rs.Fields(strjono).Value = shinta
'rs.Fields([ " & strjono & " ]).Value = shinta
'rs.Fields ("[" & yourFieldName & "]")

'if I insert the found price into the table like this
'(I made a test column called F16)

rs![F16] = shinta

'I get all the right price information into the F16 -column

End If

End If
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
End Sub

Ok, there it was.. I hope you get the idea and can tell me why it doesn't
work. It is really great that there is a place like this where one can go and
ask and get really helpful information! So thanks for that! :) And by the
way, I need to put the price information into the table like that because
this is only the first step of my project, and I need information in that
form so that I can go on and create other tables based to this information.

Yours, Beginner

p.s I will read this forum next time on monday, because we have a national
holiday tomorrow, so I'll reply then..
 
hi,
I fetch the records from a server once a day. After putting the records into
the table I create columns to the table starting from the first day of the
first month of the year (so now the first added column is '1.1.2007') and
continuing for 6 years. So I have a table that contains tens of columns:
contract information (for example name, startdate, enddate), '1.1.2007',
'1.2.2007', ..., '1.12.2012'
Click to expand...
You need to normalize your table desgin.
Then I need to put information into these columns. I'm just starting to do
this, so my sub isn't finished yet. The idea is that I pick the start date
and the end date of the contract and as long as the start date isn't the end
date I put price information into the right cells.
Click to expand...
You need a table layout like this:

ID, autoincrement, primary key
DateStart, DateTime, not null
DateEnd, DateTime, [not] null
Price, Currency, not null
OtherRelatedFields,...

So that it will contain data like this:

1, #2007-02-01#, #2012-12-01#, 1000$, ...
2, #2007-07-01#, #2008-03-01#, 2500$, ...


mfG
--> stefan <--
 
In
Beginner said:
For some reason I cannot make it work, so here is the code I'm using.
But first I'll explain my database a bit. At this moment, I have one
table with thousands of records. Each record contains information
about a contract. All the contracts have a startdate and an enddate.
For example contract could start 1.1.2007 and end 31.1.2007, or start
1.4.2007 and end 30.6.2007.

I fetch the records from a server once a day. After putting the
records into the table I create columns to the table starting from
the first day of the first month of the year (so now the first added
column is '1.1.2007') and continuing for 6 years. So I have a table
that contains tens of columns: contract information (for example
name, startdate, enddate), '1.1.2007', '1.2.2007', ..., '1.12.2012'

The month-columns (like '1.1.2007') are made using sql like this:

Dim pvmnyt As Date 'current date
Dim vuosi As Integer 'year
Dim jono As String 'string that contains column name information
Dim kuukausi As Integer 'month
Dim i As Integer
Dim strSql As String

pvmnyt = Date
vuosi = Year(pvmnyt)
kuukausi = 1

jono = "1/" & kuukausi & "/" & vuosi

'here I create the columns
For i = 0 To 71
strSql = "ALTER TABLE Sopimustaulu ADD ' " & jono & "' double);"
DoCmd.RunSQL (strSql)

'and here I change the "date" of the column
If kuukausi = 12 Then
kuukausi = 1
vuosi = vuosi + 1
Else
kuukausi = kuukausi + 1
End If
jono = "1/" & kuukausi & "/" & vuosi
Next

Ok, so now I have the columns. For some reason sql puts '' around the
column name, so my jono string is 1.1.2007 but the column name is
'1.1.2007'.

Then I need to put information into these columns. I'm just starting
to do this, so my sub isn't finished yet. The idea is that I pick the
start date and the end date of the contract and as long as the start
date isn't the end date I put price information into the right cells.
My sub is really simple right now, but there are prices in different
currencies, so I need to do lots of other things to the sub too. And
of course the contracts are of different lenghts. In this code I
don't have to change price information, because the currency I'm
looking at is euro.

Sub Sopimustaulu_With_Prices()
'price information into table Sopimustaulu

Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim salku As Date 'starting date
Dim sloppu As Date 'ending date (for the contract)
Dim shinta As Double 'price for the contract
Dim svaluutta As String 'currency information (what currency)
Dim strjono As String

'I open my contract table
rs.Open "Sopimustaulu", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
'and then I open a table where i can find prices (but I don't need
this yet, later I do)
rs2.Open "Hintataulu", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

rs.MoveFirst

Do While rs.EOF = False
'I pick the information I need from the recordset
salku = rs![STARTDATE]
sloppu = rs![ENDDATE]
shinta = rs![PRICE]
svaluutta = rs![CURRENCYCODE]

'if the contract is valid for only one month
If Year(salku) = Year(sloppu) And Month(salku) = Month(sloppu) Then

If svaluutta = "EUR" Then
shinta = rs![PRICE]

strjono = "'1/" & Month(salku) & "/" & Year(salku) & "'"
'easier to see the ': strjono = " '1/ " & Month(salku) & "/" &
Year(salku) & " ' "
'these don't work
'rs.Fields(strjono).Value = shinta
'rs.Fields([ " & strjono & " ]).Value = shinta
'rs.Fields ("[" & yourFieldName & "]")

'if I insert the found price into the table like this
'(I made a test column called F16)

rs![F16] = shinta

'I get all the right price information into the F16 -column

End If

End If
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
End Sub

Ok, there it was.. I hope you get the idea and can tell me why it
doesn't work. It is really great that there is a place like this
where one can go and ask and get really helpful information! So
thanks for that! :) And by the way, I need to put the price
information into the table like that because this is only the first
step of my project, and I need information in that form so that I can
go on and create other tables based to this information.
Click to expand...

You're doing this in an ADP, I guess? In a Jet database, you can't have
more than 255 columns in a table, and it looks to me like your design is
requiring you to create more than that. This is a faulty design, in my
opinion. You are storing data -- the date -- in the column name,
creating an unnormalized structure that will be difficult to query.
Furthermore, any design that requires you to change the database design
as part of your regular operations is suspect. Although I am not party
to all the design constraints you're working under, it seems to me that
you would do better to store the data in a table with one record per
day, if you need that, or per date range if that will serve. If you
then need to display the data in a view showing one column per day, you
can use a crosstab query to do that.

As for the specific problem you're encountering in referring to the
fields you've created, I don't think the field names you're creating
actually have the single-quotes (') as part of their names. You need
those quotes in your ALTER TABLE statement to create them, because
otherwise the field names won't be recognized as text values, but I
don't think the field name itself will contain those wrapping quotes. I
could be wrong here, but have you tried this, in your
Sopimustaulu_With_Prices() procedure:

strjono = "1/" & Month(salku) & "/" & Year(salku)

rs.Fields(strjono).Value = shinta

?
 
Hi you all :)

And thank you for the comments and advice! I know that my database might
seem a bit silly (and that it even might be) but I need to do this so I
cannot help it. The limit of 255 columns is more than enough for me, since I
"only" have about 80 columns...

I managed to find the problem with your advice, for some reason my
column-creating SQL statement puts spaces between the ' and the date, so it
isn't '1/1/2007', it is ' 1/1/2007'.

Now this thing of mine is working again, so thank you all again!

Yours, Beginner
 
Back
Top