Dear All,

I am using the following query for retrieving data between customer
code

SELECT tblOrders.CustomerCode, [tblOrder Details].InventoryCode,
tblInventory.InventoryName, [tblOrder Details].Quantity FROM tblOrders
INNER JOIN (tblInventory INNER JOIN [tblOrder Details] ON
tblInventory.InventoryCode = [tblOrder Details].InventoryCode) ON
tblOrders.OrderID = [tblOrder Details].OrderID WHERE
(((tblOrders.CustomerCode) Between [Forms]![frmFilter3]![CboCustFrom]
And [Forms]![frmFilter3]![CboCustTo]));

how could I retrive all records if the parameter leaved blank, kindly
note that the customer field is a text field with field size of 6,
customer code is mixture of letter & digits & it also could be all
letter or all digits

Thanks & Regards,
--
Abdul Shakeel

On 10/11/2010 12:36:43, Shakeel wrote:
This is a bit complex.
If you change the query for your combo boxes which I assume is something like
"SELECT CustomerName, CustomerCode FROM Customers ORDER by CustomerName" to a
Union Query "SELECT CustomerName, CustomerCode, 1 AS Pos FROM Customers UNION
SELECT 'B.O.F.' AS CustomerName, ' ' AS CustomerCode, 0 AS Pos ORDER BY Pos,
CustomerName;" This should have the first entry in the CboCustFrom box =
"B.O.F" - begining of file. You need a similar set up for your CboCustTo box
showing "E.O.F." - End of file

Your SQL then becomes

On 10/11/2010 15:24:46, "Phil" wrote:

Sorry this News server seems to send meggages whether they are complete or
not.

To continue

You then need to build the SQL
SQLStg = "SELECT tblOrders.CustomerCode, [tblOrder Details].InventoryCode, "
SQLStg = SQLStg & "tblInventory.InventoryName, [tblOrder Details].Quantity
FROM tblOrders " SQLStg = SQLStg & "INNER JOIN (tblInventory INNER JOIN
[tblOrder Details] ON " SQLStg = SQLStg & "tblInventory.InventoryCode =
[tblOrder Details].InventoryCode) ON " SQLStg = SQLStg & "tblOrders.OrderID =
[tblOrder Details].OrderID " If CboCustFrom <> "B.O.F." Then

On 10/11/2010 15:32:15, "Phil" wrote:
Sorry it's sent it again
The last bit should read
If CboCustFrom <> "B.O.F." then
SQLStg LStg = SQLStg & "WHERE tblOrders.CustomerCode >=
[Forms]![frmFilter3]![CboCustFrom] End If
If CboCustTo <> "E.O.F." then
If CboCustFrom <> "B.O.F." then
SQLStg SQLStg = SQLStg & "AND tblOrders.CustomerCode >=
[Forms]![frmFilter3]![CboCustTo] Else
SQLStg SQLStg = SQLStg & "WHERE tblOrders.CustomerCode <=
[Forms]![frmFilter3]![CboCustTo] End If
End If

Finally got there. Hope you can make sense of these bits

Phil

On Nov 10, 8:42*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Dear Phill

Thanks for your effort but i have found a simpler way to retrieve data
by setting criteria this:
Between Nz([Forms]![frmFilter3]![CboCustFrom],"") And Nz([Forms]!
[frmFilter3]![CboCustTo],"ZZZZZZ")

That won't return the records where the field is null.

It is actually possible to craft the WHERE clause of the query to return
records if the parameter is null, but it's much more messy. A better
solution is to use a Filter on your form, and just ignore the box(es) that
are null. Here's an example of how to do that:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The article contains a free sample database that illustrates how to do it.

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Shakeel" wrote in message
news:015d7eed-0869-49dd-a690-762cbbcaa5ce (AT) 35g2000prt (DOT) googlegroups.com...

On Nov 10, 8:42 pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Dear Phill

Thanks for your effort but i have found a simpler way to retrieve data
by setting criteria this:
Between Nz([Forms]![frmFilter3]![CboCustFrom],"") And Nz([Forms]!
[frmFilter3]![CboCustTo],"ZZZZZZ")