#Name? error

S

Salvador1273

Hi, I read some of the other post regarding this error and I don't sure if I
understand. I have a problem. I'm working with recordset as rowsource for a
form, there is one field that no always has a value, then when the field
doesn't have a value them this message appears in the control text box.

Does anybody can give some advices to fix this?
How can I put a Zero on this field?
 
B

Brendan Reynolds

You can use the NZ() function, e.g. NZ([SomeField], 0) will return the value
of SomeField if it is not Null, or 0 if the value of SomeField is Null.

--
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.
 
S

Salvador1273

Thank you for your help, I tried but I think that my problem is depth; here
it more information: I have a list box with values, after double click a code
start running which generate a recordset which will be the rowsource for the
following form, the problem is one field "upqtychange" not always have a
value; then when this field doesn't have a value and EOF is returned and when
the form is open just show a blank form, to avoid that I created a second
source without that field, but then the #name? error appears....what can I do?

I'm sure is a bad design, but here changes their mains a lot.

strsource = "SELECT upqtychange,*,PaymentID,Paynumber,Payperiod" & vbCrLf
strsource = strsource & "FROM UpLsChange" & vbCrLf
strsource = strsource & "INNER JOIN tblBidValues ON UpLsChange.ItemID =
tblBidValues.ItemID" & vbCrLf
strsource = strsource & "INNER JOIN tblPayment ON
tblBidValues.SubProjectName = tblPayment.SubProjectName " & vbCrLf
strsource = strsource & "WHERE PayItem =" & ActualPayItem & "AND
Paynumber =" & ActualPay & _
"AND tblPayment.SubProjectName = " & ProjectActual & vbCrLf


Set cn = CurrentProject.Connection
rcd.Open strsource, cn, adOpenKeyset, adLockBatchOptimistic, adCmdText
If rcd.EOF = True Then
Set rcd = Nothing
strsource = "SELECT *,PaymentID,Paynumber,Payperiod" & vbCrLf
strsource = strsource & "FROM tblBidValues" & vbCrLf
strsource = strsource & "INNER JOIN tblPayment ON
tblBidValues.SubprojectName = tblPayment.SubProjectName " & vbCrLf
strsource = strsource & "WHERE PayItem =" & ActualPayItem & "AND
Paynumber =" & ActualPay & _
"AND tblPayment.SubProjectName = " & ProjectActual &
vbCrLf
rcd.Open strsource, cn, adOpenKeyset, adLockBatchOptimistic, adCmdText

Else
End If
DoCmd.OpenForm FormName:="subformBidValues", WindowMode:=acHidden
Set Forms!subformBidValues.Recordset = rcd
Forms!subformBidValues.Visible = True
Set rcd = Nothing
Set cn = Nothing


DoCmd.Close acForm, Me.Name

If you notice the first

Brendan Reynolds said:
You can use the NZ() function, e.g. NZ([SomeField], 0) will return the value
of SomeField if it is not Null, or 0 if the value of SomeField is Null.

--
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.


Salvador1273 said:
Hi, I read some of the other post regarding this error and I don't sure if
I
understand. I have a problem. I'm working with recordset as rowsource for
a
form, there is one field that no always has a value, then when the field
doesn't have a value them this message appears in the control text box.

Does anybody can give some advices to fix this?
How can I put a Zero on this field?
Click to expand...
Click to expand...
 
B

Brendan Reynolds

You've got syntax errors in the SQL statement. For example, you're missing
some vital spaces ...

"WHERE PayItem =" & ActualPayItem & "AND

If the variable ActualPayItem contained the value 1, the result would be ...

WHERE PayItem=1AND

Note the missing space between the 1 and the "AND".

Also, "SubProjectName" sounds like it is probably a text field rather than a
number field, in which case you'll need quotes around the value ...

" AND tblPayment.SubProjectName = '" & ProjectActual & "'"

.... instead of ...

"AND tblPayment.SubProjectName = " & ProjectActual

If you add a Debug.Print strSource immediately after the code that builds
the SQL statement, then examine the result in the Immediate window, these
problems will become clear.

--
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.


Salvador1273 said:
Thank you for your help, I tried but I think that my problem is depth;
here
it more information: I have a list box with values, after double click a
code
start running which generate a recordset which will be the rowsource for
the
following form, the problem is one field "upqtychange" not always have a
value; then when this field doesn't have a value and EOF is returned and
when
the form is open just show a blank form, to avoid that I created a second
source without that field, but then the #name? error appears....what can I
do?

I'm sure is a bad design, but here changes their mains a lot.

strsource = "SELECT upqtychange,*,PaymentID,Paynumber,Payperiod" & vbCrLf
strsource = strsource & "FROM UpLsChange" & vbCrLf
strsource = strsource & "INNER JOIN tblBidValues ON UpLsChange.ItemID =
tblBidValues.ItemID" & vbCrLf
strsource = strsource & "INNER JOIN tblPayment ON
tblBidValues.SubProjectName = tblPayment.SubProjectName " & vbCrLf
strsource = strsource & "WHERE PayItem =" & ActualPayItem & "AND
Paynumber =" & ActualPay & _
"AND tblPayment.SubProjectName = " & ProjectActual & vbCrLf


Set cn = CurrentProject.Connection
rcd.Open strsource, cn, adOpenKeyset, adLockBatchOptimistic, adCmdText
If rcd.EOF = True Then
Set rcd = Nothing
strsource = "SELECT *,PaymentID,Paynumber,Payperiod" & vbCrLf
strsource = strsource & "FROM tblBidValues" & vbCrLf
strsource = strsource & "INNER JOIN tblPayment ON
tblBidValues.SubprojectName = tblPayment.SubProjectName " & vbCrLf
strsource = strsource & "WHERE PayItem =" & ActualPayItem & "AND
Paynumber =" & ActualPay & _
"AND tblPayment.SubProjectName = " & ProjectActual &
vbCrLf
rcd.Open strsource, cn, adOpenKeyset, adLockBatchOptimistic,
adCmdText

Else
End If
DoCmd.OpenForm FormName:="subformBidValues", WindowMode:=acHidden
Set Forms!subformBidValues.Recordset = rcd
Forms!subformBidValues.Visible = True
Set rcd = Nothing
Set cn = Nothing


DoCmd.Close acForm, Me.Name

If you notice the first

Brendan Reynolds said:
You can use the NZ() function, e.g. NZ([SomeField], 0) will return the
value
of SomeField if it is not Null, or 0 if the value of SomeField is Null.

--
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.


Salvador1273 said:
Hi, I read some of the other post regarding this error and I don't sure
if
I
understand. I have a problem. I'm working with recordset as rowsource
for
a
form, there is one field that no always has a value, then when the
field
doesn't have a value them this message appears in the control text box.

Does anybody can give some advices to fix this?
How can I put a Zero on this field?
Click to expand...
Click to expand...
Click to expand...
 
Top