Form and Query used for report

K

Kat5813

I have created a form in order to select distinct parameters to run a report
off of. The report is based on a query that uses the result of the form
selections. For example: in the last name field in the query, I have
[Forms]![Reporting Form]![lname] or [Forms]![Reporting Form]![lname] is null.
It is set up for all of the selections in the form. Problem is though that
everytime I run the report, the query changes and takes the is null out and
adds another column to place the is null in. It does this for every field.
Needless to say, after running the report numerous times, the query cannot
now be opened in designed view because it is taking up too much memory.

So......help! There are fourteen fields in the main table of this database.
The user I am building it for wants to be able to run a report on any of
these fields. So, I thought the easiest way would be to build a form with
all fourteen fields and allow the user to select parameters on any or all of
them (hence the is null in the query). I then wanted to take this form and
query and use them for several reports that have the data grouped and
subtotaled in different ways. I thought this approach might be easier than
doing a seperate form for each parameter especially since the user wants to
be able to select parameters on multiple fields.

Is there any way to accomplish this?

Thanks for any help!!!
 
K

Kat5813

I think Allen's example is over my head as I am fairly new to access. Does
anyone else have any suggestions?

Beetle said:
You might have a look at Allen Browne's search form at this link;

http://allenbrowne.com/ser-62.html

His example displays the results on a form, but you could also use the
resulting recordset as the basis for a report.
--
_________

Sean Bailey


Kat5813 said:
I have created a form in order to select distinct parameters to run a report
off of. The report is based on a query that uses the result of the form
selections. For example: in the last name field in the query, I have
[Forms]![Reporting Form]![lname] or [Forms]![Reporting Form]![lname] is null.
It is set up for all of the selections in the form. Problem is though that
everytime I run the report, the query changes and takes the is null out and
adds another column to place the is null in. It does this for every field.
Needless to say, after running the report numerous times, the query cannot
now be opened in designed view because it is taking up too much memory.

So......help! There are fourteen fields in the main table of this database.
The user I am building it for wants to be able to run a report on any of
these fields. So, I thought the easiest way would be to build a form with
all fourteen fields and allow the user to select parameters on any or all of
them (hence the is null in the query). I then wanted to take this form and
query and use them for several reports that have the data grouped and
subtotaled in different ways. I thought this approach might be easier than
doing a seperate form for each parameter especially since the user wants to
be able to select parameters on multiple fields.

Is there any way to accomplish this?

Thanks for any help!!!
Click to expand...
Click to expand...
 
K

Kat5813

Here is the SQL for my query if this helps anyone:

SELECT [Patient Information].[Date of Service], [Patient Information].[Last
Name], [Patient Information].[First Name], [Patient Information].[Date of
Birth], [Patient Information].Sex, [Patient Information].City, [Patient
Information].State, [Patient Information].County, [Patient
Information].[Diagnosis Site], [Patient Information].Physician, [Patient
Information].Department, [Patient Information].Supplement, [Patient
Information].Cost
FROM [Patient Information]
WHERE ((([Patient Information].[Date of Service]) Is Not Null And ([Patient
Information].[Date of Service]) Between [Forms]![Reporting Form]![BegDate]
And [Forms]![Reporting Form]![EndDate]) AND (([Patient Information].[Last
Name])=[Forms]![Reporting Form]![lname]) AND (([Patient Information].[First
Name])=[Forms]![Reporting Form]![fname]) AND (([Patient Information].[Date of
Birth])=[Forms]![Reporting Form]![dob] Or [Forms]![Reporting Form]![dob] Is
Null) AND (([Patient Information].Sex)=[Forms]![Reporting Form]![sex] Or
[Forms]![Reporting Form]![sex] Is Null) AND (([Patient
Information].City)=[Forms]![Reporting Form]![city] Or [Forms]![Reporting
Form]![city] Is Null) AND (([Patient Information].State)=[Forms]![Reporting
Form]![state] Or [Forms]![Reporting Form]![state] Is Null) AND (([Patient
Information].County)=[Forms]![Reporting Form]![county] Or [Forms]![Reporting
Form]![county] Is Null) AND (([Patient Information].[Diagnosis
Site])=[Forms]![Reporting Form]![diagnosis] Or [Forms]![Reporting
Form]![diagnosis] Is Null) AND (([Patient
Information].Physician)=[Forms]![Reporting Form]![physician] Or
[Forms]![Reporting Form]![physician] Is Null) AND (([Patient
Information].Department)=[Forms]![Reporting Form]![department] Or
[Forms]![Reporting Form]![department] Is Null) AND (([Patient
Information].Supplement)=[Forms]![Reporting Form]![supplement] Or
[Forms]![Reporting Form]![supplement] Is Null) AND (([Patient
Information].Cost)=[Forms]![Reporting Form]![cost] Or [Forms]![Reporting
Form]![cost] Is Null));


Kat5813 said:
I have created a form in order to select distinct parameters to run a report
off of. The report is based on a query that uses the result of the form
selections. For example: in the last name field in the query, I have
[Forms]![Reporting Form]![lname] or [Forms]![Reporting Form]![lname] is null.
It is set up for all of the selections in the form. Problem is though that
everytime I run the report, the query changes and takes the is null out and
adds another column to place the is null in. It does this for every field.
Needless to say, after running the report numerous times, the query cannot
now be opened in designed view because it is taking up too much memory.

So......help! There are fourteen fields in the main table of this database.
The user I am building it for wants to be able to run a report on any of
these fields. So, I thought the easiest way would be to build a form with
all fourteen fields and allow the user to select parameters on any or all of
them (hence the is null in the query). I then wanted to take this form and
query and use them for several reports that have the data grouped and
subtotaled in different ways. I thought this approach might be easier than
doing a seperate form for each parameter especially since the user wants to
be able to select parameters on multiple fields.

Is there any way to accomplish this?

Thanks for any help!!!
Click to expand...
 
B

brlubman

Here is the SQL for my query if this helps anyone:

SELECT [Patient Information].[Date of Service], [Patient Information].[Last
Name], [Patient Information].[First Name], [Patient Information].[Date of
Birth], [Patient Information].Sex, [Patient Information].City, [Patient
Information].State, [Patient Information].County, [Patient
Information].[Diagnosis Site], [Patient Information].Physician, [Patient
Information].Department, [Patient Information].Supplement, [Patient
Information].Cost
FROM [Patient Information]
WHERE ((([Patient Information].[Date of Service]) Is Not Null And ([Patient
Information].[Date of Service]) Between [Forms]![Reporting Form]![BegDate]
And [Forms]![Reporting Form]![EndDate]) AND (([Patient Information].[Last
Name])=[Forms]![Reporting Form]![lname]) AND (([Patient Information].[First
Name])=[Forms]![Reporting Form]![fname]) AND (([Patient Information].[Date of
Birth])=[Forms]![Reporting Form]![dob] Or [Forms]![Reporting Form]![dob] Is
Null) AND (([Patient Information].Sex)=[Forms]![Reporting Form]![sex] Or
[Forms]![Reporting Form]![sex] Is Null) AND (([Patient
Information].City)=[Forms]![Reporting Form]![city] Or [Forms]![Reporting
Form]![city] Is Null) AND (([Patient Information].State)=[Forms]![Reporting
Form]![state] Or [Forms]![Reporting Form]![state] Is Null) AND (([Patient
Information].County)=[Forms]![Reporting Form]![county] Or [Forms]![Reporting
Form]![county] Is Null) AND (([Patient Information].[Diagnosis
Site])=[Forms]![Reporting Form]![diagnosis] Or [Forms]![Reporting
Form]![diagnosis] Is Null) AND (([Patient
Information].Physician)=[Forms]![Reporting Form]![physician] Or
[Forms]![Reporting Form]![physician] Is Null) AND (([Patient
Information].Department)=[Forms]![Reporting Form]![department] Or
[Forms]![Reporting Form]![department] Is Null) AND (([Patient
Information].Supplement)=[Forms]![Reporting Form]![supplement] Or
[Forms]![Reporting Form]![supplement] Is Null) AND (([Patient
Information].Cost)=[Forms]![Reporting Form]![cost] Or [Forms]![Reporting
Form]![cost] Is Null));


Kat5813 said:
I have created a form in order to select distinct parameters to run a report
off of. The report is based on a query that uses the result of the form
selections. For example: in the last name field in the query, I have
[Forms]![Reporting Form]![lname] or [Forms]![Reporting Form]![lname] is null.
It is set up for all of the selections in the form. Problem is though that
everytime I run the report, the query changes and takes the is null out and
adds another column to place the is null in. It does this for every field.
Needless to say, after running the report numerous times, the query cannot
now be opened in designed view because it is taking up too much memory.

So......help! There are fourteen fields in the main table of this database.
The user I am building it for wants to be able to run a report on any of
these fields. So, I thought the easiest way would be to build a form with
all fourteen fields and allow the user to select parameters on any or all of
them (hence the is null in the query). I then wanted to take this form and
query and use them for several reports that have the data grouped and
subtotaled in different ways. I thought this approach might be easier than
doing a seperate form for each parameter especially since the user wants to
be able to select parameters on multiple fields.

Is there any way to accomplish this?

Thanks for any help!!!
Click to expand...
Click to expand...
 
K

Kat5813

I was finally able to figure this out.

[email protected] said:
Here is the SQL for my query if this helps anyone:

SELECT [Patient Information].[Date of Service], [Patient Information].[Last
Name], [Patient Information].[First Name], [Patient Information].[Date of
Birth], [Patient Information].Sex, [Patient Information].City, [Patient
Information].State, [Patient Information].County, [Patient
Information].[Diagnosis Site], [Patient Information].Physician, [Patient
Information].Department, [Patient Information].Supplement, [Patient
Information].Cost
FROM [Patient Information]
WHERE ((([Patient Information].[Date of Service]) Is Not Null And ([Patient
Information].[Date of Service]) Between [Forms]![Reporting Form]![BegDate]
And [Forms]![Reporting Form]![EndDate]) AND (([Patient Information].[Last
Name])=[Forms]![Reporting Form]![lname]) AND (([Patient Information].[First
Name])=[Forms]![Reporting Form]![fname]) AND (([Patient Information].[Date of
Birth])=[Forms]![Reporting Form]![dob] Or [Forms]![Reporting Form]![dob] Is
Null) AND (([Patient Information].Sex)=[Forms]![Reporting Form]![sex] Or
[Forms]![Reporting Form]![sex] Is Null) AND (([Patient
Information].City)=[Forms]![Reporting Form]![city] Or [Forms]![Reporting
Form]![city] Is Null) AND (([Patient Information].State)=[Forms]![Reporting
Form]![state] Or [Forms]![Reporting Form]![state] Is Null) AND (([Patient
Information].County)=[Forms]![Reporting Form]![county] Or [Forms]![Reporting
Form]![county] Is Null) AND (([Patient Information].[Diagnosis
Site])=[Forms]![Reporting Form]![diagnosis] Or [Forms]![Reporting
Form]![diagnosis] Is Null) AND (([Patient
Information].Physician)=[Forms]![Reporting Form]![physician] Or
[Forms]![Reporting Form]![physician] Is Null) AND (([Patient
Information].Department)=[Forms]![Reporting Form]![department] Or
[Forms]![Reporting Form]![department] Is Null) AND (([Patient
Information].Supplement)=[Forms]![Reporting Form]![supplement] Or
[Forms]![Reporting Form]![supplement] Is Null) AND (([Patient
Information].Cost)=[Forms]![Reporting Form]![cost] Or [Forms]![Reporting
Form]![cost] Is Null));


Kat5813 said:
I have created a form in order to select distinct parameters to run a report
off of. The report is based on a query that uses the result of the form
selections. For example: in the last name field in the query, I have
[Forms]![Reporting Form]![lname] or [Forms]![Reporting Form]![lname] is null.
It is set up for all of the selections in the form. Problem is though that
everytime I run the report, the query changes and takes the is null out and
adds another column to place the is null in. It does this for every field.
Needless to say, after running the report numerous times, the query cannot
now be opened in designed view because it is taking up too much memory.

So......help! There are fourteen fields in the main table of this database.
The user I am building it for wants to be able to run a report on any of
these fields. So, I thought the easiest way would be to build a form with
all fourteen fields and allow the user to select parameters on any or all of
them (hence the is null in the query). I then wanted to take this form and
query and use them for several reports that have the data grouped and
subtotaled in different ways. I thought this approach might be easier than
doing a seperate form for each parameter especially since the user wants to
be able to select parameters on multiple fields.

Is there any way to accomplish this?

Thanks for any help!!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date parameters for report 2
Access Access Report Works on One Machine but not Another 3
Creating a query form. 2
Form Input, Parameters, Report Based on Query 2
Problem with Report and Forms 2
Access Determine what query to run based on a checkbox 0
Question reg Form and Report Parameters 11
Copy record info into new form 0

Top