Linking to Table from Command with Formula

[sardine53]

CR XI to an Access 2003 database.

I have a formula {@getgroup} - "stringvar array x:=split({CLIENT.NAME}," - ");x[1]"
which returns the result I need and matches the group.group field.


I tried to incorporate the formula in a Command:

select client.name, group.group, group.groupname
from
client
inner join group on
stringvar array x:=split({CLIENT.NAME}," - ");
x[1] = group.group

But received the following error:

Database Connector Error: 'DAO Error Code 0xc3b
Source DAO.Database
Description: Syntax error in FROM clause.'

What am I doing wrong?

Thanks in advance!

 

[lbass]

select client.`name`, group.`group`, group.`groupname`
from
client
inner join group on
{fn substring(CLIENT.`NAME`,1,{fn locate(' - ', CLIENT.`NAME`)}-1)} = group.`group`

You can try the above, but this will fail if the name doesn't contain a hyphen.

-LB

 

[sardine53]

It did fail because the majority of the values in Client.Name do not have hyphens.

Any other suggestions? (I can't use a supreport in this report as there are already subreports.)

Thanks,

 

[lbass]

This might be a long shot, but you could try something like:

select client.`name`, group.`group`, group.`groupname`
from
client
inner join group on
(
(
{fn locate(' - ', CLIENT.`NAME`)} = 0 and
CLIENT.`NAME` = group.`group`
) or
{fn substring(CLIENT.`NAME`,1, {fn locate(' - ', CLIENT.`NAME`)}-1)} = group.`group`
)

-LB

 

[sardine53]

Same error. I'm not optimistic since you've solved everything I've posted thus far

 

[lbass]

Okay, first go into a new report and see what functions are available to you in the SQL expression editor. If you see substring and locate, let me know.

If they are there, you could try this:

select client.`name`, group.`group`, group.`groupname`
from client, group
where
(
{fn locate(' - ', CLIENT.`NAME`)} = 0 and
CLIENT.`NAME` = group.`group`
) or
{fn substring(CLIENT.`NAME`, 1, {fn locate(' - ', CLIENT.`NAME`)}-1)} = group.`group`

...basically handling the join in the where clause.

-LB

 

[sardine53]

Substring is not an option under Functions. Should it be generally in Crystal XI?

I used a work-around instead of the table.

Thanks so much for all your effort.

 

[lbass]

I would think that ordinarily:

{fn substring(, ,)}

...would work, or:

substr(,,)

Sometimes functions that work are not shown in the function list.

-LB