Topics Search

Switch () Function In Access

Switch () Function In Access
Views: 7
I'm supposed to write code that would give results of a truth table from a table in access. For example iif column_a <>"", column_b<>"", column_c<>"", column_d<>"", "1", Iif column_a<>"", column_b<>"",column_c<>"",column_d="","2", iif column_a <>"", column_b<>"", column_c="", column_d<>"","3", etc. There are 16 different combinations so I can't really do nested iifs. Would the switch function work. Or can I only switch 1 to 1 (there are 4 columns that I am checking values on
Sponsored Links:

More topics

 

Switch Function

Preview
I am very new to using Access and am trying to use the Switch Function. I am have a large list of URL Domains and would like to categorize them. I have written:

[CODE]

The Function works as is, but as soon as I try to add in additional lines (in red), it no longer works for me, here is what it looks like:

[CODE]

Can someone give me some advice on what might be hapenning or a different option that may be better?
 

Switch Function

Preview
In Access, the Switch function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE.
 

Switch Issue

Preview
I have a query which is using a switch function to perform a calculation. The problem is the result is #Num! and I can't figure out why. This is the function.

MPP: Switch([CurPt]>0,[CurMin]/[CurPt],[CurOd]>0,[CurMin]/[CurOd],[CurMin]=0,0)

. I've attached the DB. The query in question is qryProdSupport1.
 

Using SQL switch function MS Access

Preview
I need to know how to use SQL switch fuction in MS Acces. Simple SELECT query is like this but it is not working,

SELECT Switch(Habitat=T,"Terrestrial",Habitat=A,"Aquatic" ,Habitat=E,"Epiphytic")
FROM MainCharacteristics;

I need to display "Terrestrial" insted of "T" like wise,
 

Switch Function not working properly

Preview
I have the following switch function in a query.

DpRpPc: Switch(([DpNeIs]+[DpNeOs])=0,0,[DpNeOS]>0 And [DpNeIS]>0,(([DpNeIs]/2)+[DpNeOs])/[NeDpPts],[DpNeIs]>0 And [DpNeOs]=0,([DpNeIs]/2)/[NeDpPts],[DpNeOs]>0 And [DpNeIs]=0,[DpNeOs]/[NeDpPts])

The problem is if the DpNeIs=0 and DpNeOs=0 instead of displaying 0, it displays #Num
 

Using Case statements in a function

Preview
I was using Switch() in the query builder but my statement ended up being too long according to the error I got from Access. What I'm trying to do is assign a sales person to a client based on the clients first initial of his/her last name.

Now I am trying to create a function to take the field Alpha, and create the field SPerson using a function that uses a Case statement.

This is what I have thus far:
 

Use Select Case to Consolidate Switch Function

Preview
I need to consolidate 5 fields stored in a table into 29 different categories (eventually to be used in a cross-tab query to generate counts of each category). I'm limited in using switch and/ or iif statements because of the number of nested statements I would need.

Is there a way to do this using by creating a function and using select case? I'm not sure how to reference multiple fields in a table.
Here is an example of what I was originally thinking of...
 

Microsoft Access Runtime switches

Preview
I have an unsplit database which I want to run with Runtime on a computer that doesn't have Microsoft Access.

I am unable to get Microsoft Access Runtime working on the main networking computer (not mine) without the error message (offering switches for Runtime) coming up and then it comes up with a message saying something to the effect of there being an error and the application is closing down. I have used Runtime on several computers with no problem.

What are some of the likely reasons for this message coming up?
Is there a need to choose a switch?
If there is a need to choose a switch, which switch should I choose?
If there is a need to choose a switch, exactly where should I put it?
If there is no need to choose a switch, what should I do to fix the problem
 

SQL Switch Statement Question

Preview
I'm using a SWITCH statement in a SQL query in Access that applies different formulas based on the value of the Category field in the table. I need to head off a possible division by 0 error. Here's the code for that part of the SWITCH:

SWITCH(
Category = ('Category3') AND PFM 1, (IMUnits*PFM)/(1-PFM),
Category = ('Category3') AND PFM = 1, 0
)

The idea being that if PFM = 1 it should skip the formula (because 1 - PFM would be 0, causing a division by 0 error) and just use 0 as the value. However, it seems to be ignoring the "AND PFM = 1" part and doing the division anyway because it still returns #ERROR for any row where PFM = 1.

Can I not use AND within a SWITCH condition? How can I make this work? Note thatthis is only part of the SWITCH statement, there are several more conditions that can occur, which is why I want to use SWITCH.
 

Using a function in form

Preview
Can someone help me with functions. I'm just starting to learn how to use vba with access.

I wrote simple function that calculates difference of two fields:

Public Function oduzimanje(IznosPlaanja As Double, CijenaTe
aja As Double) As Double oduzimanje = (CijenaTe
aja - IznosPlaanja) End Function

In form in which I would like to use this function , I made Text box and in it wrote

=oduzimanje([Iznos plaanja];[Cijena te
aja])

And when I switch to Form view in that text box instead of result I get