عدم جستجو بین دو تاریخ با دستور select

[look20]

با سلام و خسته نباشید به دوستان پر تلاش و یاران همیشگی سایت
ببخشید مشکل من سر این کده

Adodc1.RecordSource = "select * from Table1 where borndate Between '" & Trim(txtDate.Text) & "' and '" & Trim(txtDate1.Text) & "'"

Adodc1.Refresh
عمل نمیکنه
البته اگه اسم فیلد borndate رو به dateتغییر بدم و توی کد بالا هم بجای borndate از date استفاده کنم عمل میکنه
ولی مشکل اینجاست که من میخام توی فیلد دیگه ای بنام employdate به همین روش جستجو بسازم
وقتی کد بالا فقط بانام date کار میکنه
پس چطور برای employdate همچین سرچی بسازم
ممنون

[vbnovin]

متوجه سوالتان نمیشم هر مشکلی باشه حلللللللللل ، بهتر توضیح بدید سوال پرسیدن هنر است ..

[look20]

سلام منظورم اینکه میخام توی فیلد borndate رکوردهایی که بین دو تاریخ که توی txtdate.text و txtdate1.text نوشتم رو با دستور select انتخاب کنم ولی متاسفانه نمیاره

[vbnovin]

Public Sub TARYKH_HAFTGY()



Dim b As Boolean

گزارش بر اساس تاریخ و منطقه
If Mask2 = "__/__/__" Or Mask3 = "__/__/__" Then
MsgBox ".ÊÇÑíÎ ÒÇÑÔ ÑÇÊÚííä äãÇííÏ", vbOKOnly + vbInformation + vbMsgBoxRight, "ãÔÇæÑ ÇÒ - æÇÑÔ"
Op6.Value = False
Exit Sub
End If

date2 = Mask2
date3 = Mask3

Frm.Visible = True
PrGB.Value = 0
Timer3.Enabled = True

'ÍÐÝ ÌÏæá ãÌÇÒí
SQLSTR = " DELETE * FROM t_TEMPHAFTEGY "
cn2.Execute SQLSTR
'

If rs1.State = 1 Then
rs1.Close
End If

SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" _
& " FROM tMantagheh INNER JOIN ((tROSTA INNER JOIN (tSADERE INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy) ON tROSTA.CODRO = tSADERE.CODRO) INNER JOIN tkala ON tSADERE.CODKA = tkala.codka) ON tMantagheh.codmn = tSADERE.codmn " _
& " Where(tSADERE.TARYKH) >= " & SETCOMA(Mask2, True, False) & _
" And " & _
" (tSADERE.tarykh) <= " & SETCOMA(Mask3, True, False) & _
" And " & _
" (tSADERE.CODMN) = " & SETCOMA(Trim(Left(Cmbman.List(Cmbman.ListIndex), 2)), True, False) _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" & " ORDER BY tSADERE.CODKA ASC"

rs1.Open SQLSTR, cn1, adOpenDynamic, adLockOptimistic

If rs1.EOF = True Then

rs1.Close
MsgBox ".ãÕÑÝí Êǘäæä ÏÑ Çíä ÈÇÒå ÊÇÑíÎí ËÈÊ äÑÏíÏå ÇÓÊ", vbOKOnly + vbInformation + vbMsgBoxRight, "ãÔÇæÑ ÇÒ - æÇÑÔ"
'Exit Sub
b = True
End If
''''''''''''''''''''''''''

If b <> True Then

SUM1 = 0
SUM2 = 0
HPY = 0
rs1.MoveFirst
Do While Not rs1.EOF

DoEvents

If rs2.State = 1 Then
rs2.Close
End If

SQLSTR = " SELECT * FROM t_TEMPHAFTEGY "

rs2.Open SQLSTR, cn2, adOpenDynamic, adLockOptimistic

rs2.AddNew

DoEvents

rs2!npy = rs1!npy
rs2!namesh = rs1!namesh
rs2!codmn = rs1!codmn
rs2!namemn = rs1!namemn
rs2!CODRO = rs1!CODRO
rs2!namero = rs1!namero

rs2!HPY = rs1!HPY
HPY = rs1!HPY

rs2!nameshahr = rs1!nameshahr
rs2!CODKA = "" & rs1!CODKA
rs2!nameka = "" & rs1!nameka
rs2!vahed = "" & rs1!vahed
rs2!DATESH = "" & rs1!DATESH
rs2!DATEAN = "" & rs1!DATEAN
rs2!MABPY = rs1!MABPY

'ãÕÑÝí ÏÑ Çíä ÈÇÒå ÊÇÑíÎ
rs2!masrafyhaftgy = rs1!SumOfMASRAFYDATE
'ÂãÇÑ ÊÓÊ ÏÑ Çíä ÈÇÒå ÊÇÑíÎí
rs2!TN = rs1!SumOfTN
rs2!TH = rs1!SUMOFTH
rs2!TPE = rs1!SUMOFTPE
rs2!TP = rs1!SUMOFTP
rs2!TB = rs1!SUMOFTB
rs2!TGA = rs1!SUMOFTGA

If rs1!st = True Then
rs2!st = 1
SUMMAHST = SUMMAHST + rs1!SumOfMASRAFYDATE
End If

If rs1!PE = True Then
rs2!PE = 1
SUMMAHPE = SUMMAHPE + rs1!SumOfMASRAFYDATE
End If

If rs1!MS = True Then
rs2!MS = 1
SUMMAHMS = SUMMAHMS + rs1!SumOfMASRAFYDATE
End If

DoEvents


''''''''''''''''''''''''''''

'ÈÏÓÊ ÂæÑÏä ÓØÍ æ ÈÑÂæÑÏ ãÌÏÏ
If rs3.State = 1 Then
rs3.Close
End If
SQLSTR = " SELECT tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero" _
& " FROM (tkala INNER JOIN tSATHE_ka_rosta ON tkala.codka = tSATHE_ka_rosta.codka) INNER JOIN tROSTA ON tSATHE_ka_rosta.codro = tROSTA.CODRO" _
& " Where(tSATHE_ka_rosta.npy) = " & SETCOMA(rs1!npy, False, False) & _
" And " & _
" tROSTA.CODRO = " & SETCOMA(Trim(rs1!CODRO), True, False) & _
" And " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) _
& " GROUP BY tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

If Not rs3.EOF = True Then
rs2!sathepy = rs3!sathepy
rs2!bms = rs3!bms
rs2!SUMSB = rs3!SUMSB

If rs1!st = True Then
SUMSBPE = SUMSBPE + rs3!SUMSB
End If

If rs1!PE = True Then
SUMSBST = SUMSBST + rs3!SUMSB
End If

If rs1!MS = True Then
SUMSBMS = SUMSBMS + rs3!SUMSB
End If
Else
rs2!sathepy = 0
rs2!bms = 0
rs2!SUMSB = 0
End If
rs3.Close
'

DoEvents


'ÈÏÓÊ ÂæÑÏä ãÕÑÝí Êǘäæä
If rs3.State = 1 Then
rs3.Close
End If

SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.npy" _
& " FROM (tkala INNER JOIN (tMantagheh INNER JOIN (tROSTA INNER JOIN tSADERE ON tROSTA.CODRO = tSADERE.CODRO) ON tMantagheh.codmn = tSADERE.codmn) ON tkala.codka = tSADERE.CODKA) INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy" _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, tSADERE.npy" _
& " HAVING (tSADERE.npy) = " & SETCOMA(rs1!npy, False, False) & _
" AND " & _
" (tMantagheh.codmn) = " & SETCOMA(Trim(rs1!codmn), True, False) & _
" AND " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) & _
" AND " & _
" (tROSTA.CODRO) = " & SETCOMA(Trim(rs1!CODRO), True, False) & " ORDER BY tkala.codka ASC"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

rs2!MASRAFYTAKA = rs3!SumOfMASRAFYDATE
rs2!TNTAKA = rs3!SumOfTN
rs2!THTAKA = rs3!SUMOFTH
rs2!TPETAKA = rs3!SUMOFTPE
rs2!TPTAKA = rs3!SUMOFTP
rs2!TBTAKA = rs3!SUMOFTB
rs2!TGATAKA = rs3!SUMOFTGA

'ÇÑ áí ÇÊíáä æÝæáÇÏí,ãæÇäÚ ÓÎÊ ÈæÏ
If rs1!st = True Then
rs2!st = 1
SUM1 = SUM1 + rs3!SumOfMASRAFYDATE
End If
If rs1!PE = True Then
rs2!PE = 1
SUM2 = SUM2 + rs3!SumOfMASRAFYDATE
End If
If rs1!MS = True Then
rs2!MS = 1
SUM3 = SUM3 + rs3!SumOfMASRAFYDATE
End If
rs3.Close
'

rs2.UpdateBatch
rs2.Close
DoEvents

If PrGB.Value < 100 Then
PrGB.Value = 5 + PrGB.Value
L.Caption = PrGB.Value
ElseIf PrGB.Value >= 100 Then
Frm.Visible = True
PrGB.Value = 0
Timer3.Enabled = True
End If

rs1.MoveNext
Loop
rs1.Close

End If


' SUM1 = 0
' SUM2 = 0
' HPY = 0
' ãæÇÞÚí íÔ ãí ÂíÏ ˜å ÏÑÇíä ÈÇÒå ãÕÑÝí äÏÇÑíã æ ÈÇíÏ ãÕÑÝí Êǘäæä ˜ÇáÇí Çíä ÑæÓÊÇí íãÇä ãÍÇÓÈå ÑÏÏ
' ÈÏÓÊ ÂæÑÏ ãÕÑÝí Êǘäæä ÂãÇÑåÇíí ˜å ÏÑÇíä ÊÇÑíÎ äãíÈÇÔÏ
If rs1.State = 1 Then
rs1.Close
End If
SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" _
& " FROM tMantagheh INNER JOIN ((tROSTA INNER JOIN (tSADERE INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy) ON tROSTA.CODRO = tSADERE.CODRO) INNER JOIN tkala ON tSADERE.CODKA = tkala.codka) ON tMantagheh.codmn = tSADERE.codmn " _
& " Where (tSADERE.CODMN) = " & SETCOMA(Trim(Left(Cmbman.List(Cmbman.ListIndex), 2)), True, False) _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" & " ORDER BY tSADERE.codka ASC"

rs1.Open SQLSTR, cn1, adOpenDynamic, adLockOptimistic

If rs1.EOF = True Then

rs1.Close

Else

rs1.MoveFirst
Do While Not rs1.EOF

If rs2.State = 1 Then
rs2.Close
End If

SQLSTR = " SELECT * FROM t_TEMPHAFTEGY " & _
" Where " & _
" CODKA = " & SETCOMA(rs1!CODKA, True, False) & _
" AND " & _
" NPY = " & SETCOMA(rs1!npy, False, False) & _
" AND " & _
" NAMESH = " & SETCOMA(rs1!namesh, True, False) & _
" AND " & _
" CODMN = " & SETCOMA(rs1!codmn, True, False) & _
" AND " & _
" CODRO = " & SETCOMA(Trim(rs1!CODRO), True, False)

rs2.Open SQLSTR, cn2, adOpenDynamic, adLockOptimistic

If rs2.EOF = True Then

rs2.AddNew

rs2!npy = rs1!npy
rs2!namesh = rs1!namesh
rs2!codmn = rs1!codmn
rs2!namemn = rs1!namemn
rs2!CODRO = rs1!CODRO
rs2!namero = rs1!namero

rs2!HPY = rs1!HPY
HPY = rs1!HPY

rs2!nameshahr = rs1!nameshahr
rs2!CODKA = "" & rs1!CODKA
rs2!nameka = "" & rs1!nameka
rs2!vahed = "" & rs1!vahed
rs2!DATESH = "" & rs1!DATESH
rs2!DATEAN = "" & rs1!DATEAN
rs2!MABPY = rs1!MABPY

If rs1!st = True Then
rs2!st = 1
'SUMMAHST = SUMMAHST + rs1!SumOfMASRAFYDATE
End If

If rs1!PE = True Then
rs2!PE = 1
'SUMMAHPE = SUMMAHPE + rs1!SumOfMASRAFYDATE
End If

If rs1!MS = True Then
rs2!MS = 1
'SUMMAHMS = SUMMAHMS + rs1!SumOfMASRAFYDATE
End If

rs2!masrafyhaftgy = 0


'ÈÏÓÊ ÂæÑÏä ãÕÑÝí ÊÇ ˜äæä
If rs3.State = 1 Then
rs3.Close
End If
SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.npy" _
& " FROM (tkala INNER JOIN (tMantagheh INNER JOIN (tROSTA INNER JOIN tSADERE ON tROSTA.CODRO = tSADERE.CODRO) ON tMantagheh.codmn = tSADERE.codmn) ON tkala.codka = tSADERE.CODKA) INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy" _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, tSADERE.npy" _
& " HAVING (tSADERE.npy) = " & SETCOMA(rs1!npy, False, False) & _
" AND " & _
" (tMantagheh.codmn) = " & SETCOMA(Trim(rs1!codmn), True, False) & _
" AND " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) & _
" AND " & _
" (tROSTA.CODRO) = " & SETCOMA(Trim(rs1!CODRO), True, False) & "ORDER BY tkala.codka ASC"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

rs2!MASRAFYTAKA = rs3!SumOfMASRAFYDATE
rs2!TNTAKA = rs3!SumOfTN
rs2!THTAKA = rs3!SUMOFTH
rs2!TPETAKA = rs3!SUMOFTPE
rs2!TPTAKA = rs3!SUMOFTP
rs2!TBTAKA = rs3!SUMOFTB
rs2!TGATAKA = rs3!SUMOFTGA
rs3.Close
'

DoEvents

'ÈÏÓÊ ÂæÑÏä ÓØÍ æ ÈÑÂæÑÏ ãÌÏÏ
If rs3.State = 1 Then
rs3.Close
End If
SQLSTR = " SELECT tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero" _
& " FROM (tkala INNER JOIN tSATHE_ka_rosta ON tkala.codka = tSATHE_ka_rosta.codka) INNER JOIN tROSTA ON tSATHE_ka_rosta.codro = tROSTA.CODRO" _
& " Where(tSATHE_ka_rosta.npy) = " & SETCOMA(rs1!npy, False, False) & _
" And " & _
" tROSTA.CODRO = " & SETCOMA(Trim(rs1!CODRO), True, False) & _
" And " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) _
& " GROUP BY tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

If Not rs3.EOF = True Then
rs2!sathepy = rs3!sathepy
rs2!bms = rs3!bms
rs2!SUMSB = rs3!SUMSB

If rs1!st = True Then
SUMSBPE = SUMSBPE + rs3!SUMSB
End If

If rs1!PE = True Then
SUMSBST = SUMSBST + rs3!SUMSB
End If

If rs1!MS = True Then
SUMSBMS = SUMSBMS + rs3!SUMSB
End If

Else

rs2!sathepy = 0
rs2!bms = 0
rs2!SUMSB = 0

End If
rs3.Close
'
rs2.UpdateBatch
rs2.Close
End If

DoEvents

If PrGB.Value < 100 Then
PrGB.Value = 5 + PrGB.Value
L.Caption = PrGB.Value
ElseIf PrGB.Value >= 100 Then
Frm.Visible = True
PrGB.Value = 0
Timer3.Enabled = True
End If

DoEvents

rs1.MoveNext
Loop
rs1.Close

End If
'

If PrGB.Value < 100 Then
PrGB.Value = 100
Frm.Visible = False
Timer3.Enabled = False
End If

msg = ".ÒÇÑÔ ÂãÇÏå Ç ãí ÈÇÔÏ"
Call MsgBox(msg, vbOKOnly + vbCritical + vbMsgBoxRight, "ãÔÇæÑ ÇÒ - æÇÑÔ")

FrmG.Visible = True

'ÏÑÕÏ íÔÑÝÊ ÝíÒí˜í
'QQ = ((SUM1 + SUM2) * 100) / HPY
'SUMKOL = SUM1 + SUM2
'Unload Me
'Call PRINTHAFTGY

End Sub

[look20]

این فایلم مشکل داره

[vbnovin]

Public Sub TARYKH_HAFTGY()



Dim b As Boolean

گزارش بر اساس تاریخ و منطقه
If Mask2 = "__/__/__" Or Mask3 = "__/__/__" Then
MsgBox ".ÊÇÑíÎ ÒÇÑÔ ÑÇÊÚííä äãÇííÏ", vbOKOnly + vbInformation + vbMsgBoxRight, "ãÔÇæÑ ÇÒ - æÇÑÔ"
Op6.Value = False
Exit Sub
End If

date2 = Mask2
date3 = Mask3

Frm.Visible = True
PrGB.Value = 0
Timer3.Enabled = True

'ÍÐÝ ÌÏæá ãÌÇÒí
SQLSTR = " DELETE * FROM t_TEMPHAFTEGY "
cn2.Execute SQLSTR
'

If rs1.State = 1 Then
rs1.Close
End If

SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" _
& " FROM tMantagheh INNER JOIN ((tROSTA INNER JOIN (tSADERE INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy) ON tROSTA.CODRO = tSADERE.CODRO) INNER JOIN tkala ON tSADERE.CODKA = tkala.codka) ON tMantagheh.codmn = tSADERE.codmn " _
& " Where(tSADERE.TARYKH) >= " & SETCOMA(Mask2, True, False) & _
" And " & _
" (tSADERE.tarykh) <= " & SETCOMA(Mask3, True, False) & _
" And " & _
" (tSADERE.CODMN) = " & SETCOMA(Trim(Left(Cmbman.List(Cmbman.ListIndex), 2)), True, False) _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" & " ORDER BY tSADERE.CODKA ASC"

rs1.Open SQLSTR, cn1, adOpenDynamic, adLockOptimistic

If rs1.EOF = True Then

rs1.Close
MsgBox ".ãÕÑÝí Êǘäæä ÏÑ Çíä ÈÇÒå ÊÇÑíÎí ËÈÊ äÑÏíÏå ÇÓÊ", vbOKOnly + vbInformation + vbMsgBoxRight, "ãÔÇæÑ ÇÒ - æÇÑÔ"
'Exit Sub
b = True
End If
''''''''''''''''''''''''''

If b <> True Then

SUM1 = 0
SUM2 = 0
HPY = 0
rs1.MoveFirst
Do While Not rs1.EOF

DoEvents

If rs2.State = 1 Then
rs2.Close
End If

SQLSTR = " SELECT * FROM t_TEMPHAFTEGY "

rs2.Open SQLSTR, cn2, adOpenDynamic, adLockOptimistic

rs2.AddNew

DoEvents

rs2!npy = rs1!npy
rs2!namesh = rs1!namesh
rs2!codmn = rs1!codmn
rs2!namemn = rs1!namemn
rs2!CODRO = rs1!CODRO
rs2!namero = rs1!namero

rs2!HPY = rs1!HPY
HPY = rs1!HPY

rs2!nameshahr = rs1!nameshahr
rs2!CODKA = "" & rs1!CODKA
rs2!nameka = "" & rs1!nameka
rs2!vahed = "" & rs1!vahed
rs2!DATESH = "" & rs1!DATESH
rs2!DATEAN = "" & rs1!DATEAN
rs2!MABPY = rs1!MABPY

'ãÕÑÝí ÏÑ Çíä ÈÇÒå ÊÇÑíÎ
rs2!masrafyhaftgy = rs1!SumOfMASRAFYDATE
'ÂãÇÑ ÊÓÊ ÏÑ Çíä ÈÇÒå ÊÇÑíÎí
rs2!TN = rs1!SumOfTN
rs2!TH = rs1!SUMOFTH
rs2!TPE = rs1!SUMOFTPE
rs2!TP = rs1!SUMOFTP
rs2!TB = rs1!SUMOFTB
rs2!TGA = rs1!SUMOFTGA

If rs1!st = True Then
rs2!st = 1
SUMMAHST = SUMMAHST + rs1!SumOfMASRAFYDATE
End If

If rs1!PE = True Then
rs2!PE = 1
SUMMAHPE = SUMMAHPE + rs1!SumOfMASRAFYDATE
End If

If rs1!MS = True Then
rs2!MS = 1
SUMMAHMS = SUMMAHMS + rs1!SumOfMASRAFYDATE
End If

DoEvents


''''''''''''''''''''''''''''

'ÈÏÓÊ ÂæÑÏä ÓØÍ æ ÈÑÂæÑÏ ãÌÏÏ
If rs3.State = 1 Then
rs3.Close
End If
SQLSTR = " SELECT tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero" _
& " FROM (tkala INNER JOIN tSATHE_ka_rosta ON tkala.codka = tSATHE_ka_rosta.codka) INNER JOIN tROSTA ON tSATHE_ka_rosta.codro = tROSTA.CODRO" _
& " Where(tSATHE_ka_rosta.npy) = " & SETCOMA(rs1!npy, False, False) & _
" And " & _
" tROSTA.CODRO = " & SETCOMA(Trim(rs1!CODRO), True, False) & _
" And " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) _
& " GROUP BY tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

If Not rs3.EOF = True Then
rs2!sathepy = rs3!sathepy
rs2!bms = rs3!bms
rs2!SUMSB = rs3!SUMSB

If rs1!st = True Then
SUMSBPE = SUMSBPE + rs3!SUMSB
End If

If rs1!PE = True Then
SUMSBST = SUMSBST + rs3!SUMSB
End If

If rs1!MS = True Then
SUMSBMS = SUMSBMS + rs3!SUMSB
End If
Else
rs2!sathepy = 0
rs2!bms = 0
rs2!SUMSB = 0
End If
rs3.Close
'

DoEvents


'ÈÏÓÊ ÂæÑÏä ãÕÑÝí Êǘäæä
If rs3.State = 1 Then
rs3.Close
End If

SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.npy" _
& " FROM (tkala INNER JOIN (tMantagheh INNER JOIN (tROSTA INNER JOIN tSADERE ON tROSTA.CODRO = tSADERE.CODRO) ON tMantagheh.codmn = tSADERE.codmn) ON tkala.codka = tSADERE.CODKA) INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy" _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, tSADERE.npy" _
& " HAVING (tSADERE.npy) = " & SETCOMA(rs1!npy, False, False) & _
" AND " & _
" (tMantagheh.codmn) = " & SETCOMA(Trim(rs1!codmn), True, False) & _
" AND " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) & _
" AND " & _
" (tROSTA.CODRO) = " & SETCOMA(Trim(rs1!CODRO), True, False) & " ORDER BY tkala.codka ASC"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

rs2!MASRAFYTAKA = rs3!SumOfMASRAFYDATE
rs2!TNTAKA = rs3!SumOfTN
rs2!THTAKA = rs3!SUMOFTH
rs2!TPETAKA = rs3!SUMOFTPE
rs2!TPTAKA = rs3!SUMOFTP
rs2!TBTAKA = rs3!SUMOFTB
rs2!TGATAKA = rs3!SUMOFTGA

'ÇÑ áí ÇÊíáä æÝæáÇÏí,ãæÇäÚ ÓÎÊ ÈæÏ
If rs1!st = True Then
rs2!st = 1
SUM1 = SUM1 + rs3!SumOfMASRAFYDATE
End If
If rs1!PE = True Then
rs2!PE = 1
SUM2 = SUM2 + rs3!SumOfMASRAFYDATE
End If
If rs1!MS = True Then
rs2!MS = 1
SUM3 = SUM3 + rs3!SumOfMASRAFYDATE
End If
rs3.Close
'

rs2.UpdateBatch
rs2.Close
DoEvents

If PrGB.Value < 100 Then
PrGB.Value = 5 + PrGB.Value
L.Caption = PrGB.Value
ElseIf PrGB.Value >= 100 Then
Frm.Visible = True
PrGB.Value = 0
Timer3.Enabled = True
End If

rs1.MoveNext
Loop
rs1.Close

End If


' SUM1 = 0
' SUM2 = 0
' HPY = 0
' ãæÇÞÚí íÔ ãí ÂíÏ ˜å ÏÑÇíä ÈÇÒå ãÕÑÝí äÏÇÑíã æ ÈÇíÏ ãÕÑÝí Êǘäæä ˜ÇáÇí Çíä ÑæÓÊÇí íãÇä ãÍÇÓÈå ÑÏÏ
' ÈÏÓÊ ÂæÑÏ ãÕÑÝí Êǘäæä ÂãÇÑåÇíí ˜å ÏÑÇíä ÊÇÑíÎ äãíÈÇÔÏ
If rs1.State = 1 Then
rs1.Close
End If
SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" _
& " FROM tMantagheh INNER JOIN ((tROSTA INNER JOIN (tSADERE INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy) ON tROSTA.CODRO = tSADERE.CODRO) INNER JOIN tkala ON tSADERE.CODKA = tkala.codka) ON tMantagheh.codmn = tSADERE.codmn " _
& " Where (tSADERE.CODMN) = " & SETCOMA(Trim(Left(Cmbman.List(Cmbman.ListIndex), 2)), True, False) _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tSADERE.CODKA,tSADERE.NAMEKA,tSADERE.VAHED, tMantagheh.namemn, tNewPyman.npy, tNewPyman.namesh, tNewPyman.hpy, tNewPyman.NAMESHAHR, tNewPyman.DATESH, tNewPyman.DATEAN, tNewPyman.MABPY, tSADERE.PE, tSADERE.ST, tSADERE.MS, tSADERE.codmn" & " ORDER BY tSADERE.codka ASC"

rs1.Open SQLSTR, cn1, adOpenDynamic, adLockOptimistic

If rs1.EOF = True Then

rs1.Close

Else

rs1.MoveFirst
Do While Not rs1.EOF

If rs2.State = 1 Then
rs2.Close
End If

SQLSTR = " SELECT * FROM t_TEMPHAFTEGY " & _
" Where " & _
" CODKA = " & SETCOMA(rs1!CODKA, True, False) & _
" AND " & _
" NPY = " & SETCOMA(rs1!npy, False, False) & _
" AND " & _
" NAMESH = " & SETCOMA(rs1!namesh, True, False) & _
" AND " & _
" CODMN = " & SETCOMA(rs1!codmn, True, False) & _
" AND " & _
" CODRO = " & SETCOMA(Trim(rs1!CODRO), True, False)

rs2.Open SQLSTR, cn2, adOpenDynamic, adLockOptimistic

If rs2.EOF = True Then

rs2.AddNew

rs2!npy = rs1!npy
rs2!namesh = rs1!namesh
rs2!codmn = rs1!codmn
rs2!namemn = rs1!namemn
rs2!CODRO = rs1!CODRO
rs2!namero = rs1!namero

rs2!HPY = rs1!HPY
HPY = rs1!HPY

rs2!nameshahr = rs1!nameshahr
rs2!CODKA = "" & rs1!CODKA
rs2!nameka = "" & rs1!nameka
rs2!vahed = "" & rs1!vahed
rs2!DATESH = "" & rs1!DATESH
rs2!DATEAN = "" & rs1!DATEAN
rs2!MABPY = rs1!MABPY

If rs1!st = True Then
rs2!st = 1
'SUMMAHST = SUMMAHST + rs1!SumOfMASRAFYDATE
End If

If rs1!PE = True Then
rs2!PE = 1
'SUMMAHPE = SUMMAHPE + rs1!SumOfMASRAFYDATE
End If

If rs1!MS = True Then
rs2!MS = 1
'SUMMAHMS = SUMMAHMS + rs1!SumOfMASRAFYDATE
End If

rs2!masrafyhaftgy = 0


'ÈÏÓÊ ÂæÑÏä ãÕÑÝí ÊÇ ˜äæä
If rs3.State = 1 Then
rs3.Close
End If
SQLSTR = " SELECT tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, Sum(tSADERE.MASRAFYDATE) AS SumOfMASRAFYDATE,Sum(tSADERE.TN) AS SumOfTN,Sum(tSADERE.TH) AS SumOfTH,Sum(tSADERE.TPE) AS SumOfTPE,Sum(tSADERE.TP) AS SumOfTP,Sum(tSADERE.TB) AS SumOfTB,Sum(tSADERE.TGA) AS SumOfTGA, tSADERE.npy" _
& " FROM (tkala INNER JOIN (tMantagheh INNER JOIN (tROSTA INNER JOIN tSADERE ON tROSTA.CODRO = tSADERE.CODRO) ON tMantagheh.codmn = tSADERE.codmn) ON tkala.codka = tSADERE.CODKA) INNER JOIN tNewPyman ON tSADERE.npy = tNewPyman.npy" _
& " GROUP BY tROSTA.CODRO, tROSTA.NAMERO, tMantagheh.codmn, tMantagheh.namemn, tkala.codka, tkala.nameka, tkala.VAHED, tSADERE.npy" _
& " HAVING (tSADERE.npy) = " & SETCOMA(rs1!npy, False, False) & _
" AND " & _
" (tMantagheh.codmn) = " & SETCOMA(Trim(rs1!codmn), True, False) & _
" AND " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) & _
" AND " & _
" (tROSTA.CODRO) = " & SETCOMA(Trim(rs1!CODRO), True, False) & "ORDER BY tkala.codka ASC"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

rs2!MASRAFYTAKA = rs3!SumOfMASRAFYDATE
rs2!TNTAKA = rs3!SumOfTN
rs2!THTAKA = rs3!SUMOFTH
rs2!TPETAKA = rs3!SUMOFTPE
rs2!TPTAKA = rs3!SUMOFTP
rs2!TBTAKA = rs3!SUMOFTB
rs2!TGATAKA = rs3!SUMOFTGA
rs3.Close
'

DoEvents

'ÈÏÓÊ ÂæÑÏä ÓØÍ æ ÈÑÂæÑÏ ãÌÏÏ
If rs3.State = 1 Then
rs3.Close
End If
SQLSTR = " SELECT tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero" _
& " FROM (tkala INNER JOIN tSATHE_ka_rosta ON tkala.codka = tSATHE_ka_rosta.codka) INNER JOIN tROSTA ON tSATHE_ka_rosta.codro = tROSTA.CODRO" _
& " Where(tSATHE_ka_rosta.npy) = " & SETCOMA(rs1!npy, False, False) & _
" And " & _
" tROSTA.CODRO = " & SETCOMA(Trim(rs1!CODRO), True, False) & _
" And " & _
" (tkala.codka) = " & SETCOMA(rs1!CODKA, True, False) _
& " GROUP BY tSATHE_ka_rosta.sathepy, tSATHE_ka_rosta.bms, tSATHE_ka_rosta.SUMSB, tSATHE_ka_rosta.namero"

rs3.Open SQLSTR, cn3, adOpenDynamic, adLockOptimistic

If Not rs3.EOF = True Then
rs2!sathepy = rs3!sathepy
rs2!bms = rs3!bms
rs2!SUMSB = rs3!SUMSB

If rs1!st = True Then
SUMSBPE = SUMSBPE + rs3!SUMSB
End If

If rs1!PE = True Then
SUMSBST = SUMSBST + rs3!SUMSB
End If

If rs1!MS = True Then
SUMSBMS = SUMSBMS + rs3!SUMSB
End If

Else

rs2!sathepy = 0
rs2!bms = 0
rs2!SUMSB = 0

End If
rs3.Close
'
rs2.UpdateBatch
rs2.Close
End If

DoEvents

If PrGB.Value < 100 Then
PrGB.Value = 5 + PrGB.Value
L.Caption = PrGB.Value
ElseIf PrGB.Value >= 100 Then
Frm.Visible = True
PrGB.Value = 0
Timer3.Enabled = True
End If

DoEvents

rs1.MoveNext
Loop
rs1.Close

End If
'

If PrGB.Value < 100 Then
PrGB.Value = 100
Frm.Visible = False
Timer3.Enabled = False
End If

msg = ".ÒÇÑÔ ÂãÇÏå Ç ãí ÈÇÔÏ"
Call MsgBox(msg, vbOKOnly + vbCritical + vbMsgBoxRight, "ãÔÇæÑ ÇÒ - æÇÑÔ")

FrmG.Visible = True

'ÏÑÕÏ íÔÑÝÊ ÝíÒí˜í
'QQ = ((SUM1 + SUM2) * 100) / HPY
'SUMKOL = SUM1 + SUM2
'Unload Me
'Call PRINTHAFTGY

End Sub


----------------------------

اینم تابع برای تنظیم کوتیشن های رشته ای و عددی برای پارامترهای که بالا استفاده کردم امیدوارم این نمونه سورس کمکتان کنه در ضمن در تایپک دیگر نونمه سورس گذاشتم از یک برنامه ام امیدوارم مفید باشه:

http://rapidshare.com/files/385020827/for_upload.rar.html


Public Function SETCOMA(STRX As Variant, Y1 As Boolean, y2 As Boolean) As Variant

If (STRX <> False) And (STRX <> True) Then
STRX = Trim(STRX)
End If

If Y1 = True Then
STRX = "'" & STRX & "'"
End If

If y2 = True Then
STRX = STRX & ","
End If

SETCOMA = STRX

End Function

[look20]

مشکل حل شد فهمیدم کجا مشکل داشتم

[xxxxx_xxxxx]

سلام دوست عزیز،
برنامه تون هیچ مشکلی نداره و دستورات به طور صحیح اجرا میشن. و این موضوع ارتباطی با نام فیلد تاریخ نداره. (شاید به خاطر تست های مختلف به نام فیلد شک کردید)

به هر حال، فقط کافیست محتوای فیلد تاریخ رو با این فرمت وارد کنید (##/##/####). روز و ماه دو رقمی باشه، سال چهار رقمی.
مثلاً بجای 1389/2/18 بنویسید: 1389/02/18

موفق باشید/

[look20]

قبل از پست شما مشکل رو فهمیدم
حق با شما بود
راستش مشکل من عدم دقت توی اضاف کردن کد فرم بود توی دیتابیس تاریخ رو بصورت 6 رقمی وارد نکرده بودم و تکس باکس سرچ کننده بصورت 6 رقمی سرچ میکرد به همین خاطر چیزی پیدا نمیکرد
بازم ممنون که توجه کردید

[vbnovin]

این فایلم مشکل داره


این فایلتان را اصلاح کردم مشکلش درست شد در ضمن توضیحات گذاشتم

بد نیست به روش کار من هم نگاه کنید .... نظر بدهید

[look20]

ببینید دوست عزیز من قرار حدود 250 دستور if استفاده کنم که برای هر کدوم حدود 5 خط کد باید ساخته بشه حالا حساب کنید 250*5 چقدر میشه حالا به استثنای سایر قسمتهای کد نویسی انوقت چطور انتظار دارید بخام از روش طولانی استفاده کنم روش شما خیلی خوبه ولی همیشه توی کد نویسیهایی که مثل مال من این قدر کد نیاز داره باید سعی کنم از کوتاه ترین و کمترین روش کدنویسی استفاده کنم
ممنون