[vbmania]

How to get the interval number of days leaped between the two date using SQL Statement in VB6 ??

currently i used ADODC Data Control in my project..

i had tried several SQL Date Built-In Function found in w3schools.com but still useless

such not functioning code is ..

Dim dTglNow As Date
Dim commSQL As String

 dTglNow = CDate( Format(Now,"dd-mmm-yy") )

commSQL = "SELECT ID_BUKU, JUDUL_BUKU, TGL_PINJAM, DATEDIFF(dd," & CStr(dTglNow) _  &",TGL_PINJAM) AS [SELANG HARI] FROM PINJAM"

AdocData1.RecordSource = commSQL
AdocData1.Refresh

Finally its viewed the ERROR MESSAGE As : wrong number of arguments used with function in query expression 'DATEDIFF(23-11-10,TGL_PINJAM)'

<please .. urgently requiring the answer >

[maj3091]

Have you tried the SQL Query direct in SQL Server or the Database you're using, just to verify that the query itself is OK?

Also try to ensure that the dates in DATEDIFF are wrapped in single quotes (ie.: '23-11-2010')

[vbmania]

no, i rather used "Microsoft.Jet.OLEDB.4.0" as a SQL Emulator, does it originally has a limitation in performing SQL command or should i install MSDE ( Microsoft SQL Desktop Edition ) ?? or other third party SQL Server Management System ?? thanks ..

[maj3091]

I only asked the question as you haven't said what DB your connecting to, and it's an easy way to test your sql query.

Did you try modifying the Datediff function as I suggested?

[vbmania]

i'm connect to MS. Access 2003 DB, i have tried your suggestion but still not work..

[maj3091]

Are you still getting the same error??

Put a breakpoint on the line

commSQL = 

and then print this to the immediate window and post it here.

Please try to give as much information as you can when you post back...don't just say it still doesn't work, it doesn't really help at all.

[raziel_]

check if the two dates have same format

[vbmania]

first of all thanks..
there are two parts of SQL i had used to filter the data that matches the desired criteria,
1. Display the interval number of days that leaped between the two date and had succeeded.

Private Sub Command1_Click()
Dim commSQL As String
Dim dTgl As Date

dTgl = CDate(Format(Now, "dd-mmm-yy"))

commSQL = "SELECT ID_BUKU, JUDUL_BUKU, TGL_PINJAM," & "(#" & CStr(dTgl) & "#" & " - TGL_PINJAM) AS [SELANG HARI] FROM PINJAM"

Adodc1.RecordSource = commSQL

Adodc1.Refresh

Query_Interval CInt(txtAwal), CInt(txtAkhir)

End Sub

2. To display the data that matches the criteria for [SELANG HARI] BETWEEN <n1> AND <n2>
For this still have a problem, and i had done what you've said to place a breakpoint on commSQL =

Sub Query_Interval(ByVal nStart As Integer, ByVal nTo As Integer)
Dim commSQL As String
Dim sDay1 As String, sDay2 As String

sDay1 = Trim(CStr(nStart))

sDay2 = Trim(CStr(nTo))

commSQL = "SELECT ID_BUKU, JUDUL_BUKU, ID_PEL, TGL_PINJAM FROM PINJAM"
commSQL = commSQL & " WHERE [SELANG HARI] BETWEEN " & sDay1 & " AND " & sDay2

Adodc1.RecordSource = commSQL

Adodc1.Refresh

End Sub

Breakpoint on two "commSQL = " has passed successfully, but it stop on the Adodc1.Refresh
it says that " No value given for one or more required parameters ."
and " Method Refresh of IAdodc Failed.

[maj3091]

The reason I asked you to put the breakpoint on the commSQL = line, is so we can see the value of commSQL.

If you break on that line then step over it, go to the immediate window and type "? commSQL" and press enter it will print the value of commSQL to the immediate window. Copy and past that value and post it here please, then we can validate your SQL is correct.