Filtering

[Rob Brown]

Hi,

Im building a VB5 front end for an Access 97 database.

I'm presenting the user with a form to filter records by date.

The form is called frmFilterByDate and contains the following controls:

Data Control: dtaFilterByDate
DBCombo: dbcDate
Command Button: cmdOK

The form the filter is to be applied to is called frmDialInChecks and contains the following controls:

Data Control: dtaDialInChecks
DBGrid: dbgDialInChecks

To call the filtering form and process the date the user selects I am using the following code:

frmFilterByDate.Show (vbModal)

Do While frmFilterByDate.booOKClicked = False
DoEvents
Loop

dtaDialInChecks.RecordSource = frmFilterByDate.strFilterByDate
dtaDialInChecks.Refresh

The filtering form itself contains the following code:

Public strFilterByDate As Variant
Public booOKClicked As Boolean

Private Sub cmdOK_Click()

strFilterByDate = "SELECT * FROM tblDialInChecks WHERE Date = '" & dbcDate.Text & "';"
booOKClicked = True
frmFilterByDate.Hide

End Sub

Private Sub Form_Load()

booOKClicked = False

End Sub

On execution the code all runs fine. The RecordSource property of dtaDialInChecks is update. However, when the code reaches the line dtaDialInChecks.Refresh, the following error is produced:

Run-time error '3011':

The Microsoft Jet database engine could not find the object 'SELECT * FROM tblDialInChecks WHERE Date = '6/1/00';'. Make sure that the object exists and that you spell its name and the path correctly.

The object does exist and I have spelt it's name and the path correctly.

Why is this happenning. Is there anything I can do about it?

Any help would be most appreciated.

Best Regards,

Rob Brown.

[Clunietp]

change this:
'SELECT * FROM tblDialInChecks WHERE Date = '6/1/00';

to this:
'SELECT * FROM tblDialInChecks WHERE [Date] = '6/1/00';

I believe DATE is a special keyword

Also try replacing the single quotes ( ' ) with pound signs ( # )

[Edneeis]

I believe Clunietp is right you probably can't reference the field if it is named Date because it is a keyword and you do have to the pound signs:

"SELECT * FROM tblDialInChecks WHERE [Date] = " & CDate(dbcDate.Text)

What are you using for your data ADO,DAO, DataEnvironment?
But it might be easier to do this instead:
This assumes that ADO is connected to tblDialInChecks

ADO.Filter="Date=" & CDate(dbcDate.text)