Database Query Problem

[Rajeev Dayal]

This is the problem regarding opening a database Access7, with a query in ADODB.
I'm using the given code to open the database (db)Access7.0. Now the problem arises that even though there is a record fulfilling the given criteria the record it is not showing in the grid box...at do events..
The search criteria should go in this way:- SELECT * FROM medicine WHERE expdate >=[5 days less then date()] AND expdate <= date().

The date comes perfectly all right but I'm sure the problem is with the query only. Can somebody help me with this? Just mail me at: - mail2rajeevdayal@yahoo.com & rectify this problem, as I'm in a middle of no-where situation.

Thanks.
********************************************
Option Explicit
Private m_DBConnection As ADODB.Connection


Private Sub Form_Load()
On Error Resume Next
Dim rs As ADODB.Recordset
Dim r As Integer
Dim c As Integer
Dim num_cols As Integer
Dim col_wid() As Single
Dim new_wid As Single
Dim a As String
Dim db_file
Dim s1, name As String

db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "medical.mdb"

Set m_DBConnection = New ADODB.Connection
m_DBConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
m_DBConnection.Open

s1 = Format$(Date, "dd/mm/yyyy")
Dim b, c1, d
Dim a1 As Variant

'MsgBox "s1= '" & s1 & "'"
a1 = Mid(s1, 1, 2)
'MsgBox "a1= '" & a1 & "'"
b = Mid(s1, 4, 2)
If Len(b) < 2 Then
b = "0" & b
'MsgBox "b= '" & b & "'"
End If
'MsgBox "b= '" & b & "'"
c1 = DatePart("yyyy", s1)
'MsgBox "c1= '" & c1 & "'"
Dim a2
a2 = a1 - 5''''but there is still 1 prob i.e. if date=01 then what will a2 return???????
If Len(a2) < 2 Then
a2 = "0" & a2
End If
'MsgBox "a2= '" & a2 & "'"
's1 = "expdate <= " & s1
MsgBox "s1= " & s1

Dim d1
'd1 = "expdate >= " & a2 & "/" & b & "/" & c1
d1 = a2 & "/" & b & "/" & c1
MsgBox "d1= " & d1
Dim n
n = "21/6/2001"

a = "'SELECT medicineid, medicinename, companyname, saltname, medicinetype, mfddate, expdate, rackno, distributorid FROM medicine WHERE expdate >= '" & d1 & "' AND expdate<= '" & s1 & "';"
'a = "SELECT medicineid, medicinename, companyname, saltname, medicinetype, mfddate, expdate, rackno, distributorid FROM medicine WHERE " & d1 & " AND " & s1 & ""
'a = "'SELECT * FROM medicine WHERE expdate = '" & n & "'"
'a = "'SELECT * FROM medicine WHERE expdate >= '" & d1 & "';"
'a = "SELECT * FROM medicine WHERE " & d1 & " AND " & s1 & ""
MsgBox a
' Open the Recordset.
Set rs = m_DBConnection.Execute( _
a, , adCmdText)

DoEvents
' Display the results.
If rs.EOF Then
flxResults.Rows = 1
flxResults.Cols = 1
flxResults.TextMatrix(0, 0) = "No Records Found."
num_cols = 1
ReDim col_wid(0 To 0)
col_wid(0) = TextWidth(flxResults.TextMatrix(0, 0))
Else
' Make room for column widths.
num_cols = rs.Fields.Count
ReDim col_wid(0 To num_cols - 1)

' Set column headers.
flxResults.Rows = 2
flxResults.Cols = num_cols
flxResults.FixedCols = 0
flxResults.FixedRows = 1
For c = 0 To num_cols - 1
flxResults.TextMatrix(0, c) = rs.Fields(c).name

' See if we need to enlarge the column.
new_wid = TextWidth(rs.Fields(c).name)
If col_wid(c) < new_wid Then col_wid(c) = new_wid
Next c

' Display the data.
Do Until rs.EOF
r = r + 1
flxResults.Rows = r + 1
For c = 0 To rs.Fields.Count - 1
flxResults.TextMatrix(r, c) = rs.Fields(c).Value

' See if we need to enlarge the column.
new_wid = TextWidth(rs.Fields(c).Value)
If col_wid(c) < new_wid Then col_wid(c) = new_wid
Next c
rs.MoveNext
Loop
End If

' Set the grid's column widths.
For c = 0 To num_cols - 1
flxResults.ColWidth(c) = col_wid(c) + 120
Next
rs.Close
End Sub

Private Sub Form_Resize()
On Error Resume Next
flxResults.Move 0, _
flxResults.Top, _
ScaleWidth, _
ScaleHeight - flxResults.Top
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
If Not m_DBConnection Is Nothing Then
m_DBConnection.Close
End If

MDIForm1.StatusBar1.Panels("msg").Text = ""
End Sub
********************************************

[Aaron Young]

Try:
sSQLString = "SELECT medicineid, medicinename, companyname, saltname, medicinetype, mfddate, expdate, rackno, distributorid FROM medicine WHERE expdate >= #" & Format(DateAdd("d", -5, date), "dd/mm/yyyy") & "# AND expdate<= #" & Format(date, "dd/mm/yyyy") & "#"



Aaron Young
Senior Programmer Analyst
ajyoung@charter.net
Certified AllExperts Expert: http://www.allexperts.com/displayExpert.asp?Expert=11884