Don't know why there is an Incompatibility of type here

[Guy Verville]

Hello,

I have a table (music scores) that contains these fields: S, A, T and B. In
these fields are integers that tell how many part there is of each voice for
a given score (e.g. SATB, SA, SSATTBB, etc.)

In a query, I'ved figured a concatenation like this (my Access is in French.
Chaîne$ is for Str$, I suppose:
SATB: Chaîne$(;"S") & Chaîne$([A];"A") & Chaîne$([T];"T") &
Chaîne$(;"B"), which gives me the correct formula.

I must say that a lot of data has no entrie for these fields (hence, the
SATB field is Null from time to time).

If I want to place, for example, the criteria "SA", I get an error that
there is an incompatibility in the criteria type. I'm lost. The SATB field
doesn't return a string?

The is Access database is for the web.

Guy

 

[Brendan Reynolds]

It looks like the function you are using is the function known in English as
String(), not Str().

The empty (Null) fields are the problem, as a Null value is not a legal
argument for the String() function.

If the query was to be executed within Access, you could use the NZ (Null to
Zero) function, but as it is to be used in a Web application, you'll need
the IIf (Immediate If) function. Here's an example ...

SELECT IIf([Num1] Is Null,"",String$([Num1],"X")) AS Expr1
FROM Table3;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guy Verville]

Thanks for answering swiftly. I'm not sure I understand pretty well the IIf
function. Say I want to search from the web the SATB scores. Should I use
the SATB field I created in my Access database or should I create it from
ASP?

Guy

It looks like the function you are using is the function known in English
as String(), not Str().

The empty (Null) fields are the problem, as a Null value is not a legal
argument for the String() function.

If the query was to be executed within Access, you could use the NZ (Null
to Zero) function, but as it is to be used in a Web application, you'll
need the IIf (Immediate If) function. Here's an example ...

SELECT IIf([Num1] Is Null,"",String$([Num1],"X")) AS Expr1
FROM Table3;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hello,

I have a table (music scores) that contains these fields: S, A, T and B.
In these fields are integers that tell how many part there is of each
voice for a given score (e.g. SATB, SA, SSATTBB, etc.)

In a query, I'ved figured a concatenation like this (my Access is in
French. Chaîne$ is for Str$, I suppose:
SATB: Chaîne$(;"S") & Chaîne$([A];"A") & Chaîne$([T];"T") &
Chaîne$(;"B"), which gives me the correct formula.

I must say that a lot of data has no entrie for these fields (hence, the
SATB field is Null from time to time).

If I want to place, for example, the criteria "SA", I get an error that
there is an incompatibility in the criteria type. I'm lost. The SATB
field doesn't return a string?

The is Access database is for the web.

Guy

 

[Guy Verville]

Ok, I've understood it. It was in my B field that there was a empty entry...
I've placed a default "0" value to be sure there is no such entry. Thanks
for your help!

Guy

It looks like the function you are using is the function known in English
as String(), not Str().

The empty (Null) fields are the problem, as a Null value is not a legal
argument for the String() function.

If the query was to be executed within Access, you could use the NZ (Null
to Zero) function, but as it is to be used in a Web application, you'll
need the IIf (Immediate If) function. Here's an example ...

SELECT IIf([Num1] Is Null,"",String$([Num1],"X")) AS Expr1
FROM Table3;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hello,

I have a table (music scores) that contains these fields: S, A, T and B.
In these fields are integers that tell how many part there is of each
voice for a given score (e.g. SATB, SA, SSATTBB, etc.)

In a query, I'ved figured a concatenation like this (my Access is in
French. Chaîne$ is for Str$, I suppose:
SATB: Chaîne$(;"S") & Chaîne$([A];"A") & Chaîne$([T];"T") &
Chaîne$(;"B"), which gives me the correct formula.

I must say that a lot of data has no entrie for these fields (hence, the
SATB field is Null from time to time).

If I want to place, for example, the criteria "SA", I get an error that
there is an incompatibility in the criteria type. I'm lost. The SATB
field doesn't return a string?

The is Access database is for the web.

Guy