i don't get it 1

[chaosguy]

hey if i ahve a criteria in a query that draws a value from a form, if the value in the form is null shouldn't all the record show up in the query?

I made a query that has like 3 criteria. my form when i click a button would set the values in two combo box as null and a value in the other, it then opens a query that should show all records with the data that was entered in the field that was filled. When the query opens tho i don't see anything, does anyone know y? My relationships are fine and everything and all record show up in the query when i take out the relationships. it kinda sucks. can anyone help? thanks in advance.

chaosguy

Chaosguy - To die would be an awefully big adventure.

 

[Crowley16]

if you search for a null value, then the query will only return records with a null value...

--------------------
Procrastinate Now!

 

[chaosguy]

ok, so what do i do? can i like just make the query ignore the other values when they're not present r something?

Chaosguy - To die would be an awefully big adventure.

 

[MajP]

I doubt your "relationships are fine". Show us the sql.

 

[chaosguy]

Here's the stuff in sql view


SELECT [Customer Info].[Customer ID], [Customer Info].[First Name], [Customer Info].[Last Name], [Customer Info].Address, [Customer Info].[Tele Cell], [Customer Info].[Tel Home], [Customer Info].[Tel Work], [Order Info].[Order No], [Order Info].[Order Date], [Order Info].[Order Status] AS Expr1, [Order Info].[Order Cost], [Order Info].[Amount Paid], [Order Info].Balance, [Order Info].[Reciept Number], [Order Info].[Reciept Number 2], [Order Info].[Find in order], [Product Info].[Product ID], [Product Info].[Product No], [Product Info].[Product Name], [Product Info].[Product Size], [Product Info].Supplier, [Product Info].Price, [Product Info].Quantity, [Product Info].[Shipping and Handling], [Product Info].[Total Cost]
FROM [Product Info] INNER JOIN (([Customer Info] INNER JOIN [Order Info] ON [Customer Info].[Customer ID] = [Order Info].[Customer ID]) INNER JOIN [Order Products] ON [Order Info].[Order No] = [Order Products].[Order No]) ON [Product Info].[Product ID] = [Order Products].[Product ID]
WHERE ((([Customer Info].[Customer ID])=[Forms]![search]![data]) AND (([Customer Info].[First Name])=[Forms]![search]![data]) AND (([Customer Info].[Last Name])=[Forms]![search]![data]) AND (([Order Info].[Order No])=[Forms]![search]![data]) AND (([Order Info].[Order Date]) Between [Forms]![search]![StartDate] And [Forms]![search]![EndDate]) AND (([Order Info].[Reciept Number])=[Forms]![search]![data]) AND (([Order Info].[Reciept Number 2])=[Forms]![search]![data]));


Chaosguy - To die would be an awefully big adventure.

 

[MajP]

Maybe you want OR instead of AND. You are trying to return a specific person, with a specific first name, with a specific last name, with a specific order no, with a specific order date, between two dates, with two specific reciept numbers.

I would be suprised if this returned anything.

WHERE ((([Customer Info].[Customer ID])=[Forms]![search]![data]) AND (([Customer Info].[First Name])=[Forms]![search]![data]) AND (([Customer Info].[Last Name])=[Forms]![search]![data]) AND (([Order Info].[Order No])=[Forms]![search]![data]) AND (([Order Info].[Order Date]) Between [Forms]![search]![StartDate] And [Forms]![search]![EndDate]) AND (([Order Info].[Reciept Number])=[Forms]![search]![data]) AND (([Order Info].[Reciept Number 2])=[Forms]![search]![data]));

 

[chaosguy]

I have apsolutely no idea what SQL was, but anyways ur stuff helped. I put the or instead of and and well all's well now. thank u.

Chaosguy - To die would be an awefully big adventure.