WHERE <> "text box" in SQL not referencing <> table field does

  • Thread starter Thread starter Hugh self taught
  • Start date Start date
H

Hugh self taught

Hi Guys & Gals,

I have a form with a (not yet hidden) text box referencing Column(1) of a
cbo. I then use that in a sql data parameter as follows:

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE ((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation] And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

The first part of the WHERE expression
"((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]" works
fine, but the 2nd part "And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))" doesn't work at all.
In fact if I remove the first part I see all records. It's as though the
reference to a text value doesn't work but reference to a table field value
does.

Any suggestions on getting around this?
 
What does "doesn't work at all" mean?

I would expect your query to return nothing when the hidden control contains
"Companion", and return data for candidates with types other than what's in
the hidden control when the hidden control does not contain "Companion". Is
that different than what you're expecting? Is it different than what you're
getting?
 
Hi Doug,

You understand exactly what I'm trying achieve, but I'm getting all records
including "Companion". All the records come from one table. Their "type"
catagorises them so I can match them with others.

I've gone so far as to copy the word "Companion" from the table field &
paste it into the statement to ensure spelling & case. I've double double
checked my syntax but can't lay my finger on the cause. Now I'm looking for
that outside view because I may not be seeing the wood for the trees anymore.


Douglas J. Steele said:
What does "doesn't work at all" mean?

I would expect your query to return nothing when the hidden control contains
"Companion", and return data for candidates with types other than what's in
the hidden control when the hidden control does not contain "Companion". Is
that different than what you're expecting? Is it different than what you're
getting?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
Hi Guys & Gals,

I have a form with a (not yet hidden) text box referencing Column(1) of a
cbo. I then use that in a sql data parameter as follows:

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE ((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]
And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

The first part of the WHERE expression
"((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]" works
fine, but the 2nd part "And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))" doesn't work at
all.
In fact if I remove the first part I see all records. It's as though the
reference to a text value doesn't work but reference to a table field
value
does.

Any suggestions on getting around this?
Click to expand...
Click to expand...
 
You didn't have to use a Lookup Field on the Designation field, did you? If
so, then it likely doesn't contain Companion, but rather the primary key
from the related table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
Hi Doug,

You understand exactly what I'm trying achieve, but I'm getting all
records
including "Companion". All the records come from one table. Their "type"
catagorises them so I can match them with others.

I've gone so far as to copy the word "Companion" from the table field &
paste it into the statement to ensure spelling & case. I've double double
checked my syntax but can't lay my finger on the cause. Now I'm looking
for
that outside view because I may not be seeing the wood for the trees
anymore.


Douglas J. Steele said:
What does "doesn't work at all" mean?

I would expect your query to return nothing when the hidden control
contains
"Companion", and return data for candidates with types other than what's
in
the hidden control when the hidden control does not contain "Companion".
Is
that different than what you're expecting? Is it different than what
you're
getting?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
Hi Guys & Gals,

I have a form with a (not yet hidden) text box referencing Column(1) of
a
cbo. I then use that in a sql data parameter as follows:

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE ((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]
And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

The first part of the WHERE expression
"((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]"
works
fine, but the 2nd part "And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))" doesn't work
at
all.
In fact if I remove the first part I see all records. It's as though
the
reference to a text value doesn't work but reference to a table field
value
does.

Any suggestions on getting around this?
Click to expand...
Click to expand...
Click to expand...
 
The Designation field is a cboBox.Column(1) & is the control source for the
text box HiddenColRef. On the form the correct data is displayed in the text
box. That's what is so confusing (& frustrating) about it not applying the
<>"Companion"

Douglas J. Steele said:
You didn't have to use a Lookup Field on the Designation field, did you? If
so, then it likely doesn't contain Companion, but rather the primary key
from the related table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
Hi Doug,

You understand exactly what I'm trying achieve, but I'm getting all
records
including "Companion". All the records come from one table. Their "type"
catagorises them so I can match them with others.

I've gone so far as to copy the word "Companion" from the table field &
paste it into the statement to ensure spelling & case. I've double double
checked my syntax but can't lay my finger on the cause. Now I'm looking
for
that outside view because I may not be seeing the wood for the trees
anymore.


Douglas J. Steele said:
What does "doesn't work at all" mean?

I would expect your query to return nothing when the hidden control
contains
"Companion", and return data for candidates with types other than what's
in
the hidden control when the hidden control does not contain "Companion".
Is
that different than what you're expecting? Is it different than what
you're
getting?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Hi Guys & Gals,

I have a form with a (not yet hidden) text box referencing Column(1) of
a
cbo. I then use that in a sql data parameter as follows:

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE ((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]
And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

The first part of the WHERE expression
"((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]"
works
fine, but the 2nd part "And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))" doesn't work
at
all.
In fact if I remove the first part I see all records. It's as though
the
reference to a text value doesn't work but reference to a table field
value
does.

Any suggestions on getting around this?
Click to expand...
Click to expand...
Click to expand...
 
That doesn't tell me whether the Designation field in the Types table is a
lookup field or not...

If you look at the table, do you see a combo box for the field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
The Designation field is a cboBox.Column(1) & is the control source for
the
text box HiddenColRef. On the form the correct data is displayed in the
text
box. That's what is so confusing (& frustrating) about it not applying the
<>"Companion"

Douglas J. Steele said:
You didn't have to use a Lookup Field on the Designation field, did you?
If
so, then it likely doesn't contain Companion, but rather the primary key
from the related table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
Hi Doug,

You understand exactly what I'm trying achieve, but I'm getting all
records
including "Companion". All the records come from one table. Their
"type"
catagorises them so I can match them with others.

I've gone so far as to copy the word "Companion" from the table field &
paste it into the statement to ensure spelling & case. I've double
double
checked my syntax but can't lay my finger on the cause. Now I'm looking
for
that outside view because I may not be seeing the wood for the trees
anymore.


:

What does "doesn't work at all" mean?

I would expect your query to return nothing when the hidden control
contains
"Companion", and return data for candidates with types other than
what's
in
the hidden control when the hidden control does not contain
"Companion".
Is
that different than what you're expecting? Is it different than what
you're
getting?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Hi Guys & Gals,

I have a form with a (not yet hidden) text box referencing Column(1)
of
a
cbo. I then use that in a sql data parameter as follows:

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type =
Types.Types_Idx
WHERE
((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]
And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

The first part of the WHERE expression
"((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]"
works
fine, but the 2nd part "And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))" doesn't
work
at
all.
In fact if I remove the first part I see all records. It's as though
the
reference to a text value doesn't work but reference to a table
field
value
does.

Any suggestions on getting around this?
Click to expand...
Click to expand...
Click to expand...
 
Sorry Doug, Didn't register what you were asking. Answer is no. I don't use
lookup fields in my tables unless I'm absolutely forced to but that hasn't
occured yet. I've double checked to make sure I didn't inadvertently do
something like that but none of that nonsense in my tables.

Douglas J. Steele said:
That doesn't tell me whether the Designation field in the Types table is a
lookup field or not...

If you look at the table, do you see a combo box for the field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hugh self taught said:
The Designation field is a cboBox.Column(1) & is the control source for
the
text box HiddenColRef. On the form the correct data is displayed in the
text
box. That's what is so confusing (& frustrating) about it not applying the
<>"Companion"

Douglas J. Steele said:
You didn't have to use a Lookup Field on the Designation field, did you?
If
so, then it likely doesn't contain Companion, but rather the primary key
from the related table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Hi Doug,

You understand exactly what I'm trying achieve, but I'm getting all
records
including "Companion". All the records come from one table. Their
"type"
catagorises them so I can match them with others.

I've gone so far as to copy the word "Companion" from the table field &
paste it into the statement to ensure spelling & case. I've double
double
checked my syntax but can't lay my finger on the cause. Now I'm looking
for
that outside view because I may not be seeing the wood for the trees
anymore.


:

What does "doesn't work at all" mean?

I would expect your query to return nothing when the hidden control
contains
"Companion", and return data for candidates with types other than
what's
in
the hidden control when the hidden control does not contain
"Companion".
Is
that different than what you're expecting? Is it different than what
you're
getting?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Hi Guys & Gals,

I have a form with a (not yet hidden) text box referencing Column(1)
of
a
cbo. I then use that in a sql data parameter as follows:

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type =
Types.Types_Idx
WHERE
((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]
And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

The first part of the WHERE expression
"((([Forms]![frmCandidates]![HiddenColRef])<>[Types].[Designation]"
works
fine, but the 2nd part "And
([Forms]![frmCandidates]![HiddenColRef])<>"Companion"))" doesn't
work
at
all.
In fact if I remove the first part I see all records. It's as though
the
reference to a text value doesn't work but reference to a table
field
value
does.

Any suggestions on getting around this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top