MS SQL with Crystal and Access Text fields

[Zbob]

We just upgraded to MS Sql, when using crystal and access to evaluate text fields such as Credit hold and User Defined Fields, it does not work.

Example: SELECT OEORDHDR_SQL.hold_fg, OEORDHDR_SQL.ord_no
FROM OEORDHDR_SQL
WHERE (((OEORDHDR_SQL.hold_fg)<>"H"))
WITH OWNERACCESS OPTION;

This returns no records.

Thanks,

Bob Zaback

 

[kscheeler]

The data in SQL is held differently than in Btrieve.

I believe the hold_fg field is either an H or an empty field. In SQL, an empty field will be NULL. In the where clause, you have to specify that you are looking for a field to be either null or not.

Try this:

SELECT OEORDHDR_SQL.hold_fg, OEORDHDR_SQL.ord_no
FROM OEORDHDR_SQL
WHERE OEORDHDR_SQL.hold_fg OEORDHDR_SQL.hold_fg is null
WITH OWNERACCESS OPTION;

Kevin Scheeler

 

[Zbob]

What do you do if it can have multiple values frt pay cd in order header history can be "C" "P" or null?

 

[kscheeler]

It would depend on what you are looking for but if you want everything that has a value I would do the following:

SELECT OEORDHDR_SQL.frt_pay_cd, OEORDHDR_SQL.ord_no
FROM OEORDHDR_SQL
WHERE OEORDHDR_SQL.frt_pay_cd is not null

Another way would be to use OR and have multiple selections.

SELECT OEORDHDR_SQL.frt_pay_cd, OEORDHDR_SQL.ord_no
FROM OEORDHDR_SQL
WHERE OEORDHDR_SQL.frt_pay_cd = 'C' or OEORDHDR_SQL.frt_pay_cd = 'P'

Kevin Scheeler

 

[abaldwin]

Forget the OR Statement. Use IN

SELECT frt_pay_cd, ord_no
FROM OEORDHDR_SQL
WHERE (frt_pay_cd IN ('C', 'P'))

Less typing

Andy "Do it with less characters" Baldwin

Andy Baldwin