Reporting Problem !

[Prakash Wadhwani]

Here is a code snippet in my Report:

** Snippet starts here *************** *********
Private Sub GroupHeader0_Fo rmat(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.Val ue
OpBal_Mst = Nz(CurrentDb(). OpenRecordset(s trSQL0)!OpBal_S ql, 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.Val ue 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.

[LoopyNZ]

Re: Reporting Problem !

A couple of ideas to try if you haven't already:

1) Change

Me.txt_Code.Val ue

to

Me!txt_Code.Val ue

or

Me.Controls("tx t_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!

[fredg]

Re: Reporting Problem !

On 27 Aug 2004 03:50:53 -0700, Prakash Wadhwani wrote:
[color=blue]
> Here is a code snippet in my Report:
>
> ** Snippet starts here *************** *********
> Private Sub GroupHeader0_Fo rmat(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.Val ue
> OpBal_Mst = Nz(CurrentDb(). OpenRecordset(s trSQL0)!OpBal_S ql, 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.Val ue 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.[/color]

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.

[Prakash Wadhwani]

Re: Reporting Problem !

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_Fo rmat(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.Val ue
OpBal_Mst = Nz(CurrentDb(). OpenRecordset(s trSQL0)!OpBal_S ql, 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_Dat e] < " &
DMY(Forms!Par_L edger_Accounts! Txt_FromDate.Va lue) & ")"

'MsgBox sSQL

OPBAL = Nz(CurrentDb(). OpenRecordset(s SQL)!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_CreditT otal.Caption = Format(Abs(Opba l), "#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!