Formatting Yes/No fields in DRW

Derek MacBeth

unread,

Apr 25, 2001, 6:16:38 PM4/25/01

to

Hi all.

Awhile back, there was a message telling how to format True/False fields in
FP2k as checkboxes. It pointed to
http://www.outfront.net/spooky/advanced.htm#custom, which said to edit the
fbdblib.inc fiile in your web. I need a similar solution, but I need to
have it display Yes/No instead of the default True/False. I'm using the DRW
to create my table. What's the easiest way to do this? Thanks in advance
for any advice.

Derek

Jon Spivey

unread,

Apr 25, 2001, 6:29:29 PM4/25/01

to

i think the easiest is to write in your custom query
SELECT iif(YesNoField = true, 'yes', 'no'), field1, field2
FROM yourtable

this way you get the Yes or No straight from the database and dont have to
edit anything

Jon

"Derek MacBeth" <sup...@spamthis.maydaycomputing.com> wrote in message
news:OD8SlWdzAHA.1396@tkmsftngp05...

Thomas A. Rowe

unread,

Apr 25, 2001, 6:30:49 PM4/25/01

to

I don't use the DRW, but you might get the following to work:

<%
Dim Checked
Checked = Rs("fieldname")
If Checked = 0 then
Checked1 = "No"
Else
Checked1 = "Yes"
End If
%>

Display on page as:

<%=Checked1%>

Note: 1 = True and 0 = False

=====================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)
http://www.ycoln-resources.com
FrontPage Resources, Email Discussion List,
Newsletter, WebRing, MS KB Quick Links, etc.

"Derek MacBeth" <sup...@spamthis.maydaycomputing.com> wrote in message
news:OD8SlWdzAHA.1396@tkmsftngp05...

Jon Spivey

unread,

Apr 25, 2001, 6:57:26 PM4/25/01

to

Missed a part from this query should be -
SELECT iif(YesNoField = true, 'yes', 'no') AS YesNo, field1, field2
FROM yourtable

"Jon Spivey" <j...@genasis.net> wrote in message
news:eWIjYbdzAHA.1616@tkmsftngp05...

Derek MacBeth

unread,

Apr 25, 2001, 8:07:22 PM4/25/01

to

Your idea looks sound, but it looks like the ODBC driver has a limit on the
size of queries. It seems to stop at about 345 characters, and the database
in question has some fairly long field names, and there arre 24 of them.
Any other suggestions? Thanks again for your help.

Derek

"Jon Spivey" <j...@genasis.net> wrote in message

news:ulSe$qdzAHA.1456@tkmsftngp02...

Jon Spivey

unread,

Apr 25, 2001, 9:13:57 PM4/25/01

to

this is sloppy but using * would keep the length of the query down. I'd
never heard of queries having a maximum length
select *, iif(YesNoField = true, 'yes', 'no') AS YesNoField from yourtable

Jon

"Derek MacBeth" <sup...@spamthis.maydaycomputing.com> wrote in message

news:uAQndUezAHA.720@tkmsftngp03...

Derek MacBeth

unread,

Apr 25, 2001, 10:34:03 PM4/25/01

to

Thanks Jon.

I'll give that a shot. Here's the query I was using. Maybe it'll shine
some light on the limit thing. It seems to die after about 345 characters.

SELECT BroadClassification, Classification, NarrowClassification, Date,
TypeofPublication, [PrimaryAuthor Last Name], PrimaryAuthorFirstName, Title,
PeriodicalorProceedingsTitle, Publisher, Volume, Number, Pages,
Organization, OrganizationAddress, OrganizationCity,
OrganizationStateorProvince, OrganizationZipCode, Country,
[WeborE-MailAddress], Photgraph, iif(FieldImplementation = True, 'Yes',
'No') AS FieldImpliementation, iif(AnalyticalStudy = True, 'Yes', 'No') AS
AnalyticalStudy, iif(LabStudy = True, 'Yes', 'No') AS LabStudy
FROM sect227MasterTable WHERE (BroadClassification LIKE
'::BroadClassification::' AND Classification LIKE '::Classification::' AND
NarrowClassification LIKE '::NarrowClassification::' AND TypeofPublication =
'::TypeofPublication::');

When I try to verify this, I get the following error message.

Server error: Unable to retrieve schema information from the query:

Then it displays my query up to the end of the WeborE-MailAddress field, and
the following error.

[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias
'AnalyticalStudy' in query definition's SELECT list.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

This is why I think there's a limitation on the query length. The database
is not my own, and I wouldn't have named the fields that way myself, but I
already had to get the client to change one field name (had a ? in it) and
their copy of Access didn't keep referential integrity in their 100+
queries. I'll try the *, but if you can think of a less sloppy way to do
this, I'd love to hear it. Thanks again.

Derek
"Jon Spivey" <j...@genasis.net> wrote in message

news:#9tRS3ezAHA.2348@tkmsftngp02...

Jon Spivey

unread,

Apr 26, 2001, 5:00:57 AM4/26/01

to

As far as I can see this error

Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias
'AnalyticalStudy' in query definition's SELECT list.

Is caused by giving fields the same name and alias, you cant do this, so
rather than
iif(AnalyticalStudy = True, 'Yes', 'No') AS AnalyticalStudy try
iif(AnalyticalStudy = True, 'Yes', 'No') AS StudyYesNo
Youll need to correct the other true/false fields too. Im sure that queries
not too long, if you can get the syntax right it'll work

Jon

"Derek MacBeth" <sup...@spamthis.maydaycomputing.com> wrote in message

news:eE$MbmfzAHA.1800@tkmsftngp02...

Derek MacBeth

unread,

Apr 26, 2001, 9:01:56 AM4/26/01

to

Thanks Jon.

I realised the error in my ways when I used the Select I version of the
query, and got the real error message. I should have known, but I was being
lazy! Thanks again. Your replacement query works perfectly!

Derek

"Jon Spivey" <j...@genasis.net> wrote in message

news:eJJ4O8izAHA.2108@tkmsftngp02...