Linking Dialog box to query

[Megan]

I have a functioning SQL code in my query, now all i need is for my users to
be able to put in their criteria on my already created dialog box, click the
OK command button and recieve the filtered forms, how do I relate the two?

 

[Tom Wickerath]

Hi Megan,

If you send me an e-mail message, with a valid reply address, I will forward
a Word document written by Michael Hernandez that covers this topic.


If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

QWaos168@XScom cast. Dnet (<--Remove all capitolized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
_______________________________

:

I have a functioning SQL code in my query, now all i need is for my users to
be able to put in their criteria on my already created dialog box, click the
OK command button and recieve the filtered forms, how do I relate the two?

 

[KARL DEWEY]

Use this for your criteria in the query --
[Forms]![YourFormName]![YourDialogBox]

 

[Ken Sheridan]

To take this on a little further you might want controls on your dialog form
to be optional, i.e. the user can enter none, some or all of them. To cater
for this test for NULL. Say you have controls [txtStartDate] and
[txtEndDate] on your form for defining a date range. If neither are entered
all rows are returned, if the first is entered only rows with dates on or
after that date are returned, if both are returned then rows within the date
range are returned, if only the second is entered then only rows on or before
that date are returned. To do this your query would go something like this:

PARAMETERS
[Forms]![MyDialogForm]![txtStartDate] DateTime,
[Forms]![MyDialogForm]![txtEndDate] DateTime;
SELECT *
FROM MyTable
WHERE
(MyDate >= [Forms]![MyDialogForm]![txtStartDate]
OR Forms![Forms]![MyDialogForm]![txtStartDate] IS NULL)
AND
(MyDate < [Forms]![MyDialogForm]![txtEndDate] + 1
OR Forms![Forms]![MyDialogForm]![txtEndDate] IS NULL);

Note that the parameters are declared in this query, always a good idea, but
particularly so with dates as these can be interpreted ambiguously depending
on the format employed when entering the dates in the dialog form. Note also
that the two OR operations are each enclosed in parentheses to force them to
evaluate independently of each other. Note too that for the end date the
query looks for rows before the day following the end date. This makes sure
that any rows with a date on the end date which include a non-zero
time-of-day element in the date/time value (the time-of-day could be present
but unseen due to the format used) are returned; these would be missed if <=
the end date, or BETWEEN the start date AND the end date were used.

 

[Megan]

I do not have any dates as search criteria, how would the code look if I want
the users to be able to enter as much or as little search criteria as they
would like, using a combination of drop-down lists and matching text

To take this on a little further you might want controls on your dialog form
to be optional, i.e. the user can enter none, some or all of them. To cater
for this test for NULL. Say you have controls [txtStartDate] and
[txtEndDate] on your form for defining a date range. If neither are entered
all rows are returned, if the first is entered only rows with dates on or
after that date are returned, if both are returned then rows within the date
range are returned, if only the second is entered then only rows on or before
that date are returned. To do this your query would go something like this:

PARAMETERS
[Forms]![MyDialogForm]![txtStartDate] DateTime,
[Forms]![MyDialogForm]![txtEndDate] DateTime;
SELECT *
FROM MyTable
WHERE
(MyDate >= [Forms]![MyDialogForm]![txtStartDate]
OR Forms![Forms]![MyDialogForm]![txtStartDate] IS NULL)
AND
(MyDate < [Forms]![MyDialogForm]![txtEndDate] + 1
OR Forms![Forms]![MyDialogForm]![txtEndDate] IS NULL);

Note that the parameters are declared in this query, always a good idea, but
particularly so with dates as these can be interpreted ambiguously depending
on the format employed when entering the dates in the dialog form. Note also
that the two OR operations are each enclosed in parentheses to force them to
evaluate independently of each other. Note too that for the end date the
query looks for rows before the day following the end date. This makes sure
that any rows with a date on the end date which include a non-zero
time-of-day element in the date/time value (the time-of-day could be present
but unseen due to the format used) are returned; these would be missed if <=
the end date, or BETWEEN the start date AND the end date were used.

I have a functioning SQL code in my query, now all i need is for my users to
be able to put in their criteria on my already created dialog box, click the
OK command button and recieve the filtered forms, how do I relate the two?

 

[Megan]

Here is my SQL:
SELECT Facilities.[Facility Name], Facilities.[Nearest Major US City],
Facilities.[Nearest Major City], Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions],
Facilities.[In-Home/Ethnographies Recruiting], Facilities.[National
Recruiting Database]
FROM Facilities
WHERE (((Facilities.[Facility Name])=[Forms]![Facilities]![Lookup])) OR
(((Facilities.[Nearest Major US City])=[Forms]![Lookup]![Mjr US cty])) OR
(((Facilities.[Nearest Major City])=[Forms]![Lookup]![Mjr frgn cty])) OR
(((Facilities.[In-Field Recruiting])=[Forms]![Lookup]![infield])) OR
(((Facilities.[Recruiting for Off-Site
Sessions])=[Forms]![Lookup]![offsite])) OR
(((Facilities.[In-Home/Ethnographies Recruiting])=[Forms]![Lookup]![inhome]))
OR (((Facilities.[National Recruiting Database])=[Forms]![Lookup]![nat dtb]));

To take this on a little further you might want controls on your dialog form
to be optional, i.e. the user can enter none, some or all of them. To cater
for this test for NULL. Say you have controls [txtStartDate] and
[txtEndDate] on your form for defining a date range. If neither are entered
all rows are returned, if the first is entered only rows with dates on or
after that date are returned, if both are returned then rows within the date
range are returned, if only the second is entered then only rows on or before
that date are returned. To do this your query would go something like this:

PARAMETERS
[Forms]![MyDialogForm]![txtStartDate] DateTime,
[Forms]![MyDialogForm]![txtEndDate] DateTime;
SELECT *
FROM MyTable
WHERE
(MyDate >= [Forms]![MyDialogForm]![txtStartDate]
OR Forms![Forms]![MyDialogForm]![txtStartDate] IS NULL)
AND
(MyDate < [Forms]![MyDialogForm]![txtEndDate] + 1
OR Forms![Forms]![MyDialogForm]![txtEndDate] IS NULL);

Note that the parameters are declared in this query, always a good idea, but
particularly so with dates as these can be interpreted ambiguously depending
on the format employed when entering the dates in the dialog form. Note also
that the two OR operations are each enclosed in parentheses to force them to
evaluate independently of each other. Note too that for the end date the
query looks for rows before the day following the end date. This makes sure
that any rows with a date on the end date which include a non-zero
time-of-day element in the date/time value (the time-of-day could be present
but unseen due to the format used) are returned; these would be missed if <=
the end date, or BETWEEN the start date AND the end date were used.

I have a functioning SQL code in my query, now all i need is for my users to
be able to put in their criteria on my already created dialog box, click the
OK command button and recieve the filtered forms, how do I relate the two?

 

[KARL DEWEY]

Here is for a partial input of the criteria --
Like [Enter your criteria] & "*"

The above must have the start exact. Below is for any part --
Like "*" & [Enter your criteria] & "*"

I do not have any dates as search criteria, how would the code look if I want
the users to be able to enter as much or as little search criteria as they
would like, using a combination of drop-down lists and matching text

To take this on a little further you might want controls on your dialog form
to be optional, i.e. the user can enter none, some or all of them. To cater
for this test for NULL. Say you have controls [txtStartDate] and
[txtEndDate] on your form for defining a date range. If neither are entered
all rows are returned, if the first is entered only rows with dates on or
after that date are returned, if both are returned then rows within the date
range are returned, if only the second is entered then only rows on or before
that date are returned. To do this your query would go something like this:

PARAMETERS
[Forms]![MyDialogForm]![txtStartDate] DateTime,
[Forms]![MyDialogForm]![txtEndDate] DateTime;
SELECT *
FROM MyTable
WHERE
(MyDate >= [Forms]![MyDialogForm]![txtStartDate]
OR Forms![Forms]![MyDialogForm]![txtStartDate] IS NULL)
AND
(MyDate < [Forms]![MyDialogForm]![txtEndDate] + 1
OR Forms![Forms]![MyDialogForm]![txtEndDate] IS NULL);

Note that the parameters are declared in this query, always a good idea, but
particularly so with dates as these can be interpreted ambiguously depending
on the format employed when entering the dates in the dialog form. Note also
that the two OR operations are each enclosed in parentheses to force them to
evaluate independently of each other. Note too that for the end date the
query looks for rows before the day following the end date. This makes sure
that any rows with a date on the end date which include a non-zero
time-of-day element in the date/time value (the time-of-day could be present
but unseen due to the format used) are returned; these would be missed if <=
the end date, or BETWEEN the start date AND the end date were used.

I have a functioning SQL code in my query, now all i need is for my users to
be able to put in their criteria on my already created dialog box, click the
OK command button and recieve the filtered forms, how do I relate the two?

 

[Ken Sheridan]

At present your query uses Boolean OR operations. This will mean that any
row in the table which matches any one of the criteria will be returned, e.g.
if the user selects Facility A as Facility Name and San Francisco as Nearest
Major City then all rows where the facility is Facility A will be returned
regardless of the city in those rows along with all rows where the city is
San Francisco regardless of the facility name in those rows. Is this what
you want? If so your query as it stands should do this.

The alternative possibility would be that you want to use Boolean AND
operations, i.e. if the user selects two values in the form, Major City and
In-Field Recruiting say then only rows where those fields contain both
selected values will be returned. Its this scenario which is analogous to my
example (the fact that it used dates is immaterial, the principle is the
same). To do this you'd use Boolean ANDs and test for the parameters being
NULL in each case, which makes the controls optional, i.e. a user can
select/enter values in as few or as many controls on the form as desired.
The query would thus go like this:

SELECT Facilities.[Facility Name], Facilities.[Nearest Major US City],
Facilities.[Nearest Major City], Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions],
Facilities.[In-Home/Ethnographies Recruiting], Facilities.[National
Recruiting Database]
FROM Facilities
WHERE
(Facilities.[Facility Name]=[Forms]![Facilities]![Lookup]
OR [Forms]![Facilities]![Lookup] IS NULL)
AND
(Facilities.[Nearest Major US City]=[Forms]![Lookup]![Mjr US cty]
OR [Forms]![Lookup]![Mjr US cty] IS NULL)
AND
(Facilities.[Nearest Major City]=[Forms]![Lookup]![Mjr frgn cty]
OR [Forms]![Lookup]![Mjr frgn cty] IS NULL)
AND
(Facilities.[In-Field Recruiting]=[Forms]![Lookup]![infield]
OR [Forms]![Lookup]![infield] IS NULL)
AND
(Facilities.[Recruiting for Off-Site Sessions]=[Forms]![Lookup]![offsite]
OR [Forms]![Lookup]![offsite] IS NULL)
AND
(Facilities.[In-Home/Ethnographies Recruiting]=[Forms]![Lookup]![inhome]
OR [Forms]![Lookup]![inhome] IS NULL)
AND
(Facilities.[National Recruiting Database]=[Forms]![Lookup]![nat dtb]
OR [Forms]![Lookup]![nat dtb] IS NULL);

I've split the criteria over separate lines to make it more readable; you
can do this anywhere in an SQL statement, it works just the same. Which of
these is correct in your case depends on just how you want the criteria
entered/selected in your dialogue form to be applied in the query.

Here is my SQL:
SELECT Facilities.[Facility Name], Facilities.[Nearest Major US City],
Facilities.[Nearest Major City], Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions],
Facilities.[In-Home/Ethnographies Recruiting], Facilities.[National
Recruiting Database]
FROM Facilities
WHERE (((Facilities.[Facility Name])=[Forms]![Facilities]![Lookup])) OR
(((Facilities.[Nearest Major US City])=[Forms]![Lookup]![Mjr US cty])) OR
(((Facilities.[Nearest Major City])=[Forms]![Lookup]![Mjr frgn cty])) OR
(((Facilities.[In-Field Recruiting])=[Forms]![Lookup]![infield])) OR
(((Facilities.[Recruiting for Off-Site
Sessions])=[Forms]![Lookup]![offsite])) OR
(((Facilities.[In-Home/Ethnographies Recruiting])=[Forms]![Lookup]![inhome]))
OR (((Facilities.[National Recruiting Database])=[Forms]![Lookup]![nat dtb]));