NULL records problem

  • Thread starter George Papadopoulos
  • 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
 
M

MacDermott

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
 
G

George Papadopoulos

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top