Case statement

Hi

I am having a few problem with a case statement.

I have a table with customer data in and I need to bring back all the rows
which have data missing from 2 different columns.

customer table
CustomerID Name SoftwareVersion
1001 TEST1 V5
1002 TEST2 V5
3 TEST3 V5
004 TEST4 V6
ect..

CustomerID Table
ID
1001
1002
1003
1004
ect...

I have a need customerid table and I need to list all the customers who are
not in this table or whos id does not match the new listings,
alos there is a softwareVersion Table list all the newest software so the same
will need to be done for this.

Thanks

Rich

You need to create the join batween the two table with CustomerID & ID, with not equal operator, yuor query may look like this

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

Gurpreet S. Gill

|||

Hi

I would like to use the case statement because where there is an error, I would like the cell to display

ERROR: 'then customerid'

Thanks

Rich

|||

I dont get you, what you want to do ? tell me in more detail.

|||

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

This won't work, you're just going to get every Customer joined to every customer except themselves.

You need to use an outer join something like this

SELECT A.CustomerID
FROM Customer A
LEFT JOIN CustomerID B ON A.CustomerID = B.[ID]
WHERE B.ID IS NULL

|||I assume you mean that the CustomerID table in your example above is really the result of a query you want to write. The problem is you haven't specified what "all the customers who are not in this table or whos id does not match the new listings" means. How do you know if a customer is not in that table or if they do not match the new listings, is that info in another table?|||

yes SnMSDN

he didnt make much clear what he wants, that why i did post that sample code. But he not responding, might he had solved the problem

sql

Carriage Returns and Line Breaks

I have an address field that is coming back as a single field with carriage returns and line breaks and I would like to have it properly wrap in a single box, but the wrapping is all off. How can I get this to properly break? ThanksMake sure you have text box property "cangrow" to true.|||

Nope. That is not the issue. It is cangrow = true.

What I want is to take a field that comes out as:

John Smith CR LB 123 Main Street CR LB Anytown, MA 01888 CR LB USA

as

John Smith

123 Main Street

Anytown, MA 01888

USA

What I am getting is:

John Smith 123

Main Street Anytown,

MA 01888 USA

So I am looking how to read the CR and LBs and maybe replace them with BR tags, not sure.

|||Also... what is being returned to indicate the line break are char(13)s. I am recreating a report that I had lost and was able to resolve this at one time, but forget how I got around it.|||

try select field1 + char(13) + char(10) + field2

Cant unlock records in SQL Server

I have an Access97 front end using ODBC to communicate with an
SQLServer 7.0 back end on a different machine. Most of the work I do
in the front end uses forms bound to linked tables that reside on the
back end. In one instance though I have to create some new records
programmatically and I use code in a procedure in a general module in
the front end that looks like this:

Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]",
dbOpenDynaset, dbAppendOnly + dbSeeChanges)
newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Assigned] = True
newrec.Update
newrec.Close

The problem occurs later when I am in a form that views those records
that were just added. For some reason SQLServer still has those
records locked, and I am not allowed to make any changes to them. I
can't even just delete them. In fact, even when I exit out of all my
forms, go straight into the table window, straight to the table itself
(but still in the front end), I cannot delete or change those records
directly. I've tried taking down the front end and bringing it back
up. I've tried restarting the whole computer where the front end
resides. I've also tried restarting the SQL Server. I still can't
change those records. Oddly enough, I can change the records within
SQL Server itself. The Access97 front end will see the new values, but
still is unable to do anything with them. How can I fix this problem?
Thank you for any help you can give me,
Rebecca JaxonOK, we've solved it. Sorry to bother you all. The problem was that I
had some other boolean fields I was just ignoring. It used to work
fine when the back end was still in Access97. Apparently, when
Access97 sees a null in a boolean field it just assumes that it means
false, but when SQL Server sees such a thing, it causes this problem.
I was not filling in all the false values, and hadn't set the default
to false. Now I know.

Rebecca