Solved filter adds Lookup_field. to field (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
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.
Code:
(((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’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
I use the Replace() function to get rid of the Lookup_ parts.
 

ClaraBarton

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
Oh. Where?
Code:
DoCmd.OpenReport _
    ReportName:=strReport, _
    View:=acViewPreview, _
    WhereCondition:=Forms![splField].Filter
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
Oh. Where?
Code:
DoCmd.OpenReport _
    ReportName:=strReport, _
    View:=acViewPreview, _
    WhereCondition:=Forms![splField].Filter
For instance:
Code:
WhereCondition:=Replace(Forms!splField.Filter,"Lookup_Size.","")
Hope that helps...
 

ClaraBarton

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
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

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
Oh no no no. I read all your advice. I would not do that. They are lookups at the form level only.;)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
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:

Code:
Replace(Replace(Replace(Forms!FormName.Filter,"Lookup_Field1.",""),"Lookup_Field2.",""),"Lookup_Field3.","")
Hope that helps...
 

ClaraBarton

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
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

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
Here is my new code:
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:
Code:
([Lookup_Code].[Code]="ah")
 

ClaraBarton

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
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

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
Here is my new code:
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:
Code:
([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:
Code:
Replace(Forms!FormName.Filter,"[Lookup_Code].","")
Hope that helps...
 

ClaraBarton

Registered User.
Local time
Today, 16:02
Joined
Oct 14, 2019
Messages
427
May I just say thank you thank you thank you. I would NEVER have figured this out.!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 19:02
Joined
Aug 29, 2022
Messages
12
I am not sure what the period is for in "Replace(Forms!FormName.Filter,"[Lookup_Code].","")"
 

Access or E

New member
Local time
Today, 19:02
Joined
Aug 29, 2022
Messages
12
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’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
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!
 

Users who are viewing this thread

Top Bottom