Solved filter adds Lookup_field. to field

[ClaraBarton]

I use the same query for the form and the report. There are several combobox fields.
When the form is filtered, the filter statement adds Lookup_field to field when the field is a combo and then the report doesn't recognize it and throws a parameter message.

(((qryField.PlantDate=#3/1/2015#))) And (Lookup_Size.Size="1.5""")

The parameter box then says: Enter parameter value? Lookup_Size.Size
Am I missing something obvious here?

 

[theDBguy]

I use the Replace() function to get rid of the Lookup_ parts.

 

[ClaraBarton]

Oh. Where?

DoCmd.OpenReport _
    ReportName:=strReport, _
    View:=acViewPreview, _
    WhereCondition:=Forms![splField].Filter

 

[theDBguy]

Oh. Where?

DoCmd.OpenReport _
    ReportName:=strReport, _
    View:=acViewPreview, _
    WhereCondition:=Forms![splField].Filter

For instance:

WhereCondition:=Replace(Forms!splField.Filter,"Lookup_Size.","")

Hope that helps...

 

[ClaraBarton]

aarrgh! I am so bad at this. I have several fields so...
Dim fld As Field
fld = Me.Fields(fld)
then... "Lookup_"fld",",""
nah...

 

[theDBguy]

aarrgh! I am so bad at this. I have several fields so...
Dim fld As Field
fld = Me.Fields(fld)
then... "Lookup_"fld",",""
nah...

Are you saying you have many "lookup" fields? If so, are they lookups at the table level? If so, that's been known to cause issues all over the place.

 

[ClaraBarton]

Oh no no no. I read all your advice. I would not do that. They are lookups at the form level only.

 

[theDBguy]

Oh no no no. I read all your advice. I would not do that. They are lookups at the form level only.

Okay, that's good news. Unfortunately, you'll just have to address all of them in your code. For example, you could nest them like this:

Replace(Replace(Replace(Forms!FormName.Filter,"Lookup_Field1.",""),"Lookup_Field2.",""),"Lookup_Field3.","")

Hope that helps...

 

[ClaraBarton]

Oh no no no. I read all your advice. I would not do that. They are lookups at the form level only.

It's a field inventory. The name and family of the tree. The size of the tree. The size of the bag it was planted in. The type of bag it was planted in. All are separate tables of information and all go into the inventory.

 

[ClaraBarton]

Here is my new code:

WhereCondition:=Replace(Replace(Replace(Replace(Me.Filter, "Lookup_Code.", ""), _
        "Lookup_Size.", ""), "Lookup_Bag.", ""), "Lookup_Container.", "")
    Debug.Print Me.Filter

And Here is what I get:
The parameter box says: Enter parameter value? Lookup_Code.Code
and debug returns:

([Lookup_Code].[Code]="ah")

 

[ClaraBarton]

And if that isn't insulting enough, if I remove all filters I still get the above error. It's recorded in the form level of the combo.

 

[theDBguy]

Here is my new code:

WhereCondition:=Replace(Replace(Replace(Replace(Me.Filter, "Lookup_Code.", ""), _
        "Lookup_Size.", ""), "Lookup_Bag.", ""), "Lookup_Container.", "")
    Debug.Print Me.Filter

And Here is what I get:
The parameter box says: Enter parameter value? Lookup_Code.Code
and debug returns:

([Lookup_Code].[Code]="ah")

Based on your original post, you didn't show us the square brackets, so I didn't include them either. But based on your last post, looks like you'll have to include them. For example:

Replace(Forms!FormName.Filter,"[Lookup_Code].","")

Hope that helps...

 

[ClaraBarton]

May I just say thank you thank you thank you. I would NEVER have figured this out.!

 

[theDBguy]

May I just say thank you thank you thank you. I would NEVER have figured this out.!

Hi Clara. You're welcome! Glad we could assist. Good luck with your project.

 

[Access or E]

I am not sure what the period is for in "Replace(Forms!FormName.Filter,"[Lookup_Code].","")"

 

[theDBguy]

I am not sure what the period is for in "Replace(Forms!FormName.Filter,"[Lookup_Code].","")"

Are you referring to the first period or the second one?

 

[Access or E]

I am referring to the period after [Lookup_Code]. Now I see it is a table name with the period preceding the field name. Thanks for your help, DBG.

 

[theDBguy]

I am referring to the period after [Lookup_Code]. Now I see it is a table name with the period preceding the field name. Thanks for your help, DBG.

Right, we're basically just trying to remove the table part and leave only the field name in. Cheers!