[RESOLVED] Problem In Sorting Recods, And Counting Them

[xboner]

So this is probably my last question for this final project (probably, sort of). So please help me guys, do your best.

I have records here like this in "absen" table in mysql

and I wrote this (probably bad) code in transaction form :

Dim countval As Integer
Dim countlem As Integer

tahun = Right(DTPicker1, 4)
hari = Left(DTPicker1, 2)
bulan = Mid(DTPicker1, 4, 2)
galtang = tahun & "-" & bulan & "-" & hari
tahun2 = Right(DTPicker2, 4)
hari2 = Left(DTPicker2, 2)
bulan2 = Mid(DTPicker2, 4, 2)
galtang2 = tahun & "-" & bulan & "-" & hari

Call koneksi
hitung = "select count(*) from absen where NIP ='" & cnip & "' and status ='absen keluar' and tglabsen between '" & galtang & "' and '" & galtang2 & "'"
rsabsen.Open hitung, KON, adOpenForwardOnly, adLockReadOnly

If Not rsabsen.EOF Then

   countval = rsabsen(0).Value
   tjumlahabsen = countval
   gaji = Val(tjumlahabsen.Text) * Val(tgaji.Text)
   rsabsen.Close
   lembur = "select sum(lmbr) from absen where NIP ='" & cnip & "' and status ='absen keluar' and tglabsen between '" & galtang & "' and '" & galtang2 & "'"
   rsabsen.Open lembur, KON, adOpenForwardOnly, adLockReadOnly
   
   If Not rsabsen(0).Value <= 0 Then
   
   countlem = rsabsen(0).Value
   tjumlahjamlembur = countlem
   ttunjangan = rsabsen(0).Value * Val(txtlemburperjam.Text)
   ttotalgaji = Val(gaji.Text) + Val(ttunjangan.Text)
   
   Else
   tjumlahjamlembur = 0
   End If
   bsimpan.Enabled = True
Else
MsgBox "Data tidak ditemukan"
End If
DTPicker1.Enabled = False
DTPicker2.Enabled = False
grid2.Enabled = False
End Sub

that code above is to sort the records in "absen" table, according to NIP, status, and tglabsen that the user set between 2 dtpicker in transaction form (as you can see, i use string manipulation to turn the dates format from dd/mm/yyy to yyyy-mm-dd), and returns the amount of records that mysql catch. I set the date from 1 august 2017 to 31 august 2017, and the code returned 1 (it returns 1 to tjumlahabsen textbox). Is there anything wrong with them? any suggestion? thanks in advance

here is the transaction form



the code is written as click even for "OK" button

[DataMiser]

You should add in the red line below to see what your query looks like when built.

hitung = "select count(*) from absen where NIP ='" & cnip & "' and status ='absen keluar' and tglabsen between '" & galtang & "' and '" & galtang2 & "'"
debug.print hitung
rsabsen.Open hitung, KON, adOpenForwardOnly, adLockReadOnly

What is in this other textbox when the code is ran?

   ttunjangan = rsabsen(0).Value * Val(txtlemburperjam.Text)

[ChrisE]

Hi

in order to sort something you need to place ASC or DESC in you sql-statement

example:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
ORDER BY Customers.CompanyName DESC;

or

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
ORDER BY Customers.CompanyName ASC;

regards
Chris

[DataMiser]

Hi

in order to sort something you need to place ASC or DESC in you sql-statement

Actually no, you do not need to add ASC in there. YOu do of course need the Order By clause but ASC is the default so does not have to be there

[xboner]

You should add in the red line below to see what your query looks like when built.

hitung = "select count(*) from absen where NIP ='" & cnip & "' and status ='absen keluar' and tglabsen between '" & galtang & "' and '" & galtang2 & "'"
debug.print hitung
rsabsen.Open hitung, KON, adOpenForwardOnly, adLockReadOnly

What is in this other textbox when the code is ran?

   ttunjangan = rsabsen(0).Value * Val(txtlemburperjam.Text)

Sorry man, a mistake, the code gives me "1". I tried it again, i set the date from 1 august 2017 to 31 august, then i clicked ok.
The second sql statement in that code (the one with select sum(*) ) also returns 1.

this is what is written in the immediate:

select count(*) from absen where NIP ='012' and status ='absen keluar' and tglabsen between '2017-08-01' and '2017-08-01'

37500 is in ttunjangan, basically the code try to select sum the lmbr field in absen table (as you can see in the picture above), and the multiply the result with 37500 (the payment for overtime for 1 hour), i got 37500, because the sql statement returns 1

[Arnoutdv]

You also have only 1 record on 2017-08-01.
Your query does a between on a single date.

[xboner]

You also have only 1 record on 2017-08-01.
Your query does a between on a single date.

huh, what? a between on a single date?

[DataMiser]

huh, what? a between on a single date?

between '2017-08-01' and '2017-08-01'

[DataMiser]

I think this is your problem

galtang2 = tahun & "-" & bulan & "-" & hari

Seems those 3 vars should all have a 2 on the end

[xboner]

I think this is your problem

galtang2 = tahun & "-" & bulan & "-" & hari

Seems those 3 vars should all have a 2 on the end

ohhhh, ****, i copy pasted it and forgot to edit them

i think this **** is solved now, thanks datamister, and my program is almost done now