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

[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?

 

[Douglas J. Steele]

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?

 

[Hugh self taught]

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)

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?

 

[Douglas J. Steele]

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)

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)

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?

 

[Hugh self taught]

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"

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)

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?

 

[Douglas J. Steele]

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)

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"

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)

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?

 

[Hugh self taught]

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.

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)

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"

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?

 

[Douglas J. Steele]

Sorry, nothing else occurs to me.

Good luck!