compare empty form text block

[Ken]

I have sql:
SELECT * FROM Orders WHERE ((Orders.Paragraph)=" & Chr(34) &
[Forms]![OrdersUnbound]![Paragraph] & Chr(34) & "));"
when the text block is empty this evaluates to "". which isn't returning
any results

In the form on lost focus for the text box I evaluate the contents to make
sure the user incloses the input in parenthesis, if nothing is entered, then
I set the box to null.
I made this WHERE clause short for this message, but the Idea is I am
comparing 4 text blocks for duplicates. They can contain basically any info
including null, as long as it isn't the same set twice (like having 4 primary
keys).
If I put this in a query and include "OR Orders.Paragraph IsNull", I get
all the matches for Null Paragraph, not necessarily matching the other three.

 

[Tom Ellison]

Dear Ken:

What is a "text block"?

I'm guessing you're referring to a [Paragraph] control on your form. This
control may be NULL, which screws up your concatenation. Use Nz() to put in
what you want to search when this happens:

SELECT *
FROM Orders
WHERE ((Orders.Paragraph) = " & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & "));"

Does this help?

Tom Ellison

 

[Ken]

I guess it is a text "box". I think what I need to do is the opposite of
Nz(), Is there a way to make sure it is null instead of ""?

Dear Ken:

What is a "text block"?

I'm guessing you're referring to a [Paragraph] control on your form. This
control may be NULL, which screws up your concatenation. Use Nz() to put in
what you want to search when this happens:

SELECT *
FROM Orders
WHERE ((Orders.Paragraph) = " & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & "));"

Does this help?

Tom Ellison

I have sql:
SELECT * FROM Orders WHERE ((Orders.Paragraph)=" & Chr(34) &
[Forms]![OrdersUnbound]![Paragraph] & Chr(34) & "));"
when the text block is empty this evaluates to "". which isn't
returning
any results

In the form on lost focus for the text box I evaluate the contents to make
sure the user incloses the input in parenthesis, if nothing is entered,
then
I set the box to null.
I made this WHERE clause short for this message, but the Idea is I am
comparing 4 text blocks for duplicates. They can contain basically any
info
including null, as long as it isn't the same set twice (like having 4
primary
keys).
If I put this in a query and include "OR Orders.Paragraph IsNull", I get
all the matches for Null Paragraph, not necessarily matching the other
three.

 

[Tom Ellison]

Dear Ken:

The query you originally posted appears to be constructed with odd use of
parens and quotes. To illustrate, I replace the quoted strings to make it
apparent:

SELECT * FROM Orders WHERE ((Orders.Paragraph)="quoted string 1"));"

A semicolon ends the query, and the final double quote is ignored.

Likely this is not what you want. First, try this please:

SELECT *
FROM Orders
WHERE Orders.Paragraph =
Chr(34) & [Forms]![OrdersUnbound]![Paragraph] & Chr(34);

Does this work at least partially?

Now, does this improve it:

SELECT *
FROM Orders
WHERE Orders.Paragraph = Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34);

Tom Ellison

I guess it is a text "box". I think what I need to do is the opposite of
Nz(), Is there a way to make sure it is null instead of ""?

Dear Ken:

What is a "text block"?

I'm guessing you're referring to a [Paragraph] control on your form.
This
control may be NULL, which screws up your concatenation. Use Nz() to put
in
what you want to search when this happens:

SELECT *
FROM Orders
WHERE ((Orders.Paragraph) = " & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & "));"

Does this help?

Tom Ellison

I have sql:
SELECT * FROM Orders WHERE ((Orders.Paragraph)=" & Chr(34) &
[Forms]![OrdersUnbound]![Paragraph] & Chr(34) & "));"
when the text block is empty this evaluates to "". which isn't
returning
any results

In the form on lost focus for the text box I evaluate the contents to
make
sure the user incloses the input in parenthesis, if nothing is entered,
then
I set the box to null.
I made this WHERE clause short for this message, but the Idea is I am
comparing 4 text blocks for duplicates. They can contain basically any
info
including null, as long as it isn't the same set twice (like having 4
primary
keys).
If I put this in a query and include "OR Orders.Paragraph IsNull", I
get
all the matches for Null Paragraph, not necessarily matching the other
three.