Expression referencing a form and using a like statement

[Guest]

I need to build an expression that references a data entry field on a report
form. The purpose is to generate a report that matches any company that is
typed in the field. That part is easy and it looks like "[Forms]![Main
Form].[text37]".

What I really need is to be able to have any company that is "Like" what is
typed in that field to be displayed in the report. This avoids having to
know exactly how the the company was entered into the table. Can I use the
like statement in the expression or do I have to generate a seperate list
table for the companies?

Thanks!

 

[Marshall Barton]

I need to build an expression that references a data entry field on a report
form. The purpose is to generate a report that matches any company that is
typed in the field. That part is easy and it looks like "[Forms]![Main
Form].[text37]".

What I really need is to be able to have any company that is "Like" what is
typed in that field to be displayed in the report. This avoids having to
know exactly how the the company was entered into the table. Can I use the
like statement in the expression or do I have to generate a seperate list
table for the companies?

You can use Like in the criteria:

Like [Forms]![Main Form].[text37] & "*"

 

[Guest]

Thank you, it worked! I really appreciate the help.

Best Regards

Darryl

I need to build an expression that references a data entry field on a report
form. The purpose is to generate a report that matches any company that is
typed in the field. That part is easy and it looks like "[Forms]![Main
Form].[text37]".

What I really need is to be able to have any company that is "Like" what is
typed in that field to be displayed in the report. This avoids having to
know exactly how the the company was entered into the table. Can I use the
like statement in the expression or do I have to generate a seperate list
table for the companies?

You can use Like in the criteria:

Like [Forms]![Main Form].[text37] & "*"

 

[fredg]

I need to build an expression that references a data entry field on a report
form. The purpose is to generate a report that matches any company that is
typed in the field. That part is easy and it looks like "[Forms]![Main
Form].[text37]".

What I really need is to be able to have any company that is "Like" what is
typed in that field to be displayed in the report. This avoids having to
know exactly how the the company was entered into the table. Can I use the
like statement in the expression or do I have to generate a seperate list
table for the companies?

Thanks!

1) You can use, in the query that is the report's record source:
Like [Forms]![Main Form].[text37] & "*"

However, this may lead to unwanted results if you have more than one
company with similar names, i.e. "Ford Motor Co." and "Ford and Sons,
Insurance". Typing "Ford" into to text control will return both
companies records.

2) A much better solution is to use a combo box on the form. Include
the CompanyID as well as the Company Name fields.
As Combo RowSource:
"Select CompanyID,CompanyName From tblCompanies Order By CompanyName;"
Hide the CompanyID by setting the Combo's column width property to
0";1"
Bind the Combo box to the CompanyID column (1).

Then as criteria in the query's CompanyID column:
forms![Main Form]!ComboName

Then, when you select the company name from the combo box, it is the
CompanyID that is actually used by the query to locate the correct
company. This avoids the possibility of any similar name entry and no
need to use "Like".

 

[Guest]

Thank you Fred. The company I work for deals with companies that have very
similar names. This may even make it more important that I use an combo box
option to allow the user to select companies from a tblcompanies list. Right
now the amount of data collected is small so there is not much of an issue.
Six months from now I could have a couple hundred companies listed and the
report would need to be further filtered to get just what is needed. Making
the change to a combo box after more data has been collected would also
require a lot of work to repopulate the company field for any names that were
entered slightly different by different individuals. Appreciate the feedback!

Best Regards

Darryl

I need to build an expression that references a data entry field on a report
form. The purpose is to generate a report that matches any company that is
typed in the field. That part is easy and it looks like "[Forms]![Main
Form].[text37]".

What I really need is to be able to have any company that is "Like" what is
typed in that field to be displayed in the report. This avoids having to
know exactly how the the company was entered into the table. Can I use the
like statement in the expression or do I have to generate a seperate list
table for the companies?

Thanks!

1) You can use, in the query that is the report's record source:
Like [Forms]![Main Form].[text37] & "*"

However, this may lead to unwanted results if you have more than one
company with similar names, i.e. "Ford Motor Co." and "Ford and Sons,
Insurance". Typing "Ford" into to text control will return both
companies records.

2) A much better solution is to use a combo box on the form. Include
the CompanyID as well as the Company Name fields.
As Combo RowSource:
"Select CompanyID,CompanyName From tblCompanies Order By CompanyName;"
Hide the CompanyID by setting the Combo's column width property to
0";1"
Bind the Combo box to the CompanyID column (1).

Then as criteria in the query's CompanyID column:
forms![Main Form]!ComboName

Then, when you select the company name from the combo box, it is the
CompanyID that is actually used by the query to locate the correct
company. This avoids the possibility of any similar name entry and no
need to use "Like".