n variable

T

taccea

Hello,

I am trying to run the code below 9 times:

If rsghtBonus_id!s_middle_data1 Is Null Then
' do nothing
Else
rsLineItems!LineItem1 = rsghtBonus_id!s_middle_data1
End If


The data fields are set appropriately to accomodate the following code:

For n = 1 To 9
If rsghtBonus_id!s_middle_data & n Is Null Then
' do nothing
Else
rsLineItems!LineItem & n = rsghtBonus_id!s_middle_data & n
End If
Next

The [ rsLineItems!LineItem & n ] above does not want to resove
to rsLineItems!LineItem1, rsLineItems!LineItem2, etc. I get an error that
says it is expecting
an [ = ] sign when I am typing the line. Why does it work for the
[rsghtBonus_id!s_middle_data & n ] field but not for the other since I don't
get the error when I type the [ If rsghtBonus_id!s_middle_data & n Is
Null Then ] line.


any guidance appreciated, as always.


taccea
 
B

Brendan Reynolds

IS NULL is SQL syntax. In VBA, use IsNull(), e.g. If
IsNull(RecordsetName!FieldName) Then ...

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
T

taccea

Thanks, Brendan, for isnull() tip, the n variable does want to resolve.
I have files like field1, field2, field3 ect, and I want to use like this:
fieldn, fieldn, where n is an incremental number, please see original messag
below,
THANKS!!!!!
taccea
Brendan Reynolds said:
IS NULL is SQL syntax. In VBA, use IsNull(), e.g. If
IsNull(RecordsetName!FieldName) Then ...

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


taccea said:
Hello,

I am trying to run the code below 9 times:

If rsghtBonus_id!s_middle_data1 Is Null Then
' do nothing
Else
rsLineItems!LineItem1 = rsghtBonus_id!s_middle_data1
End If


The data fields are set appropriately to accomodate the following code:

For n = 1 To 9
If rsghtBonus_id!s_middle_data & n Is Null Then
' do nothing
Else
rsLineItems!LineItem & n = rsghtBonus_id!s_middle_data & n
End If
Next

The [ rsLineItems!LineItem & n ] above does not want to resove
to rsLineItems!LineItem1, rsLineItems!LineItem2, etc. I get an error that
says it is expecting
an [ = ] sign when I am typing the line. Why does it work for the
[rsghtBonus_id!s_middle_data & n ] field but not for the other since I
don't
get the error when I type the [ If rsghtBonus_id!s_middle_data & n Is
Null Then ] line.


any guidance appreciated, as always.


taccea
Click to expand...
Click to expand...
 
B

Battle Troll

rsLineItems!LineItem & n = rsghtBonus_id!s_middle_data & n

Judging on this particular line of code, i believe you're trying to modify
from within a "for" all the fields LineItem1, LineItem2, ... , LineItem9,
am I wrong?

If that's what you intended, try this code instead:
rsLineItems.Fields( LineItem & str$(n) ) =
rsghtBonus_id.fields( s_middle_data & str$(n) )

Alternatively (though I've not tried it, ain't sure it will work) you
could simply try:
rsLineItems!LineItem & str$(n) = rsghtBonus_id!s_middle_data & str$(n)

give it a try and tell us what results you get...
 
B

Brendan Reynolds

Try ...

If IsNull(rsghtBonus_id.Fields("s_middle_data" & n)) Then
' do nothing
Else
rsLineItems.Fields("LineItem" & n) =
rsghtBonus_id.Fields("s_middle_data" & n)
End If

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


taccea said:
Thanks, Brendan, for isnull() tip, the n variable does want to resolve.
I have files like field1, field2, field3 ect, and I want to use like this:
fieldn, fieldn, where n is an incremental number, please see original
messag
below,
THANKS!!!!!
taccea
Brendan Reynolds said:
IS NULL is SQL syntax. In VBA, use IsNull(), e.g. If
IsNull(RecordsetName!FieldName) Then ...

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


taccea said:
Hello,

I am trying to run the code below 9 times:

If rsghtBonus_id!s_middle_data1 Is Null Then
' do nothing
Else
rsLineItems!LineItem1 = rsghtBonus_id!s_middle_data1
End If


The data fields are set appropriately to accomodate the following code:

For n = 1 To 9
If rsghtBonus_id!s_middle_data & n Is Null Then
' do nothing
Else
rsLineItems!LineItem & n = rsghtBonus_id!s_middle_data & n
End If
Next

The [ rsLineItems!LineItem & n ] above does not want to resove
to rsLineItems!LineItem1, rsLineItems!LineItem2, etc. I get an error that
says it is expecting
an [ = ] sign when I am typing the line. Why does it work for the
[rsghtBonus_id!s_middle_data & n ] field but not for the other since I
don't
get the error when I type the [ If rsghtBonus_id!s_middle_data & n
Is
Null Then ] line.


any guidance appreciated, as always.


taccea
Click to expand...
Click to expand...
Click to expand...
 
B

Brendan Reynolds

There's a problem with using the Str() function with something like this. It
puts a leading space in front of positive numbers. You can leave it out (the
number will be implicitly converted when it is appended to the string) or
use the CStr() function instead, which does not prepend the leading space
....

? "one" & str(1)
one 1
? "one" & cstr(1)
one1

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
A

AZB

Try with:

rsLineItems.fields("LineItem" & n) = rsghtBonus_id.fields("s_middle_data" &
n)


AZB



taccea said:
Thanks, Brendan, for isnull() tip, the n variable does want to resolve.
I have files like field1, field2, field3 ect, and I want to use like this:
fieldn, fieldn, where n is an incremental number, please see original messag
below,
THANKS!!!!!
taccea
Brendan Reynolds said:
IS NULL is SQL syntax. In VBA, use IsNull(), e.g. If
IsNull(RecordsetName!FieldName) Then ...

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


taccea said:
Hello,

I am trying to run the code below 9 times:

If rsghtBonus_id!s_middle_data1 Is Null Then
' do nothing
Else
rsLineItems!LineItem1 = rsghtBonus_id!s_middle_data1
End If


The data fields are set appropriately to accomodate the following code:

For n = 1 To 9
If rsghtBonus_id!s_middle_data & n Is Null Then
' do nothing
Else
rsLineItems!LineItem & n = rsghtBonus_id!s_middle_data & n
End If
Next

The [ rsLineItems!LineItem & n ] above does not want to resove
to rsLineItems!LineItem1, rsLineItems!LineItem2, etc. I get an error that
says it is expecting
an [ = ] sign when I am typing the line. Why does it work for the
[rsghtBonus_id!s_middle_data & n ] field but not for the other since I
don't
get the error when I type the [ If rsghtBonus_id!s_middle_data & n Is
Null Then ] line.


any guidance appreciated, as always.


taccea
Click to expand...
Click to expand...
Click to expand...
 
B

Battle Troll

Absolutely correct, master!!!
I had forgotten it.

Furthermore, you could also continue with str, BUT prepend every usage of
it with LTRIM:

"one" & ltrim$(str$(1))

gives the same output as:

"one" & cstr(1)
 
T

taccea

You guys are ALL wonderful,
I'm going with Brendan' code since I tried it first and it WORKS!
I will cetainly look at the other options
later.

Thanks a million.

Taccea
 
Top