Converting ELSEIF Statements To SELECTCASE

Jan 24, 2005

Heelo all:

Code:

Private Sub GPA_AfterUpdate()
If Division = "GRADUATE" Then
Honors = ""
ElseIf Division = "UNDERGRADUATE" And GPA < 3.2 Then
Honors = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
Honors = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
Honors = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
Honors = "SUMMA CUM LAUDE"
End If
If Division = "GRADUATE" And School = "SCHOOL OF EDUCATION" And GPA >= 3.7 Then
Honors = "HONORS"
MsgBox "PLEASE MAKE SURE STUDENT HAS ONLY A'S AND B'S ON TRANSCRIPT!"
End If
End Sub

Does anyone know hoe to convert the above statements into using SELECTCASE statements?

Any help is most welcome.

Thanks in advance,

Dee

View Replies


ADVERTISEMENT

If/ElseIF/Else ?

Nov 6, 2005

I have been using the following in AfterUpdate event of "MyField1". This has worked ok enabling and disabling one field which in this case is named Myfield2. Now I want to enable and disable multiple fields based on text content of "MyField1" and am not sure what to do. MyField1 is a combo. I've searched about but can't find anything to point me in the right direction. Any suggestions?

If Me.Myfield1 = "mytext" Then
Me.Myfield2.Enabled = True
ElseIf Me.Myfield1 = "someothertext" Then
Me.myfield2.Enabled = True
Else
Me.myfield2.Enabled = False
End If
End Sub

View 5 Replies View Related

If / Elseif

Nov 4, 2004

Can someone please look at my code and help me understand why it isn't working?

Code:Private Sub Text113_Enter()If [current retailer] = "One" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([one] - [total]) < 0 Then meter_type = "B Meter"ElseIf [current retailer] = "One" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([one] - [total]) >= 0 Then meter_type = "5 Meter"ElseIf [current retailer] = "Two" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([two] - [total]) < 0 Then meter_type = "B Meter"ElseIf [current retailer] = "Two" And ([peakusage] + [offpeakusage] + [controlledloadusage]) > 40000 And ([two] - [total]) >= 0 Then meter_type = "5 Meter"ElseIf [peakusage] + [offpeakusage] + [controlledloadusage] <= 40000 Then meter_type = "B Meter"End IfEnd Sub

Everytime I try to run this code to store it in my field(meter_type), I get the "Else without If" error, but I can't see where I'm going wrong! Anyone??

View 8 Replies View Related

Elseif Type Function In Query

May 31, 2006

i have the following query statement:

intext: IIf([calllogs]![InternalCall],"0","External")

in "InternalCall" there is either a "0" or a "1", these represent external and internal calls

So I want to turn the "1" and "0" into "internal" and "external"

I've done half of it, but I can't use an "else if" statement in the query, the only other option is to have one field for internal and one box for external with seperate iif statements which I want to stay away from...

View 3 Replies View Related

I Need Help With Using IIF Statements

May 16, 2007

Can anyone give me any guidance on how to use IFF statements in microsoft access? or Has anyone got a link to a good website that can help me? I need to use the formula below to determine if the quantity of an order from a store is greater than the quantity that is in stock at the warehouse and produce a message message.IIF([quantityordered])(=[quantityinstock]"enoughstock","notenoughstock") I just dont know where to start, i didnt even know you could enter formulas in Microsoft Access so i really need help

View 2 Replies View Related

If Statements

Mar 8, 2007

Hi, I have another problem with my Access database now...

I am trying to disable fields when a certain value is chosen. I can do it for one field with this code:

If ALLOCATION_LP = "Other" Then
OTHER_ALLOCATION_LP.Enabled = True
Else
OTHER_ALLOCATION_LP.Enabled = False
End If

That one works perfectly well, but I have another one that needs to disable two fields instead. If they choose "No Extant PP", I want it to disable two fields; one combo box - PLNG_PERMISSION_STATUS_2 (which I can get to and it works) and also an "Other" text box - OTHER_PLNG_PERMISSION_STATUS_2 (this is where i get stuck). I have tried two If statements, but whether I'm writing them wrong, I'm not sure.

I would really appreciate the help. Thanks.

View 2 Replies View Related

Two Where Statements

Feb 5, 2008

'construct a where clause as required
If Nz(Me.Manufacturer.Text, "") <> "" Then
Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
End If

Me.list.RowSource = _
"SELECT ID, Description, Par, MaxCoins, PayLines " & _
"FROM MachineTypeQuery " & _
Where1, Where2 & _ <- how do i get something like this to work
"ORDER BY Description;"


Exit Sub
Where2 comes from another control and has to be filled in before they get to this control. also "where1" and "where2" are set as public stings

View 6 Replies View Related

And Or Statements

Feb 25, 2005

I want to check eight Yes/No fields. If none of the boxes are checked I want to display a msgbox. I can link seven of the fields (no problem), when I add the eighth it doesn't work.

Am I limited to seven fields in my statement? Is there a way around this?

Any help would be appreciated.

============

If Me.Printer = 0 And Me.Hardware = 0 And Me.Software = 0 And Me.MSOffice = 0 And Me.Network = 0 And Me.NewComputer = 0 And Me.Profile = 0 And Me.Other = 0 Then

MsgBox "You must enter the type of problem(s) addressed with this customer", vbQuestion, "Type of Problem(s)"

Else
blah blah
End if

View 2 Replies View Related

SQL Statements

Nov 5, 2004

I want to select all records where a specifc field data start with specific data.

I'm having a meltdown because I know if is easy. If have tried Like "*" but that doesn't work. What is the command for Starts with?

View 4 Replies View Related

IF Statements

Feb 28, 2007

My database has a field named "Note". This field will either have a "Y" or "N" in it. At the bottom of a report I'm writing, I want to count the number of "Y's" (not the "N's") at the bottom of my report. Can someone tell me how to do this? Thanks so much in advance!

View 1 Replies View Related

Iif Statements

Apr 2, 2007

hey all

i have a list of values that i'm trying to run through an iif, like so:

IIf(([Warehouse Location].[ToAISLE] Like "1" Or "2" Or "3" Or "4" Or "5" Or "6" Or "7" Or "8" Or "9" Or "10"),([Product Inventory with totals].[Available]+[Warehouse Location].[Qty]))

im doing that in a query, not the vb code. now heres my problem: that's not the correct syntax for the Like blah blah blah part. does anybody know the correct syntax for this?

thanks!

View 2 Replies View Related

IF THEN Statements

Jun 5, 2007

Within my database I have 5 fields that will have either "Yes" or "No" data. I want to be able to have one of these fields look at the 4 others and if they meet a condition have the background of the field change colors.Here's a breakdown of what I'm trying to do:

Field1 Field2 Field3 Field4 Field5

Yes Yes Yes Yes No
No No No No Yes

If Field5 has a "Yes" and Fields 1,2,3,4 are all "No" then I want Field 5 background to change color to indicate that a criteria was met.

Is there a way to do this? Thanks for any help.

View 1 Replies View Related

Exporting A Set Of SQL Statements

Mar 7, 2007

Hi.
I need to export a set of sql statements from my Microsoft Access database in order to put it into my new mySql server. I know that its possible to get the statements off the mysql database, but i cant figure out how to do it in microsoft access. Any help is appreciated

Thanks in advanced,
Teh mockers

View 1 Replies View Related

Select Statements

Mar 19, 2007

Hi,

I hope I posted this in the right forum. It didn't seem specific enough for the other ones.

I have a select statement in a form that reads as follows:


Select [Port of Origin] from [Port of Origin]

It works great. It does exactly what I want it to do.

I want to add another field to the statement but it is from a differnet table. If it was to stand alone it would read as follows:

Select [VendorName] from Container_Vendor_Information

How would I combine these two statements? I have tried various things can't get it to work. Any thoughts?

Thanks so much in advance.

Eddie.

View 2 Replies View Related

Combining 2 SQL Statements

Jun 1, 2005

Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.

I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.

SQL Statements


FIRST QUERY

SELECT tblApp.Time
FROM tblApp
WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));


SECOND QUERY

SELECT tblTime.Time
FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time
WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));


Rixx

View 4 Replies View Related

Operators In IIF Statements

Sep 7, 2005

Is it possible to have operators (<, >, <=, >=) in an IIF statement like this:

iif([field a] is null, > [field b], < [field c])

This is in the criteria for a currency field.

I have been trying different combinations without any luck for the last couple of hours or so. :confused: :confused:

Any help would be greatly appreciated.

Thanks in advance,
Brian

View 4 Replies View Related

IIF Statements With Wildcards

Sep 14, 2005

I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.


SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number,
[AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry,
dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number,
Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code,
vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2

FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number)
LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number)
LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory

WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))

GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType,
dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number,
vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0

HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel]))
AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number]))
AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType]))
AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));

View 2 Replies View Related

Multiple IIF Statements

Nov 16, 2006

I'm trying to compile a number of IIF statements.

I have 6 IIF statements, but would like to put them all in to one IIF statement.

Could anyone help me with this one?

OrderTrack: IIf([O_SRC_CD]="2005WMDT","Web","Phone")
OrderTrack: IIf([O_SRC_CD]="2005WMKE","Internet","Phone")
OrderTrack: IIf([O_SRC_CD]="DOGNET23","Web","Fax")
OrderTrack: IIf([O_SRC_CD]="CANTRY3J","Web","")
OrderTrack: IIf([O_SRC_CD]="MAMTWOSE","Web",",")
OrderTrack: IIf([O_SRC_CD]="2005W999","Web","Mail")

Thank you for your help and time

Corey

View 2 Replies View Related

Disappearing SQL Statements

Feb 28, 2008

Hi All,

I have a large database with about 200 queries. I am having a problem with the last few I added. I run them from VB. When I run them the SQL statement disappears, sometimes. If I go to the SQL View instead of the Design veiw all that is left is Select;. I the Design View there is nothing.

Any suggestions?

View 3 Replies View Related

Iif Statements... Then The WHERE Clause? Help!

Apr 16, 2008

Hi,
here is my query which runs within the VB code...

queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No],"
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name],"
queryString2 = queryString2 & " part.id AS Part,"
queryString2 = queryString2 & " price_factor.use_fixed_price AS [Use_fixed],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.price,price_list.price) AS [Base Price],"
queryString2 = queryString2 & " iif(Use_fixed =1,price_factor.fixed_price_factor,price_factor.fa ctor) AS [Factor],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.min_quantity,price_list.min_qu antity) AS [Price Break],"
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required],"
queryString2 = queryString2 & " part.unit_id As [Unit],"
queryString2 = queryString2 & " iif(Use_fixed =1"
queryString2 = queryString2 & " ,(price_factor.fixed_price_factor*(fixed_price_lis t.price*FixedCurrencyConUKP.sterling_exchange_rate ))"
queryString2 = queryString2 & " ,(price_factor.factor *(price_list.price*CurrencyConUKP.sterling_exchang e_rate))) AS Price"
queryString2 = queryString2 & " FROM part_select, part, fixed_price_list, price_list, price_factor, Custom2, CurrencyConUKP, FixedCurrencyConUKP"
queryString2 = queryString2 & " WHERE part.id = part_select.part"
queryString2 = queryString2 & " AND price_list.part_id = part.id"
queryString2 = queryString2 & " AND fixed_price_list.part_id = part.id"
queryString2 = queryString2 & " AND Custom2.JDE_Price_Group=price_factor.customer_grou p"
queryString2 = queryString2 & " AND price_factor.part_group=part." & PriceGroup
queryString2 = queryString2 & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'"
queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND part.id = " & "'" & select_part_rst!part & "'"
queryString2 = queryString2 & " AND part_select.quantity = " & select_part_rst!quantity
queryString2 = queryString2 & " ;"

It all works pretty much fine, however, the problem occurs because the iif statement tells the code to use either the fixed_price_list table or the price_list table depending on the value of Use_fixed. the iif works fine, its the WHERE clause. because the iif statements tell the query to look at one table or the other, the WHERE clause includes statements from both tables e.g.

queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "

but some parts dont have information within the fixed_price_list table, and thus are not being found in the results due to the WHERE clause. can anyone help?? i tried using a left join but it didnt work how i wanted. in a perfect world i would be able to include a iif statement in the where clause e.g.

queryString2 = queryString2 & " iif Use_fixed =1, AND price_list.min_quantity <= part_select.quantity, AND fixed_price_list.min_quantity <= part_select.quantity) "

can anyone help?

View 5 Replies View Related

One Event Two IF Statements

May 13, 2006

This should be simple! I'm looking at something wrong. I have two If-Else statements on the "onCurrent event of a form. ONly the first one fires. Is there something rong with the syntax???

Private Sub Form_Current()

If Me.OptInterviewed.Value = -1 Then
Me.InterviewDate.Visible = True
Else
Me.InterviewDate.Visible = False

If Me.OrientationDate.Value = -1 Then
Me.OrientationDate.Visible = True
Else
Me.OrientationDate.Visible = False

End If
End If

End Sub

View 10 Replies View Related

Multiple If Statements, Best Way ?

Dec 15, 2004

I have a query which works fine for one if statement, but i neewd to have 10, whats the best way of sorting this, can it be done in one query, or should i use a module or should i use the code?

my if statements are as follows:

IIf([eventcode]="n204",[totaltime]-[totalcipstd],0)
IIf([eventcode]="n301",[totaltime]-[totalPaststd],0)
IIf([eventcode]="X117",[totaltime]-[totalMaintstd],0)
etc....

can someone please help me sort this...

a problem may be that the list of eventcodes may increase... again whats the best way to sort this.

Andy

View 1 Replies View Related

Stringing IIf Statements

Dec 23, 2004

Hello all, season's greetings!

Basically I'm trying to write a query that has 2 IIF statements. The process is pretty straight forward but I'm getting weird errors. What's supposed to happen is it creates a CDN List Price from the cost based on a P or M code.

Once the CDN List Price is calculated it's supposed to calculate a US list price based on the P or M code.

If I remove the second IIF statement and run the query everything works out fine and it displays the results in a table. With the second statement in I run query and a box pops up asking me to input to CDNlist PMT code. I can put it in but the calculated valves are way off.

Can anyone help?

Thanks!

Steve.

SELECT [updatedCosts].[ID], [updatedCosts].[ITEM], [updatedCosts].[description], [updatedCosts].[PMTcode], [updatedCosts].[Cost2004], [updatedCosts].[updatedBy], IIf([updatedCosts]![PMTcode]="M",[updatedCosts]![Cost2004]*2.3809,[updatedCosts]![Cost2004]*2.1978 AS CDNlist, IIf([updatedCosts]![PMTcode]="M",[CDNlist]/1.2,[CDNlist]/1.33)) AS USList
FROM updatedCosts;

View 5 Replies View Related

Reporting: If Statements

May 17, 2005

Hello
I have a report reading off of a query, the query shows an #Error
whenever the 0 spend is divided by the 0 potential. In the report i would like
this #Error to show as N/A. I tried =IIf([BY PROGRAM BY UNIT 30 DAYS.AdminRate]="#Error","N/A",[BY PROGRAM BY UNIT 30 DAYS.AdminRate]) but it doesn't work. can you suggest something else?

thank you

View 2 Replies View Related

Use Of Null Statements

Nov 15, 2004

I am trying to get a recordset to skip over empty cells in the data...however, I really don't want to use If, Then, Else statements as I already have quite a few in the code, and by using more, it's causing the code not to run

I've been trying to use:

If IsNull(field) = True Then GoTo CertainSpot

here is where I'd like it to continue if it is false, without using If, Then, Else...
so basically, if the above is true, go to that spot...otherwise, just continue running the code

Is there any way of doing this and avoiding the If, Then, Else statements?

thanks

View 14 Replies View Related

Conditional Statements

Aug 16, 2006

What is the best way to handle a complex conditonal statement like this? I thought about nested statements but i'm not sure if i can put all of these statements in the expression. There are some other conditions that i did not include.

If VBand = A and if CBand = A, allowed
If VBand = B and if CBand = B, allowed
If VBand = A and if CBand = A/B, allowed
If VBand = B and if CBand = A/B, allowed
If VBand = A and if CBand = A/C, allowed
If VBand = B and if CBand = B/C, allowed
If VBand = A and if CBand = A/C, allowed

If VBand = B and if CBand = C, not allowed
If VBand = B and if CBand = C, not allowed except certain areas
If VBand = A and if CBand = C, not allowed
If VBand = C and if CBand = A, not allowed except certain areas
If VBand = C and if CBand = C, not allowed
If VBand = A and if CBand = B, not allowed except certain areas
If VBand = C and if CBand = B, not allowed except certain areas
If VBand = A and if CBand = B/C, not allowed
If VBand = B and if CBand = A/C, not allowed
If VBand = C and if CBand = A/C, not allowed
If VBand = C and if CBand = A/B, not allowed
If VBand = C and if CBand = A/B/C, not allowed
If VBand = C and if CBand = B/C, not allowed except certain area

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved