Help with SQL syntax

  • Thread starter Thread starter smjohnson1
  • Start date Start date
S

smjohnson1

I am trying to make a combo box that looks up information from an
existing table, using filters such that only those records show that
match the filter. This is the query I am currently using:

Row Source = SELECT DISTINCTROW tblMembers.*
FROM tblMembers WHERE
(((tblMembers.Current)=Yes)) ORDER BY tblMembers.ID;

Where "Current" is a "Yes/No" field in the table.

If I change a members "Current" status to "No" their information no
longer appears in the form I am using. I would like to be able to
still see their information in past entries, but only see current
members for making new entries. It was recommended that I use a query
in the following form.

SELECT ID, LN & ", " & FN AS Dev
FROM tblDeveloper
WHERE ActiveSW=True OR ID = NZ([Forms]![frmActivities]![DevID],0)
ORDER BY LN, FN;

However, I am somewhat new to this, and it doesn't entirely make sense
to me. I do not know what ID, LN or FN are. Could someone explain
this to me or suggest another way in which I can use a combo box the
way I have explained.

Thanks so much in advance for your help.
 
hi,

If your table column is truly a "YES/NO" type, then your query will not
work. "Yes" values are interpreted as -1 and "NO" is interpreted as 0. So
your query would look like:

Row Source = SELECT DISTINCTROW tblMembers.*
FROM tblMembers WHERE
(((tblMembers.Current)='-1')) ORDER BY tblMembers.ID;

Hope this helps
geebee
 
ID = the unique identifier for tblDeveloper, perhaps your primary key
LN = Last Name
FN = First Name
 
SELECT ID, LN & ", " & FN AS Dev
FROM tblDeveloper
WHERE ActiveSW=True OR ID = NZ([Forms]![frmActivities]![DevID],0)
ORDER BY LN, FN;
Click to expand...
Click to expand...

Thank you both for the feedback. I think I have most of it figured out
now, except the
OR ID = NZ([Forms]![frmActivities]![DevID],0) part. In my form I still
get a blank entry in the combo box if a members "Current" status is set
to "No".

Any further assistance would be appreciated.
 
hi,

Maybe I am not understanding you right, but...
"Yes" values are interpreted as -1 and "NO" is interpreted as 0.
So you need to set your form filter to be 0 or -1 to when trying to
determine the [current status] filter.

Hope this helps
geebee

[email protected] said:
SELECT ID, LN & ", " & FN AS Dev
FROM tblDeveloper
WHERE ActiveSW=True OR ID = NZ([Forms]![frmActivities]![DevID],0)
ORDER BY LN, FN;
Click to expand...
Click to expand...

Thank you both for the feedback. I think I have most of it figured out
now, except the
OR ID = NZ([Forms]![frmActivities]![DevID],0) part. In my form I still
get a blank entry in the combo box if a members "Current" status is set
to "No".

Any further assistance would be appreciated.
Click to expand...
 
I tried using 0 and -1 and it didn't work, but with Yes and No it seems
to work fine. The problem I have is when I have made an entry and then
update the member status to No meaning not current, that persons name
doesn't appear in the form where it had been previously entered.

So the combo box does half of what I am trying to do, which is to limit
list of possible entries to current members only. However, I would
also like the combo box to display a prevoius entry regardless of
whether the member is current or not.

Thanks,

Schiz
 
Back
Top