Iif Statement Or Better Ways

Apr 17, 2008

Hello,
I was hoping someone can help me how to put the following statement in right format. I have 4 fields idResult, QuestionNumber, Description and Result.

So If Question Number and Description have same idResult then Result is (results are defined in the table) there are 30 combinations.

Many thanks,
Debbie

View Replies


ADVERTISEMENT

Top 10 Ways To Not Get An Answer

Feb 3, 2007

I've been here for two months and answered over 400 questions. I'm seeing a pattern here, and I have decided to post the best ways to make sure you are never answered.10) Blood, sweat, and tears are for losers. Spend no time figuring things out on your own.9) Announce almost ceremoniously that you have no VBA experience.8) Fail to use the search functions of this forum.7) Try to put every example you're given into a macro.6) Believe that this is the only source for answers, because Access Help provides nothing. Nothing, I tell you!5) Have us write your homework for you and then complain when you don't understand it.4) Post in German.3) Have a belief that your particular problem is the most important issue ever, and post in every sub-forum possible. Then bump it.2) Be vague, and then be belligerent.1) Have no appreciation for the people that are answering your questions in the first place. I know this sounds cold and mean and otherwise uncaring, but we are here to answer questions, not give you fish, as it were. This is a place to say, "I've done most of the work, but I need help over this hump," not "I have this crazy project/idea and I want you to write it for me." I've written over a dozen example DBs for people, and I know that I know what I'm doing. But, myself along with perhaps the majority of others that answer the majority of the questions, I'm asking on their behalf to please take the time to figure some of this out. If you can't do VBA, you are using 25% maybe of Access. If you can't understand your own DB, then asking us to fix it is a solution, but not a learning opportunity for you. If you can't understand what I'm getting at, it's that so many posts are the same. Again, I'm not trying to be an ass, but if you want to be a decent Access programmer and a programmer in general, then please, take the time to learn it. There are plenty of books (look at the bottom of this forum) and there are plenty of resources. I don't think any of us are teachers by trade, but the best teacher for something like programming is always trial and error. Programming is a language, a passion, a "something you figure out" if you will. If you're spoon-fed the entire time, you don't learn. I will continue to answer as I always do and I apologize if anyone was offended, but jeez, make sure you've tried everything offline before asking for help. And don't forget to search first, both here and Google.Sorry for the long read. :)

View 14 Replies View Related

Best Ways To Store Data

Apr 12, 2006

Hi
I am creating a contact management database.

I have set it up and now need to create an invoice section. An invoice needs to be issued every month and is worked out on a percentage of works complete. For example in one job there may be 10 items of work and for 8 of these iems 25% of the work is complete and the other 2 no work has started.
Therefore my invoice needs to be able to calculate 25% of the value of the 8 items and work out a total price to be invoiced. Each time an invoice is created I need to be able to store the invoice value and the percent value of each item complete.
Does any one have any ideas as to a good way of doing this. That is if you can undersatnd what I am asking for. Thanks for any help.

View 2 Replies View Related

Help: Ways To Open/Close Db In ASP

Jun 7, 2005

I'm trying to find out what's the best (or good ways) to open and close a database (*.mdb) in ASP without
worrying about it staying open. The website stops running (page not displayed) after it has been visited several times
I think, or it's happening when I'm creating new scripts I made which crash several times during debugging. Not
sure what's causing it.

Here's how the db connections work. I'm basically using (includes) to do lots of this..
every web page that requires the DB, I do this format...

(includefile: open database)
(includefile: create constants/variables)
(includefile: load functions used)
- the html page, which access functions when needed -
(includefile: close database)


the includefile that holds all the function scripts does not open or close the db at all but only the recordsets
semi-example function
(function)
rs.open "", cn <--- the cn was opened in the database script
- blah = the stuff -
rs.close
(end function)


Is this a good way to work with the database? I'm using access. Yes, i know sql servers are better, but I was told that
since I have only access, I want to minimize the number of connections, so I open the database once during the page
being opened, and close at the end of the page.

Like I say, the problem is, the site works great for a while (no real visitors yet as it's new), but it suddenly says
"page not displayed" after a while. NORMALLY this happens when I'm making new functions/webpages and did the coding
wrong while the database is still open. I'm thinking it's crashing because there were too many errors during the database
being open.


PLEASE HELP!

BTW, is it possible to just run a script that finds ALL the open connections after a crash and close them?

View 3 Replies View Related

How To Model A Field That Manifests Itself In Two Ways

Dec 1, 2005

Here is my problem:

Say I have a products table:

PRODUCT
========
Prod_id
Prod_name
Dept
Type
:


It is now possible that some of the products can also be a product
group, and a product group would then consist of multiple products.
(Note: if a product is a product group as well, both of them have
the same names. therefore for consistency, if in future someone
alters the product name, that should be reflected for the product
group if that product happens to be a product group as well)

So, I started of by thinking that the products table should contain
a binary filed like "Is_this_a_ProductGroup", where for any product
that is also a product group, while creating or editing the product
the user would flag this field "Yes" and "No" otherwise.

S, my revised products table is like:

PRODUCT
========
Prod_id
Prod_name
Is_it_a_ProductGroup
Dept
Type
:


Based on some earlier suggestions in this forum, I tried to create a
junction table like

PRODUCT_PRODGROUP
=================
Prod_id
Prod_Group_id

and joined the Prod_id above to the Prod_idin PRODUCT table and
created a copy of the PRODUCT table and joined the Prod_Group_id to
Prod_id in the PRODUCT_Copy table.

I would like some help now on:

1. whether this design is really correct?

2. If so, how does one go about the process of data entry through
forms for these tables?

Any help is much appreciated. thank you

View 1 Replies View Related

Ways To Select And Consolidate Records

Nov 24, 2004

I am currently building a db that will handle information on many events around the world. What I would like to do is set a check box that when people scroll through the different records they could check it and it (the current record they are viewing) will go into a compiled list wherein the iindividual record details of that list which the user created by their choices could be exported to an excell sheet (to automate a reporting need in the job) I have seen functions like this in large websites particularly the classified job sections in the seattle Times . where as you scroll through you see something you like you mark it continue scrolling mark another and then when you are all done you go to a page that has all and only your selections.

Can anyone tell me where to start on this one what am I Looking for Etc.
This will be the next big step in this my first database.
All help greatly appreciated

View 1 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related

Creating IIF Statement In Between Statement For Dates?

Apr 28, 2015

I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:

Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])

I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.

View 11 Replies View Related

Need Help With IF THEN Statement

Sep 25, 2005

I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's

If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then

This part works but I need to add a second one

If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then

So I want the text box to be RED if the status of both Ch A and Ch B is 0
but if either one has a status greater than 0 then the text box would be green.

Thanks

Jon

View 4 Replies View Related

Need Help With IF Statement

Oct 15, 2005

I'm using this code to do some calculations on a form. This code works fine
as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?


If Me.[qual] < 4 And Me.[completed] < 61 Then
Me.[GtoG] = [GtoGtotal]
Else
End If
If Me.[qual] < 4 And Me.[completed] > 61 Then
Me.[GtoG] = Me.[qual] + 61
Me.[temp1] = Me.[completed] - 61
Me.[Delayone] = Me.[Delayone] + [temp1]
Else
End If
If Me.[qual] > 4 And Me.[completed] < 61 Then
Me.[GtoG] = Me.[completed] + 4
Me.[temp2] = Me.[qual] - 4
Me.[Delaytwo] = Me.[Delaytwo] + [temp2]
Else
End If
If Me.[qual] > 4 And Me.[completed] > 61 Then
Me.[GtoG] = 65
Me.[temp1] = Me.[completed] - 61
Me.[Delayone] = Me.[Delayone] + [temp1]
Me.[temp2] = Me.[qual] - 4
Me.[Delaytwo] = Me.[Delaytwo] + [temp2]
Else
End If

View 3 Replies View Related

IIf Statement

Jun 21, 2006

Is there a better way to do this as it's not giving me the right results - so maybe I should say a correct way to do this!

=IIf([PMtype]="3" Or "4" Or "5",2,1)

Thanks

DBL

View 8 Replies View Related

If Then Statement

Nov 21, 2006

I am trying to do something really simple, but my lack of experience in Access has bitten me.
I have a report... there is a field called payor_code and one called bill_time. These are both based on tables.

The variable is payor code... it could be 2 different things.

All I want to do is make a calculation for TOTAL that multiplies the bill time, times the correct rate. (which is determined by the payor code)

I want to basically do this:

If payor_code = "02" Then
total = Bill_Time * 1
Else
total = Bill_Time * 2

Any suggestions?

View 14 Replies View Related

IFF Statement

Jul 19, 2007

Hellow :(

Question.....
i have a report in access on which grades of student are shown, like the following example:


Lifescience 6
Brainstorming 7
learningdev. 9
communication 5


My goal is to, next to the grades, also to put the grades as words...


Lifescience 6 six
Brainstorming 7 seven
Learningve. 9 nine
Communication 5 vife


I have no clue actually how to do that, i of course thought of an IIF statement, the only thing i know is the crystal reports syntax, but in this case i am not sure how to 'translate' it to the expression on the field.
(that is: i think i have to use the field where the grade comes from?)

Thanks for this, i think, relatively simple question........

Tj

View 3 Replies View Related

If / Or Statement

Mar 31, 2008

Ok guys, I am stuck. If I have the following codes that works fine when I separate each "IF" statement, but I was trying to combine it all into one code. When I combine the code, I continue to get the msgbox even when the criteria has been met. Please tell me what I am doing wrong. Just trying to simplify the codes.

If Me.cboFat = 1 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If

If Me.cboFat = 2 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If

If Me.cboFat = 3 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If

If Me.cboFat = 4 And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken, Closing Date is a required field!"
Cancel = True
End If


If (Me.cboFat = "1") Or Me.cboFat = "2" Or Me.cboFat = "3" Or Me.cboFat = "3" _
Or Me.cboFat = "4" And Not IsDate(ClosingDate) Then
MsgBox "Based on your selection in the Final Action Taken,Closing Date is a required field!"
Cancel = True
End If

View 4 Replies View Related

IIF Statement

Apr 17, 2005

Hi,

I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:

IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)

It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?

Thanks

View 2 Replies View Related

SUM Statement

Jul 6, 2006

On the following code I want to attribute the value of the sum([price]) to an variable called txtFullPrice:

Is there anybody that could help me please.

Dim dbs As Database, rst As Recordset
Dim strcriteria As String

'return reference to current database
Set dbs = CurrentDb

'Define serach criteria
strcriteria = "SELECT SUM([Price]) FROM tblRBS WHERE [USERID]='" & FOSUsername & "'"

'Create a dynadet-type Recordset object based on tblUser table
Set rst = dbs.OpenRecordset(strcriteria)

rst.Movelast

*******

'close connection on tblUser table
rst.Close
Set dbs = Nothing

This Forum have been helping me to improve my skills a lot. Thanks to everyone.

Jony Bravo

View 5 Replies View Related

IIf Statement...is This Possible?

Jun 27, 2005

I have a nested IIF statement in one of the parameters of my query that checks for the value in an option box and returns the results for a specific record based on the value. This works great for what we need and we've been using it for over a month without a problem.

I just found out that there may be instances where management might want to see a group of records with combined data. I've racked my brain and searched this forum and have not come up with a solution yet.

Here is my IIF statement:

IIf([Forms]![frmRejectopt]![optgrpWC]=2,1,IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))

In the first line is where I tried expanding the criteria to a group but I either get blank results or an error depending on how I've tweaked it.

This is an example of what I'm after:

IIf([Forms]![frmRejectopt]![optgrpWC]=2,([tblProcess]![ProcessID]=1 Or [tblProcess]![ProcessID]=9 Or [tblProcess]![ProcessID]=10 Or [tblProcess]![ProcessID]=11),IIf([Forms]![frmRejectopt]![optgrpWC]=3,2,IIf([Forms]![frmRejectopt]![optgrpWC]=4,5,IIf([Forms]![frmRejectopt]![optgrpWC]=5,9,IIf([Forms]![frmRejectopt]![optgrpWC]=6,10,IIf([Forms]![frmRejectopt]![optgrpWC]=7,11,IIf([Forms]![frmRejectopt]![optgrpWC]=12,4,IIf([Forms]![frmRejectopt]![optgrpWC]=8,3,IIf([Forms]![frmRejectopt]![optgrpWC]=9,6,IIf([Forms]![frmRejectopt]![optgrpWC]=11,7,IIf([Forms]![frmRejectopt]![optgrpWC]=10,8,[tblProcess]![ProcessID])))))))))))

I hope I was clear in my explanations. Thanks in advance for your help.

View 11 Replies View Related

IIF Statement

Sep 1, 2005

What i am trying to do is:

IIf([Me].[Custom1Checkbox]=-1,([tblPart].[Description]) Like "*" & [Forms]![frmParts]![TxtFilter] & "*",([tblPart].[Description]) Like [Forms]![frmParts]![TxtFilter] & "*")

have tried:

(IIf([Me].[Custom1Checkbox]=True,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))

and

(IIf([Me].[Custom1Checkbox]=-1,[tblPart].[Description] Like "*" & [Forms]![frmParts]![TxtFilter] & "*",[tblPart].[Description] Like [Forms]![frmParts]![TxtFilter] & "*"))

but with no luck

what i have done to get over it for now is:

Link to post (http://www.access-programmers.co.uk/forums/showthread.php?t=92802)

View 12 Replies View Related

IIf Statement Help

Oct 12, 2005

Hi there

I am having problems with an IIF statement and I was wondering how I can get the query that I need to work.

I have the following columns

application_type: this has three values in it - Major, Minor and Other
numberofdaystodecide: this is the number of days taken to decide a file

what i want to do is an iif statement that works as follows

iif the application type = major and the numberofdaystodecide >92 then over 13 weeks

iif the application_type = major and the numberofdaystodecide <=92 then less than 13 weeks

iif the application_type = minor and the numberofdaystodecide >56 then over 8 weeks

iif the application_type = minor and the numberofdaystodecide <=56 then less than 8 weeks

iif the application_type = other and the numberofdaystodecide >56 then over 8 weeks

iif the application_type = other and the numberofdaystodecide <=56 then less than 8 weeks

Any ideas how I put this in to a query to get the information that I need?

Tried a couple of things to no avail:(

Thanks for any help :D

Jools

View 5 Replies View Related

TOP Statement

Feb 20, 2006

Hello Guys,

I have a question.

I have a query that looks like this:

Category Customer_ID
1 123444
1 233732
1 87667
1 3343
1 99876
2 123
2 775446
2 332334
2 22311
2 1121

Now, from Category 2 I want to see the top 3 and from Category 1 I want to see all.

I know I can't use Select TOP because it cuts random.
I just want to cut from Category 2.

Thank you in advance guys

View 1 Replies View Related

SQL Statement?

Mar 4, 2006

I am using a database that holds (amongst other things) payments received from a membership. This info is held in tblPayments. When payments are received I enter a new record against the membership number and in a field named PdForYr I enter the year for which they are paying, i.e. current year is 2005/6. I need to be able to extract members who have NOT renewed, i.e. this info is not in the database. So I think I need a SQL statement asking for records that paid in 2004/5 but not in 2005/6. Can't quite get it. Any help please?

View 6 Replies View Related

Using Between In An IIF Statement

Mar 24, 2006

I am trying to display Shift information in a query by using the following

IIf(Time([CALL IN]) Between (07:00) and (15:30),"1st Shift",IIF(Time([CALL IN]) Between (15:31) and (23:30) ,"2nd Shift",IIF(Time([CALL IN])Between (23:31) and (06:59) ,"3rd Shift"," ")))

and I am getting a message "The expression you entered has too many closing Parentheses"

Can anyone spot my error

Thanks

View 3 Replies View Related

IIf Statement

May 3, 2006

Should this statement work?

IIf([CalledNumber] Like "*?*",[CalledNumber]=Mid([CalledNumber],2))

Some of the fields brought in from the CSV file have random "?" in front of some of the names, so I made this to remove the first character of the field if there was a "?" there. Maybe there is an easier way, but I would still like to understand why this doesnt, it just returns no data as if there are no fields matching, there are some with a "?" e.g.

?JoeBloggs

View 6 Replies View Related

IIF Statement

Oct 12, 2006

Hi,

I'm running a query and pulling in a status field, with the following selections:
25% to sell
50% to sell
Sold

I've added an IIF statement (see below) to change the sold to 100%, however, it will only return 100% and does not pull in any of the other data.

IIF([Salesboard4]![Status]="Closed",100%).

How do I change my statement to pull in all data and change the sold to 100% at the same time.

Any help will be much appreciated.

Thanks

View 5 Replies View Related

Iif Statement

Nov 2, 2006

I have the following statements and keep getting "YES" where I should be
getting "WAIVED" or "NO". Does anyone know why, everything else evaluates correctly.

IIf([admit_dec_code] Is Null Or
[admit_dec_code]="R","",IIf([admit_dec_code] Is Not Null Or
[admit_dec_code]<>"R" And [date_acc_fee_paid] Is Not
Null,"YES",IIf([admit_dec_code] Is Not Null Or [admit_dec_code]<>"R" And
[date_acc_fee_paid] Is Null And [accept_fee_amount]="1","WAIVED","NO")))


IIf(IsNull([admit_dec_code]) Or [admit_dec_code]="R","",IIf(Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And Not IsNull([date_acc_fee_paid]),"YES",IIf(Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And IsNull([date_acc_fee_paid]) And [accept_fee_amount]="1","WAIVED","NO")))

View 6 Replies View Related







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