Query calling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that I want to call from a form. I want to have the user
select the LastName of a person and filter the results of the report for that
person. However if LastName is blank, I want the total report. I currently
have the following code:

stDocName = "My Report"
DoCmd.OpenQuery "stDocName", acViewNormal, acEdit

and the query is set up to use [myForm]![LastName] as a criteria.

When I select a last name, the form works as expected, however it returns no
records when I have no last name selected. I understand this is happenning
because there aren't any last names that match "". Is there an easy way to
configure my query or report to reproduce the desired results?

Thank you
 
I have a report that I want to call from a form. I want to have the user
select the LastName of a person and filter the results of the report for that
person. However if LastName is blank, I want the total report. I currently
have the following code:

stDocName = "My Report"
DoCmd.OpenQuery "stDocName", acViewNormal, acEdit

and the query is set up to use [myForm]![LastName] as a criteria.

When I select a last name, the form works as expected, however it returns no
records when I have no last name selected. I understand this is happenning
because there aren't any last names that match "". Is there an easy way to
configure my query or report to reproduce the desired results?

Thank you
Click to expand...

I don't understand!
You want to open a report, but you are using OpenQuery.

1) If the query is the record source of the report, then use:

stDocName = "My Report"
DoCmd.OpenQuery stDocName, acViewPreview
to open the report in Preview (acViewNormal to print without preview.

Note that stDocName is NOT in quotes.

2) Change the query criteria to:
Like IIf IsNull(forms!myForm!LastName),"*",forms!myForm!LastName)

3) Using [LastName] as criteria is not a good idea. It is not uncommon
to have lists with more that one person having the same last name.
Using your criteria, Mary Smith, Joe Smith, Frank Smith, etc., will
all be returned.
Use a [NameID] field instead.
If you use a Combo box to select the correct person wanted, you can
search for LastName] and [FirstName] but the ID field is used to
select the correct record.

If you still need help post back.
 
Keith Meier said:
I have a report that I want to call from a form. I want to have the
user select the LastName of a person and filter the results of the
report for that person. However if LastName is blank, I want the
total report. I currently have the following code:

stDocName = "My Report"
DoCmd.OpenQuery "stDocName", acViewNormal, acEdit

and the query is set up to use [myForm]![LastName] as a criteria.

When I select a last name, the form works as expected, however it
returns no records when I have no last name selected. I understand
this is happenning because there aren't any last names that match "".
Is there an easy way to configure my query or report to reproduce the
desired results?

Thank you
Click to expand...

Change the query's WHERE clause from

... WHERE LastName = [Forms]![MyForm]![LastName]

to

... WHERE LastName = [Forms]![MyForm]![LastName]
OR [Forms]![MyForm]![LastName] Is Null
 
Back
Top