Multiple search on Query???

[cppiston]

I have a query that takes criteia from one combo box and two text boxes from a form. It is layed out like this: The first criteria takes from a combo box, Forging, and searches a table like this. ForgingID field: [Forms]![frm_openForgingForSales].[Combo1]. ForgingID1 field OR [Forms]![frm_openForgingForSales].[Combo1]. So this searches on the table 2 fields for one value from a combo box to pull all matching data... Now on the qury there is also this statement Between [Forms]![frm_openForgingForSales].[Text38] And [Forms]![frm_openForgingForSales].[Text42]The purpose of this is to search between dates entered by the user. So the outcome on the final form should be, for example, all forgings of X111 in sold between the specified dates... but it only works if it seacrches one field(either ForgingID and ForgingID10 it will not work when searching both....

SQL View is like this:SELECT [Job Order Entry].ForgingID, [Job Order Entry].ForgingID1, [Job Order Entry].OrderEntryDate, [Job Order Entry].JobID, [Job Order Entry].CustomerNumber, [Job Order Entry].Bore, [Job Order Entry].CompressionHgt, [Job Order Entry].ProfileNumber, [Job Order Entry].MotorTypeID, [Job Order Entry].HeadTypeID
FROM [Job Order Entry]
WHERE ((([Job Order Entry].ForgingID)=[Forms]![frm_openForgingForSales].[Combo24]) AND (([Job Order Entry].OrderEntryDate) Between [Forms]![frm_openForgingForSales].[Text38] And [Forms]![frm_openForgingForSales].[Text42])) OR ((([Job Order Entry].ForgingID1)=[Forms]![frm_openForgingForSales].[Combo24]))
ORDER BY [Job Order Entry].OrderEntryDate DESC;

What to do???

 

[PaulF]

When adding an "OR" condition for more than one field, in the Query By Example Grid (QBE), you drop down a line in the criteria row, so try adding the Criteria for the Dates to cover both Fields, instead of just on the same line (in the QBE) as the criteria for ForgingID. Add it to the next line down, so that it will also apply to ForgingID1.

PaulF

 

[cppiston]

That is the way have set it up... the problem is it doesnt accept the by date query... it shows all dates, not just the user specified dates

 

[LittleSmudge]

It might be just the way you've types it in the post but I don't see the "#" makes to delimit the dates in the code above.

eg.
.. ..
AND (([Job Order Entry].OrderEntryDate) Between
#[Forms]![frm_openForgingForSales].[Text38]# And
#[Forms]![frm_openForgingForSales].[Text42]))# OR

Also remember that Access SQL will interpret the date in mm/dd/yy format REGARDLESS of the international setting of your system and [Text38]'s date format.
This often leads to strange date limit performance.



GL S