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

Why does a query count (with GROUP BY) indicate more records than there are??

P: n/a
Hallo,

Indeed, a count of a query with a group by function gives more records than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact.
Answers are greatly appreciated.
--

mvg Hyphessobrycon
Nov 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi,

Post some code.

Ken
-----------
"Hyphessobricon" <sy*********@telenet.be> wrote in message
news:lW*******************@phobos.telenet-ops.be...
Hallo,

Indeed, a count of a query with a group by function gives more records than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact.
Answers are greatly appreciated.
--

mvg Hyphessobrycon

Nov 21 '05 #2

P: n/a

"Ken Tucker [MVP]" <vb***@bellsouth.net> schreef in bericht
news:eO**************@TK2MSFTNGP15.phx.gbl...
Hi,

Post some code.

Ken
-----------
"Hyphessobricon" <sy*********@telenet.be> wrote in message
news:lW*******************@phobos.telenet-ops.be...
Hallo,

Indeed, a count of a query with a group by function gives more records
than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact.
Answers are greatly appreciated.
--

mvg Hyphessobrycon


So, in my Module1
the count says : 5 rows in table
and th'n an error no row at 2
in the query there are indeed only 2 rows, an din the table there are 5, but
I work with the query.

Private Sub brandstof1() 'huisbrandolie

i = dtqBs1.Rows.Count '=dtBS1 is for the tbl, dtqBs1 is the query,
everything containing q is for the query

MsgBox("brandstofrijen " & i) '= here I get the 5 rows

Dim dattum As Date

For i = 0 To i - 1

'query gebruikt om de laatst ingevoerde te tonen

If dsqBrandstof1.Tables(0).Rows(i).Item("Brandstofjaa r") = CInt(Year(Now()))
Then '= the query dataset

frmVariabele.lblStookVJ.Text = Format(dsqBrandstof1.Tables(0).Rows(i -
1).Item("SomvanBrandstof"), "0.00")

frmVariabele.txtGasT.Text =
Format(dsqBrandstof1.Tables(0).Rows(i).Item("Somva nBrandstof"), "0.00")

frmVariabele.lblVersStook.Text = Format(Val(frmVariabele.lblStookVJ.Text) -
Val(frmVariabele.txtGasT.Text), "0.00")

End If

Next i

i = dsBrandstof1.Tables(0).Rows.Count =that is the regular table dataset

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m") > dattum Then

dattum = dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m")

End If

Next i

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m") = dattum Then

frmVariabele.txtGas.Text =
Format(dsBrandstof1.Tables(0).Rows(i).Item("Brands tof"), "0.00")

frmVariabele.txtGasDat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofinfo ")

frmVariabele.txtGadat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m")

End If

Next

End Sub
Nov 21 '05 #3

P: n/a
Hi,

Think the problem is with your for loop. for i=0 to i-1. try this
instead

dim intRows as integer = dtqBs1.Rows.Count

For i = 0 To intRows - 1
Ken
------------------------

"Hyphessobricon" <sy*********@telenet.be> wrote in message
news:E2*******************@phobos.telenet-ops.be...

"Ken Tucker [MVP]" <vb***@bellsouth.net> schreef in bericht
news:eO**************@TK2MSFTNGP15.phx.gbl...
Hi,

Post some code.

Ken
-----------
"Hyphessobricon" <sy*********@telenet.be> wrote in message
news:lW*******************@phobos.telenet-ops.be...
Hallo,

Indeed, a count of a query with a group by function gives more records
than
there are and so for-next structures don't function.
How is this to be mended.
Anyone? Everyone in fact.
Answers are greatly appreciated.
--

mvg Hyphessobrycon


So, in my Module1
the count says : 5 rows in table
and th'n an error no row at 2
in the query there are indeed only 2 rows, an din the table there are 5, but
I work with the query.

Private Sub brandstof1() 'huisbrandolie

i = dtqBs1.Rows.Count '=dtBS1 is for the tbl, dtqBs1 is the query,
everything containing q is for the query

MsgBox("brandstofrijen " & i) '= here I get the 5 rows

Dim dattum As Date

For i = 0 To i - 1

'query gebruikt om de laatst ingevoerde te tonen

If dsqBrandstof1.Tables(0).Rows(i).Item("Brandstofjaa r") = CInt(Year(Now()))
Then '= the query dataset

frmVariabele.lblStookVJ.Text = Format(dsqBrandstof1.Tables(0).Rows(i -
1).Item("SomvanBrandstof"), "0.00")

frmVariabele.txtGasT.Text =
Format(dsqBrandstof1.Tables(0).Rows(i).Item("Somva nBrandstof"), "0.00")

frmVariabele.lblVersStook.Text = Format(Val(frmVariabele.lblStookVJ.Text) -
Val(frmVariabele.txtGasT.Text), "0.00")

End If

Next i

i = dsBrandstof1.Tables(0).Rows.Count =that is the regular table dataset

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m") > dattum Then

dattum = dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m")

End If

Next i

For i = 0 To i - 1

If dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m") = dattum Then

frmVariabele.txtGas.Text =
Format(dsBrandstof1.Tables(0).Rows(i).Item("Brands tof"), "0.00")

frmVariabele.txtGasDat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofinfo ")

frmVariabele.txtGadat.Text =
dsBrandstof1.Tables(0).Rows(i).Item("Brandstofdatu m")

End If

Next

End Sub

Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.