By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

"Query by Search Form" Question...

P: n/a
Hi, folks -

Perhaps you could help me with a search form I am building. I have a
table with a field [AddDate] in it.

I want to return dates inside two parameters the user enters into a
search form: >[forms]![frmSearch].[StartDate] and
<[forms]![frmSearch].[EndDate]

BUT - I would like the user to be able to SKIP either field, so that a
null [StartDate] returns ALL dates less than [EndDate] and vice versa.

(FYI I'm a newbie stuck in "design view", easily stymied by VBA...)

Any ideas/suggestions?
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Create a query and put the AddDate field in two columns. Include whatever other
fields you need. Put the following expression in the criteria of the first
AddDate column:
[forms]![frmSearch].[StartDate] Or ([forms]![frmSearch].[StartDate] Is Null)
Put the following expression in the criteria of the second AddDate column:
<[forms]![frmSearch].[EndDate] Or ([forms]![frmSearch].[EndDate] Is Null)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"screenwriter776" <sc*************@hotmail.com> wrote in message
news:2f**************************@posting.google.c om... Hi, folks -

Perhaps you could help me with a search form I am building. I have a
table with a field [AddDate] in it.

I want to return dates inside two parameters the user enters into a
search form: >[forms]![frmSearch].[StartDate] and
<[forms]![frmSearch].[EndDate]

BUT - I would like the user to be able to SKIP either field, so that a
null [StartDate] returns ALL dates less than [EndDate] and vice versa.

(FYI I'm a newbie stuck in "design view", easily stymied by VBA...)

Any ideas/suggestions?

Nov 12 '05 #2

P: n/a
On 17 Feb 2004 17:10:48 -0800, screenwriter776 wrote:
Hi, folks -

Perhaps you could help me with a search form I am building. I have a
table with a field [AddDate] in it.

I want to return dates inside two parameters the user enters into a
search form: >[forms]![frmSearch].[StartDate] and
<[forms]![frmSearch].[EndDate]

BUT - I would like the user to be able to SKIP either field, so that a
null [StartDate] returns ALL dates less than [EndDate] and vice versa.

(FYI I'm a newbie stuck in "design view", easily stymied by VBA...)

Any ideas/suggestions?


If you are trying to do this with a Query object, then you'll need 4 of
them, if you are trying to do it with code, then you need to contruct the
different parts of the query using a few tricks...

If you are using the Query objects, then make one for searching on no
dates, one for just startdate, one for just end date and one for both
dates. You'll then need to use the IsDate() function to determine if each
date field is filled in, and run the appropriate query. If in code,
something like this (add your own error trapping and modify as necessary)

Function BuildSearchQuery(Optional StartDate As String = "", _
Optional EndDate As String = "") As String

BuildSearchQuery=""

Dim SelPart As String
Dim WherePart As String
Dim EndPart As String
Dim DtePart As String

SelPart = "SELECT fieldList FROM(Joins if needed)"

Select Case IsDate(StartDate)
Case True
DtePart = "(DateField = #" & StartDate & "#) AND "
Case Else
DtePart = ""
End Select

Select Case IsDate(StartDate)
Case True
DtePart = DtePart & "(DateField = #" & EndDate & "#)"
Case Else
DtePart = DtePart & ""
End Select

If Len(DtePart) > 0 Then
If Right(DtePart, 5) = " AND " Then
DtePart = Left(DtePart, Len(DtePart)-5)
End If
WherePart = " WHERE("
EndPart = ");"
Else
WherePart = ""
EndPart = ";"
End If

BuildSearchQuery = SelPart & WherePart & DtePart & EndPart

End Function
Mike Storr
www.veraccess.com
Nov 12 '05 #3

P: n/a
Here's some code that will build a SQL query string for you.

Function GetQuery() As String

dim strSQL as string
dim strSQL1 as string, strSQL2 as string

strSQL = "SELECT field, field, field ... FROM ..."
strSQL1 = ""
strSQL2 = ""

if not isnull(me!StartDate) then
strSQL1 = " AddDate > #" & me!StartDate & "# "

if not isnull(me!EndDate) then
strSQL1 = " AddDate < #" & me!EndDate & "# "

if len(strSQL1) > 0 and len(strSQL2) > 0 then
strSQL = strSQL & " Where " & strSQL1 & " And " & strSQL2
elseif len(strSQL1) > 0 then
strSQL = strSQL & " Where " & strSQL1
elseif len(strSQL2) > 0 then
strSQL = strSQL & " Where " & strSQL2
end if

GetQuery = strSQL

End Function

If you had a command button on your form you could respond to the OnClick
event with something like
currentdb.createquerydef("tmpQry", GetQuery()) in order to create a new
query with your start and end
date parameters. If neither StartDate nor EndDate was entered then the
query would return all records.

HTH -Linda
"screenwriter776" <sc*************@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Hi, folks -

Perhaps you could help me with a search form I am building. I have a
table with a field [AddDate] in it.

I want to return dates inside two parameters the user enters into a
search form: >[forms]![frmSearch].[StartDate] and
<[forms]![frmSearch].[EndDate]

BUT - I would like the user to be able to SKIP either field, so that a
null [StartDate] returns ALL dates less than [EndDate] and vice versa.

(FYI I'm a newbie stuck in "design view", easily stymied by VBA...)

Any ideas/suggestions?

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.