Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

search date problem

Status
Not open for further replies.

Kindi

Programmer
Jan 31, 2001
54
GB
I have a created a search engine on a form in access. When the search command button is hit, it filters all records that matches the criteria given, and opens a form in read only mode, with all the filtered records. There is a point where a user can enter a date into a textfield and search by that date, but there is an error, the debugger indicates an error:

"the open form action was canceled"

The rest of the search works fine, the error occurs when a date is inputted.

This is the code that creates a sql statement to filter the records:

If Not IsNull(Me.Text116) Then
If criteria <> &quot;&quot; Then
criteria = criteria & &quot; AND &quot;
End If
criteria = criteria & &quot;datefrom = '&quot; & Me.Text116 & &quot;'&quot;
End If


Here is the command to open the form:

DoCmd.OpenForm &quot;viewMain&quot;, , , criteria

Please help!
 
Apparently datefrom type is Date. In such case:

criteria = criteria & &quot;datefrom = #&quot; & Me.Text116 & &quot;#&quot;

Aivars
 
I am afraid i have tried that, this does not give an error, but does not return any results

Thanks
 
Are you sure that your SQL have any record. Breakpoint codes before DoCmd.OpenForm &quot;viewMain&quot;, , , criteria,
then write ? &quot;Select * From YourTableName Where &quot; & criteria, press <Enter>, copy result and paste it into SQL window (open before queries constructor as New), run pasted query or open it in Design View and analise results or errors!

Good luck!
Aivars
 
Are you sure that your SQL have any record. Breakpoint codes before DoCmd.OpenForm &quot;viewMain&quot;, , , criteria,
then write: ? &quot;Select * From YourTableName Where &quot; & criteria & &quot;;&quot; on Immediate window (you can open this window with pushing Ctrl+G), press <Enter>, copy result and paste it into SQL window (open before queries constructor as New), run pasted query or open it in Design View and analise results or errors!

Good luck!
Aivars
 
Hi Aivars

I have no breakpoints in the code, and the criteria string holds the correct data that has been entered into the date textbox

?

Kindi
 
Kindi!

Press <F9> on VBA window or click mouse left button on grey border left codes' clause DoCmd.OpenForm &quot;viewMain&quot;, , , criteria. After them on border will appear big red point - it's breakpoint. Whenever program comes to pointed row it will suspend. Then follow instruction what I gave you before!

To clear breakpoints press <Ctrl>+<Shift>+<F9> or <Debug>;<Clear All BreakPoints>. It's necessary because in opposite way program will be stoped every time.

Aivars
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top