Parameter Query not working

[Lavenderchan]

Hi I am trying to get a report to use a wild card paramter query for a report .
but it's not returning any values. I have it working in access 2003 but not in 2007. Is there a reason this won't not work?

Keri

 

[dhookom]

IMO, parameter queries should not be part of any application faq701-6763. Consider useing controls on forms for all user interaction. Could you share your SQL view of your query?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Hi dhookom,

here is they copy of my sql view. thanks,
SELECT [Open Tasks].ID, [Open Tasks].[Reserve for Replacement ID], [Open Tasks].[Excess Income ID], [Open Tasks].[Property Name], [Open Tasks].[REMS Number], [Open Tasks].[FHA Number], [Open Tasks].[Contract Number], [Open Tasks].[Project Manager], [Open Tasks].Task, [Open Tasks].[Open Date], [Open Tasks].[Open By], [Open Tasks].[Assigned To], [Open Tasks].Status, [Open Tasks].[% Complete], [Open Tasks].[Due Date], [Open Tasks].[Time Frame], [Open Tasks].[Closed Date], [Open Tasks].Comments, [Open Tasks].Archive
FROM [Open Tasks]
WHERE ((([Open Tasks].[Assigned To]) Like "*" & [Which Project Manager] & "*") AND (([Open Tasks].Status)="active"));

 

[dhookom]

Have you tried running the query by itself?

Do you get prompted?

If you remove the Assigned To criteria, does it return any records?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

I run the query by itself and I get return on the records but when it prompts and I type a name it I know is in the records I get nothing.

 

[dhookom]

Is [Assigned To] a lookup field? If so, it might be storing a numeric code but displaying a text value.

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

No Assigned To is not a look up field. the value is set at text value. Howevery there is a macro set on the report to pull up the form on which to edit if needed. I've taken it out and didn't change anything.

thanks,

 

[dhookom]

When you get prompted for [Which Project Manager] what happens if you type in a single letter that is found in many [Assigned To] fields?

What would you use an edit form for when opening a report?

Is [Open tasks] a table or query?

What do you see if you enter this into the SQL?

SELECT [Open Tasks].ID, [Open Tasks].[Reserve for Replacement ID], 
[Open Tasks].[Excess Income ID], [Open Tasks].[Property Name], 
[Open Tasks].[REMS Number], [Open Tasks].[FHA Number], 
[Open Tasks].[Contract Number], [Open Tasks].[Project Manager], 
[Open Tasks].Task, [Open Tasks].[Open Date], 
[Open Tasks].[Open By], [Open Tasks].[Assigned To], 
[Open Tasks].Status, [Open Tasks].[% Complete], 
[Open Tasks].[Due Date], [Open Tasks].[Time Frame], 
[Open Tasks].[Closed Date], [Open Tasks].Comments, 
[Open Tasks].Archive
FROM [Open Tasks]
WHERE [Open Tasks].Status="active";

Is the [Assigned To] value edited using a combo box?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

When you get prompted for [Which Project Manager] what happens if you type in a single letter that is found in many [Assigned To] fields?
It returns nothing.
What would you use an edit form for when opening a report?
Was already added to the report. i'm using a template from mircosoft. I already made the changes to it. I'm just setting up the reports.

Is [Open tasks] a table or query?
Query

What do you see if you enter this into the SQL?
returns all active records.

Is the [Assigned To] value edited using a combo box?
yes assign to is set as a combo box. I'm using an after update event to auto fill this on the form for this field.

thanks,
keri

 

[dhookom]

Tell us more about

Is the [Assigned To] value edited using a combo box?
yes assign to is set as a combo box. I'm using an after update event to auto fill this on the form for this field.

What is the data type of the field that stores [Open tasks]? Every symptom you have suggests this field actually stores a code value associated with a record in another table.

What is the Row Source property of the combo box?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Here is the sql for the combo box:
SELECT [Multifamily Staff].[Multifamily Staff]
FROM [Multifamily Staff]
WHERE ((([Multifamily Staff].[Field Office])="Columbus" Or ([Multifamily Staff].[Field Office])="cincinnati"));

The Row Source Type is Table/Query.

thanks,

 

[dhookom]

You didn't tell us anything about the After Update.

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

I'm sorry the afterupdate is
Private Sub cboProperty_Name_AfterUpdate()
Me.cboREMS_Number = Me.cboProperty_Name.Column(1)
Me.cboFHA_Number = Me.cboProperty_Name.Column(2)
Me.Contract_Number = Me.cboProperty_Name.Column(3)
Me.Project_Manager = Me.cboProperty_Name.Column(4)
End Sub

Thanks,

Keri

 

[dhookom]

So, where is [Assigned To] used? Is it bound to some control?

Your entire setup escapes my understanding. It seems that little by little it gets revealed to us.

Duane
Hook'D on Access
MS Access MVP