Date Field problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I am trying to pull data from in where the table has many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


For some reason, my query is pulling data from all dates in the table
regardless of what date I specify to pull from. I only want data from Dec of
2005, but the results include data from prior months as well. I have changed
the Criteria in the date field to:

Like "12/*/2005" -- with no luck and

Between "12/01/2005" And "12/31/2005" -- with no luck

Every time it pulls data from the whole date range included in the table.
The criteria in the other 2 fields is working fine, it's just the date that
is giving me trouble. Any suggestions?

I appreciate any help you could provide.
 
Your "In (1455,4671,4743,4921,5882,7544,9125)" and "Between [Enter Begin
Date] And [Enter End Date]" criteria needs to be on each line as your Like
criteria. What's happening now is only the first line requires all three
criteria to work. The rest of the lines only require something like
"*caster*" no matter what the date.

If you go to design view of the query and select SQL view, you'll see what
you're asking the query to do in words. AND and OR clauses can be confusing.
 
I have a table that I am trying to pull data from in where the table has many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]
Click to expand...

What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
 
Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType, MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc) Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc) Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




John Vinson said:
I have a table that I am trying to pull data from in where the table has many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]
Click to expand...

What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
 
If you paste the following into the SQl window it should work as desired.
Once you save it and look at it in the grid view it will be much more
complex - Access will redo all the extra parentheses and Access will combine
each of the like criteria with the OrderDate and SupplierID to give you many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Lucien said:
Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc) Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




John Vinson said:
I have a table that I am trying to pull data from in where the table has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]
Click to expand...

What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
 
I pasted that in the SQL view and tried the query. I received this error
message:

This Expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Any suggestions?


John Spencer said:
If you paste the following into the SQl window it should work as desired.
Once you save it and look at it in the grid view it will be much more
complex - Access will redo all the extra parentheses and Access will combine
each of the like criteria with the OrderDate and SupplierID to give you many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Lucien said:
Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date] And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc) Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




John Vinson said:
On Wed, 25 Jan 2006 11:42:03 -0800, Lucien

I have a table that I am trying to pull data from in where the table has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Try this version.

PARAMETERS [Enter Begin Date] datetime, [Enter End Date] DateTime;
SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

If that fails, try dropping typing all of that in. Sometimes copying and
pasting from the newsgroups enters extraneous characters and those can cause
problems.

Lucien said:
I pasted that in the SQL view and tried the query. I received this error
message:

This Expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Any suggestions?


John Spencer said:
If you paste the following into the SQl window it should work as desired.
Once you save it and look at it in the grid view it will be much more
complex - Access will redo all the extra parentheses and Access will
combine
each of the like criteria with the OrderDate and SupplierID to give you
many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Lucien said:
Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date]
And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc)
Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




:

On Wed, 25 Jan 2006 11:42:03 -0800, Lucien

I have a table that I am trying to pull data from in where the table
has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
I copied and pasted the new version into the SQL view and I am still getting
the same error message.
Then I manually typed everything in word for word, executed the query and I
am still getting the same message.




John Spencer said:
Try this version.

PARAMETERS [Enter Begin Date] datetime, [Enter End Date] DateTime;
SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

If that fails, try dropping typing all of that in. Sometimes copying and
pasting from the newsgroups enters extraneous characters and those can cause
problems.

Lucien said:
I pasted that in the SQL view and tried the query. I received this error
message:

This Expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Any suggestions?


John Spencer said:
If you paste the following into the SQl window it should work as desired.
Once you save it and look at it in the grid view it will be much more
complex - Access will redo all the extra parentheses and Access will
combine
each of the like criteria with the OrderDate and SupplierID to give you
many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date]
And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc)
Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




:

On Wed, 25 Jan 2006 11:42:03 -0800, Lucien

I have a table that I am trying to pull data from in where the table
has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Hmm. I guess you will have to try breaking it down into parts. To see where the
error is coming from.

Try removing the entire WHERE Clause. If that works, add in just the date test,
then the date plus the supplier, then just one of the itemdescriptions.desc

AND DESC could be a problem since DESC is a reserved word in SQL. Try
surrounding all the field names that are DESC with [] to make them read
ItemDescriptions.[Desc]

I should have noticed that before.
I copied and pasted the new version into the SQL view and I am still getting
the same error message.
Then I manually typed everything in word for word, executed the query and I
am still getting the same message.

John Spencer said:
Try this version.

PARAMETERS [Enter Begin Date] datetime, [Enter End Date] DateTime;
SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

If that fails, try dropping typing all of that in. Sometimes copying and
pasting from the newsgroups enters extraneous characters and those can cause
problems.

Lucien said:
I pasted that in the SQL view and tried the query. I received this error
message:

This Expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Any suggestions?


:

If you paste the following into the SQl window it should work as desired.
Once you save it and look at it in the grid view it will be much more
complex - Access will redo all the extra parentheses and Access will
combine
each of the like criteria with the OrderDate and SupplierID to give you
many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date]
And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc)
Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




:

On Wed, 25 Jan 2006 11:42:03 -0800, Lucien

I have a table that I am trying to pull data from in where the table
has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Well, I removed the where clause.....worked fine. I added the date test and
it worked fine. Then I added the supplier criteria and found the error. The
data type for the SupplierID field was text. So, I just put quotation marks
around each supplier number, added the desription criteria, and it worked
beautifully!

Thanks for all the help!!!!




John Spencer said:
Hmm. I guess you will have to try breaking it down into parts. To see where the
error is coming from.

Try removing the entire WHERE Clause. If that works, add in just the date test,
then the date plus the supplier, then just one of the itemdescriptions.desc

AND DESC could be a problem since DESC is a reserved word in SQL. Try
surrounding all the field names that are DESC with [] to make them read
ItemDescriptions.[Desc]

I should have noticed that before.
I copied and pasted the new version into the SQL view and I am still getting
the same error message.
Then I manually typed everything in word for word, executed the query and I
am still getting the same message.

John Spencer said:
Try this version.

PARAMETERS [Enter Begin Date] datetime, [Enter End Date] DateTime;
SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail, MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

If that fails, try dropping typing all of that in. Sometimes copying and
pasting from the newsgroups enters extraneous characters and those can cause
problems.

I pasted that in the SQL view and tried the query. I received this error
message:

This Expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Any suggestions?


:

If you paste the following into the SQl window it should work as desired.
Once you save it and look at it in the grid view it will be much more
complex - Access will redo all the extra parentheses and Access will
combine
each of the like criteria with the OrderDate and SupplierID to give you
many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID, MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In (1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin Date]
And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*") AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR (((ItemDescriptions.Desc)
Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR (((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




:

On Wed, 25 Jan 2006 11:42:03 -0800, Lucien

I have a table that I am trying to pull data from in where the table
has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion - you're
seeing all of the casters, wheels and wheelbarrows. (By the way, all
records found by the wheelbarrow criterion will have already been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto *every
line* of the OR clause; or else go into SQL view and use parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
HOORAY!!!
Glad you found the cause of the problem. And now you have a new
troubleshooting skill.

Wish I would have thought to ask you about field types earlier.
Lucien said:
Well, I removed the where clause.....worked fine. I added the date test
and
it worked fine. Then I added the supplier criteria and found the error.
The
data type for the SupplierID field was text. So, I just put quotation
marks
around each supplier number, added the desription criteria, and it worked
beautifully!

Thanks for all the help!!!!




John Spencer said:
Hmm. I guess you will have to try breaking it down into parts. To see
where the
error is coming from.

Try removing the entire WHERE Clause. If that works, add in just the
date test,
then the date plus the supplier, then just one of the
itemdescriptions.desc

AND DESC could be a problem since DESC is a reserved word in SQL. Try
surrounding all the field names that are DESC with [] to make them read
ItemDescriptions.[Desc]

I should have noticed that before.
I copied and pasted the new version into the SQL view and I am still
getting
the same error message.
Then I manually typed everything in word for word, executed the query
and I
am still getting the same message.

:

Try this version.

PARAMETERS [Enter Begin Date] datetime, [Enter End Date] DateTime;
SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant, MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In
(1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

If that fails, try dropping typing all of that in. Sometimes copying
and
pasting from the newsgroups enters extraneous characters and those
can cause
problems.

I pasted that in the SQL view and tried the query. I received this
error
message:

This Expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression
to
variables.

Any suggestions?


:

If you paste the following into the SQl window it should work as
desired.
Once you save it and look at it in the grid view it will be much
more
complex - Access will redo all the extra parentheses and Access
will
combine
each of the like criteria with the OrderDate and SupplierID to
give you
many
more lines of criteria.

SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant,
MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE MasterAvailabilityData.OrderDate Between [Enter Begin Date]
And [Enter End Date] AND
MasterAvailabilityData.SupplierID In
(1455,4671,4743,4921,5882,7544,9125)
AND
(ItemDescriptions.Desc Like "*brake*"
OR ItemDescriptions.Desc Like "*caster*"
OR ItemDescriptions.Desc Like "*wheel*"
OR ItemDescriptions.Desc Like "*rigid*"
OR ItemDescriptions.Desc Like "*wheelbarrow*")
ORDER BY MasterAvailabilityData.OrderDate;

Here is the SQL view of the query:

SELECT MasterAvailabilityData.OrderID,
MasterAvailabilityData.OrderDate,
MasterAvailabilityData.OrderHeaderType,
MasterAvailabilityData.OrderLineType,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RequestedQty,
MasterAvailabilityData.LineAvailPassFail,
MasterAvailabilityData.SupplierID,
MasterAvailabilityData.ActualAvailPlant,
MasterAvailabilityData.RSL
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between [Enter Begin
Date]
And
[Enter End Date]) AND ((ItemDescriptions.Desc) Like "*brake*")
AND
((MasterAvailabilityData.SupplierID) In
(1455,4671,4743,4921,5882,7544,9125))) OR
(((ItemDescriptions.Desc)
Like
"*caster*")) OR (((ItemDescriptions.Desc) Like "*wheel*")) OR
(((ItemDescriptions.Desc) Like "*rigid*")) OR
(((ItemDescriptions.Desc)
Like
"*wheelbarrow*"))
ORDER BY MasterAvailabilityData.OrderDate;




:

On Wed, 25 Jan 2006 11:42:03 -0800, Lucien

I have a table that I am trying to pull data from in where the
table
has
many
fields. In one field my criteria is:

In (1455,4671,4743,4921,5882,7544,9125)

In another field my criteria is:

Criteria Like "*brake*"
Or Like "*caster*"
Like "*wheel*"
Like "*rigid*"
Like "*wheelbarrow*"

In my date field my criteria is:

Between [Enter Begin Date] And [Enter End Date]


What's happening is that it's only applying the IN and BETWEEN
criteria in conjunction with the Like "*brake*" criterion -
you're
seeing all of the casters, wheels and wheelbarrows. (By the
way, all
records found by the wheelbarrow criterion will have already
been
found by the wheel criterion <g>)...

You need to copy both the IN and the BETWEEN criteria onto
*every
line* of the OR clause; or else go into SQL view and use
parentheses
so that all of the OR clauses are wrapped in one set of parens.

If you'll post the SQL of the query, someone should be able to
help
recast it.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date/Time Selection problem!!!!! 10
criteria on form 1
Date parameter question 2
criteria for date field using iif() 1
Query - pull past 12 months 1
Criteria help 4
Between Date 10
Between Dates Criteria 2

Back
Top