[RESOLVED] Problem In Sorting Recods, And Counting Them

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 Attachment 150431

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

Code:

---

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

Attachment 150435

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

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

Code:

---

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?

Code:

---

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

---

Hi

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

example:

Code:

---

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

---

or

Code:

---

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

---

regards
Chris

Quote:

---

Originally Posted by ChrisE

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

Quote:

---

Originally Posted by DataMiser

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

Code:

---

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?

Code:

---

  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

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

Quote:

---

Originally Posted by Arnoutdv

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?

Quote:

---

Originally Posted by xboner

huh, what? a between on a single date?

---

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

I think this is your problem

Code:

---

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

---

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

Quote:

---

Originally Posted by DataMiser

I think this is your problem

Code:

---

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