date / text criteria for query

[Guest]

I have a query and want to set up the criteria for filtering. I have one
field for date [EntryDate] and another field called [Company]. I would like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the correct
dates and works fine. Then I can remove the entrydate criteria and enter the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error that says
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain to many complicated elements.
Try simplifying the expression by assigning parts of the expression to
varaibles."

Please help me with this, I have searched for a day and half for an answer
and can not find one with type of problem. Thank you very much.

 

[Duane Hookom]

Are there any lookup fields involved in the table(s)?

 

[Guest]

Yes, I have 3 tables in this query tblCustomer, with TAXID as Primary Key,
tblPolicy, that has a primary key of it's own PolicyID, and looks up the
TAXID and finally tblPolicyDetail with own primary key, and looks up the
PolicyID.

Thanks for your reply

Are there any lookup fields involved in the table(s)?

--
Duane Hookom
MS Access MVP
--

I have a query and want to set up the criteria for filtering. I have one
field for date [EntryDate] and another field called [Company]. I would
like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the correct
dates and works fine. Then I can remove the entrydate criteria and enter
the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error that says
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain to many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
varaibles."

Please help me with this, I have searched for a day and half for an answer
and can not find one with type of problem. Thank you very much.

 

[Duane Hookom]

I meant lookup fields defined in the table design view.
I would make sure that the datatypes of your fields/columns in the query
correspond with the datatypes of your values in the controls on the form.

--
Duane Hookom
MS Access MVP
--

Yes, I have 3 tables in this query tblCustomer, with TAXID as Primary Key,
tblPolicy, that has a primary key of it's own PolicyID, and looks up the
TAXID and finally tblPolicyDetail with own primary key, and looks up the
PolicyID.

Thanks for your reply

Are there any lookup fields involved in the table(s)?

--
Duane Hookom
MS Access MVP
--

I have a query and want to set up the criteria for filtering. I have
one
field for date [EntryDate] and another field called [Company]. I would
like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the
correct
dates and works fine. Then I can remove the entrydate criteria and
enter
the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error that
says
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain to many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
varaibles."

Please help me with this, I have searched for a day and half for an
answer
and can not find one with type of problem. Thank you very much.

 

[Guest]

Yes they are defined within the table design view. No the Look up fields are
not within the query (i didnt know you could do that) I have matched my
format on all query fields to what is used in table design.

Still get error when combining the critieria

I meant lookup fields defined in the table design view.
I would make sure that the datatypes of your fields/columns in the query
correspond with the datatypes of your values in the controls on the form.

--
Duane Hookom
MS Access MVP
--

Yes, I have 3 tables in this query tblCustomer, with TAXID as Primary Key,
tblPolicy, that has a primary key of it's own PolicyID, and looks up the
TAXID and finally tblPolicyDetail with own primary key, and looks up the
PolicyID.

Thanks for your reply

Are there any lookup fields involved in the table(s)?

--
Duane Hookom
MS Access MVP
--

I have a query and want to set up the criteria for filtering. I have
one
field for date [EntryDate] and another field called [Company]. I would
like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the
correct
dates and works fine. Then I can remove the entrydate criteria and
enter
the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error that
says
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain to many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
varaibles."

Please help me with this, I have searched for a day and half for an
answer
and can not find one with type of problem. Thank you very much.

 

[Guest]

UPDATE - Here is my query code for you to look at, maybe that might help more:

SELECT tblPolicy.EntryDate, tblPolicy.BinderDate, tblCustomers.FirstName & "
" & [LastName] AS [Customer Name], tblPolicyDetail.Company,
tblPolicyDetail.Premium, tblPolicyDetail.CommissionRate,
tblPolicyDetail.Commission, tblPolicyDetail.AmountSubmitted
FROM (tblCustomers INNER JOIN tblPolicy ON tblCustomers.TaxID =
tblPolicy.TaxID) INNER JOIN tblPolicyDetail ON tblPolicy.PolicyID =
tblPolicyDetail.PolicyID
WHERE (((tblPolicy.EntryDate)=[Forms]![CompanyTrans]![EntryDate]) AND
((tblPolicyDetail.Company)=[Forms]![CompanyTrans]![Company]));

Yes they are defined within the table design view. No the Look up fields are
not within the query (i didnt know you could do that) I have matched my
format on all query fields to what is used in table design.

Still get error when combining the critieria

I meant lookup fields defined in the table design view.
I would make sure that the datatypes of your fields/columns in the query
correspond with the datatypes of your values in the controls on the form.

--
Duane Hookom
MS Access MVP
--

Yes, I have 3 tables in this query tblCustomer, with TAXID as Primary Key,
tblPolicy, that has a primary key of it's own PolicyID, and looks up the
TAXID and finally tblPolicyDetail with own primary key, and looks up the
PolicyID.

Thanks for your reply

:

Are there any lookup fields involved in the table(s)?

--
Duane Hookom
MS Access MVP
--

I have a query and want to set up the criteria for filtering. I have
one
field for date [EntryDate] and another field called [Company]. I would
like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the
correct
dates and works fine. Then I can remove the entrydate criteria and
enter
the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error that
says
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain to many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
varaibles."

Please help me with this, I have searched for a day and half for an
answer
and can not find one with type of problem. Thank you very much.

 

[Duane Hookom]

You might want to select Query->Parameters and enter:
[Forms]![CompanyTrans]![EntryDate] Date/Time

Keep in mind that queries expect dates in the format m/d/y.


--
Duane Hookom
MS Access MVP
--

UPDATE - Here is my query code for you to look at, maybe that might help
more:

SELECT tblPolicy.EntryDate, tblPolicy.BinderDate, tblCustomers.FirstName &
"
" & [LastName] AS [Customer Name], tblPolicyDetail.Company,
tblPolicyDetail.Premium, tblPolicyDetail.CommissionRate,
tblPolicyDetail.Commission, tblPolicyDetail.AmountSubmitted
FROM (tblCustomers INNER JOIN tblPolicy ON tblCustomers.TaxID =
tblPolicy.TaxID) INNER JOIN tblPolicyDetail ON tblPolicy.PolicyID =
tblPolicyDetail.PolicyID
WHERE (((tblPolicy.EntryDate)=[Forms]![CompanyTrans]![EntryDate]) AND
((tblPolicyDetail.Company)=[Forms]![CompanyTrans]![Company]));

Yes they are defined within the table design view. No the Look up fields
are
not within the query (i didnt know you could do that) I have matched my
format on all query fields to what is used in table design.

Still get error when combining the critieria

I meant lookup fields defined in the table design view.
I would make sure that the datatypes of your fields/columns in the
query
correspond with the datatypes of your values in the controls on the
form.

--
Duane Hookom
MS Access MVP
--

Yes, I have 3 tables in this query tblCustomer, with TAXID as Primary
Key,
tblPolicy, that has a primary key of it's own PolicyID, and looks up
the
TAXID and finally tblPolicyDetail with own primary key, and looks up
the
PolicyID.

Thanks for your reply

:

Are there any lookup fields involved in the table(s)?

--
Duane Hookom
MS Access MVP
--

I have a query and want to set up the criteria for filtering. I
have
one
field for date [EntryDate] and another field called [Company]. I
would
like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the
correct
dates and works fine. Then I can remove the entrydate criteria
and
enter
the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error
that
says
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain to many complicated
elements.
Try simplifying the expression by assigning parts of the
expression to
varaibles."

Please help me with this, I have searched for a day and half for
an
answer
and can not find one with type of problem. Thank you very much.

 

[Guest]

I got same error message from that....However i found a temp solution, i
created a 2nd query for each company (not that many) then added the date
criteria to it. This maybe the long way, but works for me and all I knew to
do with my limited knowledge. I am trying to learn Access, so this database
may not be pretty, but gets the job done and will be a starting point for
tweaking in the future.

Thank you for your help

You might want to select Query->Parameters and enter:
[Forms]![CompanyTrans]![EntryDate] Date/Time

Keep in mind that queries expect dates in the format m/d/y.


--
Duane Hookom
MS Access MVP
--

UPDATE - Here is my query code for you to look at, maybe that might help
more:

SELECT tblPolicy.EntryDate, tblPolicy.BinderDate, tblCustomers.FirstName &
"
" & [LastName] AS [Customer Name], tblPolicyDetail.Company,
tblPolicyDetail.Premium, tblPolicyDetail.CommissionRate,
tblPolicyDetail.Commission, tblPolicyDetail.AmountSubmitted
FROM (tblCustomers INNER JOIN tblPolicy ON tblCustomers.TaxID =
tblPolicy.TaxID) INNER JOIN tblPolicyDetail ON tblPolicy.PolicyID =
tblPolicyDetail.PolicyID
WHERE (((tblPolicy.EntryDate)=[Forms]![CompanyTrans]![EntryDate]) AND
((tblPolicyDetail.Company)=[Forms]![CompanyTrans]![Company]));

Yes they are defined within the table design view. No the Look up fields
are
not within the query (i didnt know you could do that) I have matched my
format on all query fields to what is used in table design.

Still get error when combining the critieria

:

I meant lookup fields defined in the table design view.
I would make sure that the datatypes of your fields/columns in the
query
correspond with the datatypes of your values in the controls on the
form.

--
Duane Hookom
MS Access MVP
--

Yes, I have 3 tables in this query tblCustomer, with TAXID as Primary
Key,
tblPolicy, that has a primary key of it's own PolicyID, and looks up
the
TAXID and finally tblPolicyDetail with own primary key, and looks up
the
PolicyID.

Thanks for your reply

:

Are there any lookup fields involved in the table(s)?

--
Duane Hookom
MS Access MVP
--

I have a query and want to set up the criteria for filtering. I
have
one
field for date [EntryDate] and another field called [Company]. I
would
like
to search for companies that have a certain date.

What I can do is [Forms]![QUERYNAME]![EntryDate] this will get the
correct
dates and works fine. Then I can remove the entrydate criteria
and
enter
the
company criteria and will pull up the correct companies.

However when I try to do both at same time I am getting an error
that
says
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain to many complicated
elements.
Try simplifying the expression by assigning parts of the
expression to
varaibles."

Please help me with this, I have searched for a day and half for
an
answer
and can not find one with type of problem. Thank you very much.