I need help with an IF statement

393

256MB

If you're evaluating this in an access query, use a Switch() that will look like this

Switch([yourfield]=30,"Not Interested",[yourfield]=80,"Your text",[yourfield]=45,"Other Text",......)

If you're using VBA, use a Select Case that looks like this

Expand|Select|Wrap|Line Numbers

 
Public Function YourFx() as string

Dim x as Integer (or proper type)

x=[yourfield]
 
Select Case x

   Case 30

     YourFx="Not interested"

   Case 80

     YourFx="Other text"

   etc....

End Select

end function

The Switch can hold a lot, but it does cap at some point. I think the Select Case can be infinite

Aug 10 '10 #3

@ dsatino
Thank you everyone for the help this is what I have

SELECT CallHist.ResultCode, Count(CallHist.ResultCode) AS CountOfResultCode, CallHist.UserID, CallHist.QueueNo, Count(CallHist.QueueNo) AS CountOfQueueNo
FROM CallHist
WHERE (((CallHist.CallDT)>=[Enter Start date] And (CallHist.CallDT)<=[Enter End Date]))
GROUP BY CallHist.ResultCode, CallHist.UserID, CallHist.QueueNo
ORDER BY CallHist.ResultCode;

Where would I put the switch and how?

Aug 10 '10 #4

393

256MB

SELECT CallHist.ResultCode,Switch([callhist.resultcode]=...) as resulttext, Count(CallHist.ResultCode) AS CountOfResultCode, CallHist.UserID, CallHist.QueueNo, Count(CallHist.QueueNo) AS CountOfQueueNo
FROM CallHist
WHERE (((CallHist.CallDT)>=[Enter Start date] And (CallHist.CallDT)<=[Enter End Date]))
GROUP BY CallHist.ResultCode,Switch([callhist.resultcode]=...), CallHist.UserID, CallHist.QueueNo
ORDER BY CallHist.ResultCode;

Aug 10 '10 #5

@dsatino

You ROCK!!! It worked. Just what I was looking for. Thank you.

Aug 10 '10 #6

@dsatino

So I think it might be a problem with the amount of result codes. I am getting an error saying it is too complex.

This is what I have now.

SELECT CallHist.ResultCode,Switch([callhist.resultcode]=5,"Cancel After Card Declined",[callhist.resultcode]=6,"Card Declined Sent Admin",[callhist.resultcode]=7,"Spanish Speaker",[callhist.resultcode]=30,"Answering Machine",[callhist.resultcode]=31,"Abandoned by Agent",[callhist.resultcode]=45,"Not Now",[callhist.resultcode]=69,"Verified Number",[callhist.resultcode]=71,"No Contact",[callhist.resultcode]=72,"Wrong Number",[callhist.resultcode]=73,"Not Interested",[callhist.resultcode]=75,"Callback",[callhist.resultcode]=77,"Change Number",[callhist.resultcode]=80,"Sale",[callhist.resultcode]=85,"Sale New Customer",[callhist.resultcode]=982,"Automated Busy",[callhist.resultcode]=983,"Automated No Answer",[callhist.resultcode]=984,"Automated Telco",[callhist.resultcode]=988,"Automated Abandoned",[callhist.resultcode]=990,"Automated No Dial",[callhist.resultcode]=991,"Automated Fax",[callhist.resultcode]=993,"Automated Dropped Call",[callhist.resultcode]=994,"Automated Dial Failed") as resulttext, Count(CallHist.ResultCode) AS CountOfResultCode, CallHist.UserID, CallHist.QueueNo, Count(CallHist.QueueNo) AS CountOfQueueNo
FROM CallHist
WHERE (((CallHist.CallDT)>=[Enter Start date] And (CallHist.CallDT)<=[Enter End Date]))
GROUP BY CallHist.ResultCode,Switch([callhist.resultcode]=5,"Cancel After Card Declined",[callhist.resultcode]=6,"Card Declined Sent Admin",[callhist.resultcode]=7,"Spanish Speaker",[callhist.resultcode]=30,"Answering Machine",[callhist.resultcode]=31,"Abandoned by Agent",[callhist.resultcode]=45,"Not Now",[callhist.resultcode]=69,"Verified Number",[callhist.resultcode]=71,"No Contact",[callhist.resultcode]=72,"Wrong Number",[callhist.resultcode]=73,"Not Interested",[callhist.resultcode]=75,"Callback",[callhist.resultcode]=77,"Change Number",[callhist.resultcode]=80,"Sale",[callhist.resultcode]=85,"Sale New Customer",[callhist.resultcode]=982,"Automated Busy",[callhist.resultcode]=983,"Automated No Answer",[callhist.resultcode]=984,"Automated Telco",[callhist.resultcode]=988,"Automated Abandoned",[callhist.resultcode]=990,"Automated No Dial",[callhist.resultcode]=991,"Automated Fax",[callhist.resultcode]=993,"Automated Dropped Call",[callhist.resultcode]=994,"Automated Dial Failed"), CallHist.UserID, CallHist.QueueNo
ORDER BY CallHist.ResultCode;

I get a "Expression to complex in Query Expression"

Aug 10 '10 #7

393

256MB

yes, this has to do with the number of things a Switch function can evaluate. You simply have more than it can take. If you're comfortable with VBA, you should definitely move this to a Select Case function which I believe can handle infinite choices.

If you're not, than you could UNION two SELECT queries in which one of them handles certain result codes and the other handles the remaining ones.

Aug 10 '10 #8

@dsatino

I am not sure with the VBA but I would like to learn :) how would I union if I got stuck.

Also, is the Union going to make things perform extremely slow? Would the VBA perform faster?

Aug 10 '10 #9

393

256MB

Aug 10 '10 #10

@dsatino

I want to just say thank you very much for all your help on this. I have really one more question in regards to grouping all of this together in a sort of summary.

Should I do this sum? in a query or on the report in access?

Basically what I am trying to accomplish is this.

I want to see the following columns in an Access Report

UserID Queue5 Queue0 Queue1 AnsweringMachine Busy

I want the report to show UserID 2005 and then do a count of all the calls made in queue5 under that column and a count of all the calls in queue0 and so on. Currently it shows

UserID ResultCode QueueNo
2005 80 5
2005 80 0
2005 45 1

Aug 11 '10 #11

C# has live-as VB's With Statement?

393

256MB

I'm not sure this layout is what you're looking for. From the looks of it, a call can't be in multiple queues and can't have multiple results so your report will have 'staggered' (for lack of a better term) and duplicate results. They will probably look something like this:

UserID Q5 Q0 Q1 AnsM Busy
2005 1 1
2005 2 2
2005 3 3

In this example, the 1 under Q5 is the same as the 1 under Busy because you don't have counts for Q5 or counts for Busy, rather the counts are representative of a grouping of UserId, result, and queue combined.

Long story short, what you want to do is create a Crosstab query from the UNION query. Set UserID as row heading, resulttext as a row heading, queueno as a column heading, and your count as the value. Then build your report from this crosstab query and group the report by userid with the header and footer sections turned on. The report will look like this.

2005 Q1 Q2 Q3 etc.

Busy 4 2 1
AnsMac 2 0 1
etc.

The Q headers in this example are in the UserID header, but you can put them in the page header as well. You can also sum the columns in the footer, I was just too lazy to type that out. But you shoud get the gist of it

Aug 11 '10 #12

Similar topics

by: ABC | last post by:

Which statement on the C# has live-as VB's "With" Statement?

Any Equivalent to VB.NET's With...End With Statement ...

With Statement

by: doodle | last post by:

access 97 can someone tell me the syntax for a with stement for multiple controls? pseudo (doesn't work) With cmbBox1,cmbBox2,cmbBox3 .Visible = False End With

Microsoft Access / VBA

by: Ashok Guduru | last post by:

Hi, I'm new to C#. In VB.NET we have With...End With Statement. Often, I need to perform several different actions on the same object. For example, I may need to set several properties or...

with statement

by: Gunawan | last post by:

Hi All, When using vb I could use with statement to make access to property/method shorter Dim cmd AS new SqlCommand with cmd.parameters .AddWithValue() .... end with

'with' statement

by: =?Utf-8?B?RE9UTkVUR1VZ?= | last post by:

Hello, In vb.net there is a with statement, Is there are similar constructor in c#?

with statement in C-sharp?

by: Nhan | last post by:

Hi, is there equivalent statement in C# as WITH ... END in VB? instead of: frm.dataGridView1.AutoGenerateColumns = true; frm.dataGridView1.DataSource = l_oDataset;...

Need help with "with-statement-compatible" object

by: Dmitry Teslenko | last post by:

Hello! I've made some class that can be used with "with statement". It looks this way: class chdir_to_file( object ): .... def __enter__(self): .... def __exit__(self, type, val, tb): ....

getting with statement to deal with various exceptions

by: mk | last post by:

Hello, I'm trying to learn how with statement can be used to avoid writing: prepare() try: something_that_can_raise_SomeException() except SomeException, err: deal_with_SomeException...

with statement for two files

by: braver | last post by:

Can open two files in a with statement: with open(src) as readin, open(dst,"w") as writin: # WRONG: comma doesn't work ... -- so that you have transactional safety for two file descriptors?...

Using the 'with' statement with cStringIO objects

by: peppergrower | last post by:

I've been experimenting with the 'with' statement (in __future__), and so far I like it. However, I can't get it to work with a cStringIO object. Here's a minimum working example: ### from...