Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 12th, 2008, 11:04 PM
Registered User
Join Date: Mar 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default how I can search required information form table

I have make table & form in MS Access and have two text field one is from------ and other is to----- and I want to search data Date wise for example 15/10/2000 to 15/10/20007 and press search button but no result found.
please help me to write query.

thanks regards

Old March 13th, 2008, 06:31 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

It is helpful if there are actual field and table names, and the format of the query results you want to display. If you are using text boxes to allow users to type in from and to dates, you will need to do some data validation as part of your code. Another way to do this is to create the query using the query designer, and then add this line to the date field criteria:

Between #[Please enter a start date:]# And #[Please enter an end date:]#

Then use the button wizard to create the button to launch the query, and the query will prompt the user for these parameters, and then display the results in a datasheet.

If you were using a form or report to launch the query results, you can create the same sort of query, but in the Criteria line for the date field you can do this:

Between #[Forms]![frmMyForm].[txtMyStartDateTextBox]# And #[Forms]![frmMyForm].[txtMyEndDateTextBox]#

The form or report will launch, and the query will look for the parameters for you.

Another way to do this, and my preferred method since it uses data vaidation, is:

Dim dtStart As Date
Dim dtEnd As Date
Dim sLink As String
Dim sDoc As String

If IsNull(Me.txtStartDate) Or Me.txtStartDate = "" Then
   MsgBox "Please enter a start date", vbCritical
   Exit Sub
   dtStart = Me.txtStartDate
End If
If IsNull(Me.txtEndDate) Or Me.txtEndDate = "" Then
   MsgBox "Please enter an end date", vbCritical
   Exit Sub
   dtEnd = Me.txtEndDate
End If

sLink = "[DateField] Between #" & dtStart & "# And #" & dtEnd & "#"

sDoc = "frmMyFormName"

DoCmd.OpenForm sDoc, , , sLink

You would need to add some validation to make sure the values are dates, otherwise if they are not, errors will be thrown.

I use combo boxes and have them look up distinct entries in the date field you will be using to make sure users can only select valid dates. I also use cascading combo boxes so that when the user selects a start date, they can then only choose end dates that are after the start date, otherwise you need to validate that as well, which I left out above.

Did that help?


Look it up at: http://wrox.books24x7.com

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ejb WeblogicBuilder information Required satyaprakash22 EJB 0 December 10th, 2007 02:28 PM
Exception Absolute Path Information required roydaniel .NET Framework 2.0 1 June 19th, 2006 04:38 PM
Urgent Information Required For reporting subhan BOOK: Professional Crystal Reports for VS.NET 1 October 8th, 2005 12:05 PM
Inserting Information From Form into another Table smartgir Access VBA 6 October 22nd, 2004 12:35 AM
updating table information with a form? lanctotd Access 1 June 8th, 2004 12:46 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.