NULL records problem

  • Thread starter Thread starter George Papadopoulos
  • Start date Start date
G

George Papadopoulos

I have written the code below

Private Sub Anazhthsh_Click()
Dim strSelect As String
Dim intLoop As Integer
Dim intKwdikos_Episkeyhs As Integer, sum1 As Integer, Totalsum As Integer
Dim dbEPEMBATHS As Database

' Make a connection to the database
Set dbEPEMBATHS = CurrentDb

strSelect = "Select Kwdikos_episkeyhs, Kwdikos_klinikhs,
Kwdikos_mhxanhmatos FROM EPISKEYH WHERE" _
& " (Hmeromhnia >= #" & Me.From_Date & "#)" _
& " and (Hmeromhnia <= #" & Me.To_Date & "#)" _
& " and (Aitia_blabhs =" & Me.btnKakh_xrhsh & ")"

'Fill in the list box
[list_Episkeyes].RowSource = strSelect

' Loop through table aggregating each sum
For intLoop = 1 To Me!list_Episkeyes.ListCount - 1

intKwdikos_Episkeyhs = Me!list_Episkeyes.Column(0, intLoop)
strSelect = "Select Sum(Kostos) FROM ANTALLAKTIKA WHERE
Kwdikos_episkeyhs =" & intKwdikos_Episkeyhs

sum1 = dbEPEMBATHS.OpenRecordset(strSelect).Fields(0).Value
Totalsum = Totalsum + sum1

Next intLoop

Me.Oliko_kostos.Value = Totalsum

End Sub

I`m experiencing this problem with the strSelect in the For loop. When the
criterion for the Select statement is not satisfied the OpenRecordset
statement fails with an 'invalid use of NULL' error message. How can I get
around this?

thx, in advance


George Papadopoulos
 
If you want to use this syntax, you can simply trap the error out and
continue on your merry way.
One simple approach would be to add these two lines:
sum1=0
on error resume next
before this one:
sum1 = dbEPEMBATHS.OpenRecordset(strSelect).Fields(0).Value

However, I believe you could get the same result like this:
totalsum=dbEPEMBATHS.OpenRecordset("SELECT Sum(ANTALLAKTIKA.Kostos) AS
SumKost FROM ANTALLAKTIKA WHERE ANTALLAKTIKA.Kwdikos_episkeyhs IN (SELECT
Kwdikos_episkeyhs, FROM EPISKEYH WHERE" _
& " (Hmeromhnia >= #" & From_Date & "#)" _
& " and (Hmeromhnia <= #" & To_Date & "#)" _
& " and (Aitia_blabhs =" & btnKakh_xrhsh & ")").Fields(0).Value

HTH
- Turtle
 
thx, for your help.

I used the first approach since I found the two-depth SQL statement a bit
hard to code.

George

Ï "MacDermott said:
If you want to use this syntax, you can simply trap the error out and
continue on your merry way.
One simple approach would be to add these two lines:
sum1=0
on error resume next
before this one:
sum1 = dbEPEMBATHS.OpenRecordset(strSelect).Fields(0).Value

However, I believe you could get the same result like this:
totalsum=dbEPEMBATHS.OpenRecordset("SELECT Sum(ANTALLAKTIKA.Kostos) AS
SumKost FROM ANTALLAKTIKA WHERE ANTALLAKTIKA.Kwdikos_episkeyhs IN (SELECT
Kwdikos_episkeyhs, FROM EPISKEYH WHERE" _
& " (Hmeromhnia >= #" & From_Date & "#)" _
& " and (Hmeromhnia <= #" & To_Date & "#)" _
& " and (Aitia_blabhs =" & btnKakh_xrhsh & ")").Fields(0).Value

HTH
- Turtle


George Papadopoulos said:
I have written the code below

Private Sub Anazhthsh_Click()
Dim strSelect As String
Dim intLoop As Integer
Dim intKwdikos_Episkeyhs As Integer, sum1 As Integer, Totalsum As Integer
Dim dbEPEMBATHS As Database

' Make a connection to the database
Set dbEPEMBATHS = CurrentDb

strSelect = "Select Kwdikos_episkeyhs, Kwdikos_klinikhs,
Kwdikos_mhxanhmatos FROM EPISKEYH WHERE" _
& " (Hmeromhnia >= #" & Me.From_Date & "#)" _
& " and (Hmeromhnia <= #" & Me.To_Date & "#)" _
& " and (Aitia_blabhs =" & Me.btnKakh_xrhsh & ")"

'Fill in the list box
[list_Episkeyes].RowSource = strSelect

' Loop through table aggregating each sum
For intLoop = 1 To Me!list_Episkeyes.ListCount - 1

intKwdikos_Episkeyhs = Me!list_Episkeyes.Column(0, intLoop)
strSelect = "Select Sum(Kostos) FROM ANTALLAKTIKA WHERE
Kwdikos_episkeyhs =" & intKwdikos_Episkeyhs

sum1 = dbEPEMBATHS.OpenRecordset(strSelect).Fields(0).Value
Totalsum = Totalsum + sum1

Next intLoop

Me.Oliko_kostos.Value = Totalsum

End Sub

I`m experiencing this problem with the strSelect in the For loop. When the
criterion for the Select statement is not satisfied the OpenRecordset
statement fails with an 'invalid use of NULL' error message. How can I get
around this?

thx, in advance


George Papadopoulos
Click to expand...
Click to expand...
 
Back
Top