By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Reporting Problem !

P: n/a
Here is a code snippet in my Report:

** Snippet starts here ************************
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
Dim sSQL As String, strSQL0 As String
Dim OpBal_Mst, OPBAL As Double

' ***** Op.Bal from Master-File ... DPATMST *****
strSQL0 = "SELECT Sum(nz(DPATMST.[OPBAL])) AS OpBal_Sql" & _
" FROM DPATMST" & _
" WHERE DPATMST.Code = " & Me.txt_Code.Value
OpBal_Mst = Nz(CurrentDb().OpenRecordset(strSQL0)!OpBal_Sql, 0)
** Snippet ends here **************************
At times I get an error msg where Access highlights the above SQL
Statement & says:

RunTime Error 2427
You entered an expression that has no value.

I realized that the problem is with Me.txt_Code.Value which is a Text
Box in my Report in the Group Header Section.

Can anyone please throw some light on this ?

Thx & Best Rgds,
Prakash.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
A couple of ideas to try if you haven't already:

1) Change

Me.txt_Code.Value

to

Me!txt_Code.Value

or

Me.Controls("txt_Code").Value

2) What is the data type of the value in txt_Code? If it's a date or
text it will need to be handled differently. (If it's a number it
should be fine as is.)

3) From what you've said, I've assume that the error is occurring on the
{strSQL0 = } line (i.e. setting the string value). If it's occurring on
the next line (i.e. using the SQL), post the SQL string to the
newsgroup.

4) (If error on running SQL) Maybe SQLs need a semi-colon on the end?

-------
LoopyNZ
-------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #2

P: n/a
On 27 Aug 2004 03:50:53 -0700, Prakash Wadhwani wrote:
Here is a code snippet in my Report:

** Snippet starts here ************************
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
Dim sSQL As String, strSQL0 As String
Dim OpBal_Mst, OPBAL As Double

' ***** Op.Bal from Master-File ... DPATMST *****
strSQL0 = "SELECT Sum(nz(DPATMST.[OPBAL])) AS OpBal_Sql" & _
" FROM DPATMST" & _
" WHERE DPATMST.Code = " & Me.txt_Code.Value
OpBal_Mst = Nz(CurrentDb().OpenRecordset(strSQL0)!OpBal_Sql, 0)
** Snippet ends here **************************

At times I get an error msg where Access highlights the above SQL
Statement & says:

RunTime Error 2427
You entered an expression that has no value.

I realized that the problem is with Me.txt_Code.Value which is a Text
Box in my Report in the Group Header Section.

Can anyone please throw some light on this ?

Thx & Best Rgds,
Prakash.


What is the datatype of the value stored in DPATMST.Code ?

If it is Text datatype, use
"WHERE DPATMST.Code ='" & Me.txt_Code & "';"

Note: The Value property is the default property in Access. You do not
need to explicitly state it.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

P: n/a
Thx for replying !

datatype of the value stored in DPATMST.Code = NUMERIC

The report is being called from a form where the user enters his FROM /
TO parameters.

The code works fine usually. However, I realized this error is being
triggered only when there is NO DATA in DPATDAT for DPATMST.Code

Pls note that there are 2 SQL statements below.
1) Extracting data fm DPATMST & the other
2) extracting data fm DPATDAT

What's really odd is that there IS 1 entry in DPATMST containing Data,
but the error occurs when there is NO DATA in DPATDAT. Yet ... the SQL
part highlighted by the debugger is the 1st SQL Statement pointing to
DPATMST.

This is driving me crazy.
Here's my entire report GroupHeader Code:
*** Code starts here *********************
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
Dim sSQL As String, strSQL0 As String
Dim OpBal_Mst, OPBAL As Double

' ***** Op.Bal from Master-File ... DPATMST *****
'MsgBox Me.CODE

strSQL0 = "SELECT Sum(nz(DPATMST.[OPBAL])) AS OpBal_Sql" & _
" FROM DPATMST" & _
" WHERE DPATMST.Code = " & Me.txt_Code.Value
OpBal_Mst = Nz(CurrentDb().OpenRecordset(strSQL0)!OpBal_Sql, 0)
'===== OPBAL Calculation SQL Way from Data-File ... DPATDAT
=============

sSQL = "SELECT ( Sum(nz(DPATDAT.[DEBIT])) -
Sum(nz(DPATDAT.[CREDIT])) ) AS OpBal_Sql" & _
" FROM DPATDAT" & _
" WHERE DPATDAT.Code =" & Me.txt_Code & _
" AND ( [DPATDAT.Inv_Date] < " &
DMY(Forms!Par_Ledger_Accounts!Txt_FromDate.Value) & ")"

'MsgBox sSQL

OPBAL = Nz(CurrentDb().OpenRecordset(sSQL)!OpBal_Sql, 0) + OpBal_Mst
'MsgBox OPBAL

'-- Initializing these text boxes to erase previous values
Me.Txt_Dr_Opbal = 0
Me.Txt_Cr_Opbal = 0

If Nz(OPBAL) > 0 Then
Me.Txt_Dr_Opbal = Nz(OPBAL)
'LBL_OP_CreditTotal.Caption = Format(Abs(Opbal), "#0.000")
ElseIf Nz(OPBAL) = 0 Then
Me.Txt_Dr_Opbal = Nz(OPBAL)
Me.Txt_Cr_Opbal = Nz(OPBAL)
ElseIf Nz(OPBAL) < 0 Then
Me.Txt_Cr_Opbal = Nz(OPBAL)
End If

End Sub
*** Code ends here *********************

I think I've presented my problem in a confusing manner so here's it
once again in short:

The error shows up when:
a) There is 1 transaction in DPATMST ... but
b) NO transaction in DPATDAT
matching the parameters requested.

I'm at my wit's end here.

Thx & Best Rgds,
Prakash.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.