Case or IIF?

Works!! Thank You!
"Bob Barrows [MVP]" wrote in message
...
Change it to this:

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst

NBullock wrote:
I have tried this, but it does not return a vaule for Department

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') AS Department
FROM Smdprtlst;





"NBullock" wrote in message
...
Might there be a better way? I am trying to pull text from a field
in one table and then assign those records to that result. For
example, if the printer name contains "gen" it is a member of the
"Medical" department or if it contains "acct" then it is a member of
the Accounting
department - and so on.

I do not know of any other way to make this comparison other than
maybe a case statement, but I don't know the syntax for that in SQL.

Can anyone help?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

It is possible that you aren't using the correct set of wildcards. Try using
* in place of %.

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*') AS Department
FROM Smdprtlst;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NBullock wrote:
Works!! Thank You!
"Bob Barrows [MVP]" wrote in message
...
Change it to this:

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst

NBullock wrote:
I have tried this, but it does not return a vaule for Department

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '%' & PrinterCode & '%') AS Department
FROM Smdprtlst;





"NBullock" wrote in message
...
Might there be a better way? I am trying to pull text from a field
in one table and then assign those records to that result. For
example, if the printer name contains "gen" it is a member of the
"Medical" department or if it contains "acct" then it is a member of
the Accounting
department - and so on.

I do not know of any other way to make this comparison other than
maybe a case statement, but I don't know the syntax for that in SQL.

Can anyone help?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

This worked fine, Thanks Bob.

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst




"Bob Barrows [MVP]" wrote in message
...
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Oops, I do have a problem:

ACCT = Accounting
CC = Cancer Treatment Center

ACCT comes up as Cancer Treatment Center



SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst




"Bob Barrows [MVP]" wrote in message
...
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*') AS Department
FROM Smdprtlst;

Works that same way



"NBullock" wrote in message
...
Oops, I do have a problem:

ACCT = Accounting
CC = Cancer Treatment Center

ACCT comes up as Cancer Treatment Center



SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst




"Bob Barrows [MVP]" wrote in message
...
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Yes, you have a problem. A human is going to need to be involved in
assigning the departments to these printers ...
unless ... Is the printername exactly "ACCT"? If so, you could do this:

SELECT PrinterName, Nz(
(select Max(DepartmentName) From DepartmentCodes
where PrinterCode =PrinterName)
,(select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*')
) AS Department
FROM Smdprtlst;

But if there's a third match to this pattern, all bets are off. You might as
well add a Department field to the Smdprtlst table and tell someone to fill
it in.


NBullock wrote:
SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*') AS Department
FROM Smdprtlst;

Works that same way



"NBullock" wrote in message
...
Oops, I do have a problem:

ACCT = Accounting
CC = Cancer Treatment Center

ACCT comes up as Cancer Treatment Center



SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst




"Bob Barrows [MVP]" wrote in message
...
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Yikes.
The printer names always start with "I" followed by the department code,
driver and tray indicator . i.e. IACCTAT1 or ICC1AT1. The problem is that
the codes vary in length.

But, you last bit of code get most of them, so manula editing my not be a
big deal.

"Bob Barrows [MVP]" wrote in message
...
Yes, you have a problem. A human is going to need to be involved in
assigning the departments to these printers ...
unless ... Is the printername exactly "ACCT"? If so, you could do this:

SELECT PrinterName, Nz(
(select Max(DepartmentName) From DepartmentCodes
where PrinterCode =PrinterName)
,(select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*')
) AS Department
FROM Smdprtlst;

But if there's a third match to this pattern, all bets are off. You might
as well add a Department field to the Smdprtlst table and tell someone to
fill it in.


NBullock wrote:
SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*') AS Department
FROM Smdprtlst;

Works that same way



"NBullock" wrote in message
...
Oops, I do have a problem:

ACCT = Accounting
CC = Cancer Treatment Center

ACCT comes up as Cancer Treatment Center



SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst




"Bob Barrows [MVP]" wrote in message
...
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

This seems to work best. By putting "I" (what they all begin with) so, as
long as my codes don't start out the same ...

Thanks for all your help.

SELECT PrinterName, Nz(
(select Max(DepartmentName) From DepartmentCodes
where PrinterCode =PrinterName)
,(select Max(DepartmentName) From DepartmentCodes
where PrinterName like "I" & PrinterCode & '*')
) AS Department
FROM Smdprtlst;

"NBullock" wrote in message
...
Yikes.
The printer names always start with "I" followed by the department code,
driver and tray indicator . i.e. IACCTAT1 or ICC1AT1. The problem is that
the codes vary in length.

But, you last bit of code get most of them, so manula editing my not be a
big deal.

"Bob Barrows [MVP]" wrote in message
...
Yes, you have a problem. A human is going to need to be involved in
assigning the departments to these printers ...
unless ... Is the printername exactly "ACCT"? If so, you could do this:

SELECT PrinterName, Nz(
(select Max(DepartmentName) From DepartmentCodes
where PrinterCode =PrinterName)
,(select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*')
) AS Department
FROM Smdprtlst;

But if there's a third match to this pattern, all bets are off. You might
as well add a Department field to the Smdprtlst table and tell someone to
fill it in.


NBullock wrote:
SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where PrinterName like '*' & PrinterCode & '*') AS Department
FROM Smdprtlst;

Works that same way



"NBullock" wrote in message
...
Oops, I do have a problem:

ACCT = Accounting
CC = Cancer Treatment Center

ACCT comes up as Cancer Treatment Center



SELECT PrinterName, (select Max(DepartmentName) From DepartmentCodes
where Instr(PrinterName,PrinterCode)0) AS Department
FROM Smdprtlst




"Bob Barrows [MVP]" wrote in message
...
John Spencer wrote:
It is possible that you aren't using the correct set of wildcards.

It's more than possible ... whap to the side of my head


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"