Date field does not work in my form. 1

[Tadynn]

I'm having trouble with searching by date range with a form that I've designed. Basically, I have about 16 text boxes that a user can use to query the actual query by, and all of them work and interact together okay - except my date range query. Whenever I query a date range, It ignores the query and just dumps the entire database in my results form. Please find below the query that is used and the object properties for my form that I query with. Also, being a novice, you may notice that I am using a simple SELECT query as I honestly don't know of another way. Does anybody know of another type of query to use??? Thanks.

SQL for my Query.

SELECT [dbo_LOTxLOCxID].[StorerKey], [dbo_LOTxLOCxID].[Sku], [dbo_SKU].[DESCR], [dbo_LOTATTRIBUTE].[Lottable03], [dbo_LOTATTRIBUTE].[Lottable02], [dbo_LOTATTRIBUTE].[Lot], [dbo_LOTxLOCxID].[Id], [dbo_LOTxLOCxID].[Qty], [dbo_LOTxLOCxID].[Loc], [dbo_LOTATTRIBUTE].[Lottable01], [dbo_LOTATTRIBUTE].[Lottable04]
FROM (dbo_LOTATTRIBUTE INNER JOIN dbo_LOTxLOCxID ON [dbo_LOTATTRIBUTE].[Lot]=[dbo_LOTxLOCxID].[Lot]) INNER JOIN dbo_SKU ON ([dbo_LOTxLOCxID].[StorerKey]=[dbo_SKU].[StorerKey]) AND ([dbo_LOTxLOCxID].[Sku]=[dbo_SKU].[Sku])
WHERE (((([dbo_LOTxLOCxID].[StorerKey])>=[Forms]![Inventory Balance]![From Storer])) And ((([dbo_LOTxLOCxID].[StorerKey])<=[Forms]![Inventory Balance]![To Storer])) And ((([dbo_LOTxLOCxID].[Sku])>=[Forms]![Inventory Balance]![From Commodity])) And ((([dbo_LOTxLOCxID].[Sku])<=[Forms]![Inventory Balance]![To Commodity])) And ((([dbo_LOTATTRIBUTE].[Lottable03])>=[Forms]![Inventory Balance]![From Cust Lot]) Or IsNull([Forms]![Inventory Balance]![From Cust Lot])) And ((([dbo_LOTATTRIBUTE].[Lottable03])<=[Forms]![Inventory Balance]![To Cust Lot])) And ((([dbo_LOTATTRIBUTE].[Lottable02])>=[Forms]![Inventory Balance]![From Nature 20]) Or IsNull([Forms]![Inventory Balance]![From Nature 20])) And ((([dbo_LOTATTRIBUTE].[Lottable02])<=[Forms]![Inventory Balance]![To Nature 20])) And ((([dbo_LOTATTRIBUTE].[Lot])>=[Forms]![Inventory Balance]![From Lot])) And ((([dbo_LOTATTRIBUTE].[Lot])<=[Forms]![Inventory Balance]![To Lot])) And ((([dbo_LOTxLOCxID].[Id])>=[Forms]![Inventory Balance]![From Pallet Id])) And ((([dbo_LOTxLOCxID].[Id])<=[Forms]![Inventory Balance]![To Pallet Id])) And ((([dbo_LOTxLOCxID].[Loc])>=[Forms]![Inventory Balance]![From Location])) And ((([dbo_LOTxLOCxID].[Loc])<=[Forms]![Inventory Balance]![To Location])) And ((([dbo_LOTATTRIBUTE].[Lottable04])<=[Forms]![Inventory Balance]![From Date]) Or IsNull([Forms]![Inventory Balance]![From Date])) And ((([dbo_LOTATTRIBUTE].[Lottable04])<=[Forms]![Inventory Balance]![To Date]) And ((([dbo_LOTxLOCxID].[Qty])>0))));

This query links directly to tables in another program that we use. The date field is actually called 'Lottable04'.

Also, please find below the text box properties for my search forms, and their default values.

Name Default Value Value needed
From Storer 0 Always
To Storer ZZZZZZZZZ Always
From Commodity 0 Always
To Commodity ZZZZZZZZZ Always
From Cust Lot Sometimes
To Cust Lot ZZZZZZZZZ Sometimes
From Nature 20 Sometimes
To Nature 20 ZZZZZZZZZ Sometimes
From Lot 0 Always
To Lot ZZZZZZZZZ Always
From Pallet Id 0 Always
To Pallet Id ZZZZZZZZZ Always
From Location 0 Always
To Location ZZZZZZZZZ Always
From Date Sometimes
To Date =now() Sometimes

I appreciate any help that anybody can give me on this one. As it's truly got me buggered!!!

Thank you.

 

[tlbroadbent]

I note that the query criteria for the FROM and To dates are both &quot;<=&quot;. Shouldn't the criteria for the FROM date be &quot;>=&quot;?

([dbo_LOTATTRIBUTE].[Lottable04]<=[Forms]![Inventory Balance]![From Date] Or IsNull([Forms]![Inventory Balance]![From Date]))
And ([dbo_LOTATTRIBUTE].[Lottable04]<=[Forms]![Inventory Balance]![To Date]) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.