Iif With True Part Containing 2 Values

Nov 28, 2005

First Post - please be gentle :)

I may be missing something here, but can any one help?

I am using a Iif statment in a query to filter records as follows:

If([value from a form]= "all holidays",("weeks hols" or "days hols"),"not a holiday")

This is returning a to complex to calculate error - I think it is a Syntax error but could be wrong
but if I enter The string "week hols" or "days Hol" as the criteria without the Iif statment that works fine.

Thanks Dean

View Replies


ADVERTISEMENT

Queries :: IIF Statement With Two True Values

Dec 3, 2014

Can I generate an IIf statement with two-true values? I have these two statements but don't know how to combine them:

IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;””)
IIf((Weekday(date);1)=vbSaturday or vbSunday;y;””)

I have at field with a date and will like the true statement to be “x” if the data is Monday-Friday and “y” if the date are a Saturday or Sunday and if the date field is blank, I want to have a blank field here as well.

I have tried:

IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;y)

But it returns y in fields that should be blank.

View 7 Replies View Related

Option Group With True Or False Values

Jul 3, 2006

I want to create an opion group with two rad buttons in it. The values of those button wont be values but as true or false in order to use it in another text box to performa calculations. EX.

=IIf([radNew],"Hello",IIf([radUsed],IIf([txtAgeofCar]<184,[txtExciseinTotal]=Null,IIf([txtAgeofCar] Between 185 And 365,[txtExciseinTotal]*(-0.15),IIf([txtAgeofCar] Between 366 And 1095,[txtExciseinTotal]*(-0.2),IIf([txtAgeofCar] Between 1096 And 1825,[txtExciseinTotal]*(0),IIf([txtAgeofCar]>1825,[txtExciseinTotal]*(0.25))))))))

As you can see radNew and radUsed are the Options Buttons. Its not working in an option group.

Thanks in advance

View 2 Replies View Related

Access Report Design - Display True Values As A List With Checkboxes

Oct 1, 2015

I have a combo box in my report design and I would like it to display "true" values as a list with check boxes.

View 5 Replies View Related

Queries :: If Function And Inserting Null Values In False Part

Oct 7, 2013

I have a calculated field (Bhours) with the following IIf function:

=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)

It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".

View 7 Replies View Related

Tricky Question - Form Part Data Entry, Part Not

Dec 16, 2004

I have what I think is a difficult problem to overcome...

I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.

Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!

If this is not possible, do you know how I can acheive this?

Thanks

S

View 1 Replies View Related

Date Problem - Part Constant - Part Now()

Nov 3, 2006

Hi everyone,

I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())

Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.

Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.

Many thanks,

View 6 Replies View Related

If A Is True, Then Do B

Apr 9, 2007

=IIf([Text34]<=2644,0,IIf([Text34]<=7164,650,IIf([Text34]>=242506,22000,[Text34]*0.0907194)))

The above formula works as is in the field I have it in, but need it performed only if [Label5] is marked yes. I guess what I need is what goes in front of the above formula. I've looked through the forum, but it is so huge I had no luck finding what I needed.

If [Label5] is no, I need it to go to [Label6] and if [Label6] is yes, perform another calc like the one above. If [Lable6] is no, I need an error message.

Hope this is clear. Any help is greatly appreciated.

Thanks.

View 3 Replies View Related

Allowaddition = True

Sep 15, 2005

Hi all very simple question i'm sure but had a look through the forums and couldn't find anything.

On a form i have 2 buttons, one for new data and one for editing data. When either of these buttons are clicked a few text boxes pop up and immediately display data (The first record of the recordset). For the edit button this fine, however how do i get the text boxes to display a new record and not allow the user to head backwards through the recordset only onto a new record??

Thanks in advance

View 4 Replies View Related

Posting A Pic When A Value = True

Jan 31, 2008

Hi everyone,

I have made a form for our database and I would like to have a .gif image display when a certain checkbox is checked. Any idea how to do this?

Thanks

PS - sorry if this is a noob question...I don't use access much

View 4 Replies View Related

Locked=True Fuzzy

Jun 1, 2005

I have a form that has code tied to the 'on open' event that is going to be accessed by users where we want them to only have access to certain fields which we want them to fill out. The fileds that will be locked will change based on the field called 'Item Number'. The code will be long because there are 30 different Item Numbers and about 10 to 20 fields that we will disable based on the Item Number. The code is like:

Dim Item_Number As String
If Me.Item_Number = "32000" Then
Me.Batch_Lot_Number.Enabled = False
End If

This is all great except that the disable makes the field kind of obscure by the color it gives it. I don't want to use the lock property because that doesn't give you a visual clue that its locked.

Is there a way to change the color of the field background using VBA?

View 5 Replies View Related

Counting True Items

May 4, 2006

Can anyone help?
I have a table with the following:
Booked with a y/n field
Person Name with a text field
So...
Yes(True) Jim
Yes(True) Jim
No(False) Jim

What I need is to be able to show a query that shows if the checkbox is ticked (True) and the Name is Jim total up how many true items there are. So from the above I can see that Jim has 2 true items. I would then like this shown in a text box is this possible?
Also, it is not always going to be a person called Jim so... (From above)
Yes(True) Jim
Yes(True) Jim
No(False) Jim
Yes(True) Jack

So how would this go on a query in a sub form? So Name Jim would show 2 and Jack would show 1? At the moment is is grouping the name and I dont want that.
Any ideas
Cheers

View 4 Replies View Related

Is It Possible To Return No Value When An If Statement Is True?

Sep 22, 2006

This should be really simple, but I am stumbling on the criteria. I have a combo box which returns "Y" or "N". I'm using this value as criteria for a field called [Planned=Y/N]. This field stores either a "Y" or a "N" to indicate that a network outage was planned or not. If the combo box is "Y" I want to return all values(Y and N). If the combo box is "N" I only want to return values of "N". I've tried the following in the criteria without the desired affect:

IIf([Forms]![frmOutagemenu]![cboflag]="Y","","N") - only returns records when "N" is selected.

IIf([Forms]![frmOutagemenu]![cboflag]="Y",null,"N") - only returns records when "N" is selected.

IIf([Forms]![frmOutagemenu]![cboflag]="Y",In("Y","N"),"N") - too complicated for Access to decode.

Is there another way to return nothing for the criteria when "Y" is selected?

View 3 Replies View Related

True Date Ranges

Nov 6, 2006

I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!


Between Date()-Weekday(Date()-1) And Date()-1

View 2 Replies View Related

Count Of TRUE Statements?

Oct 18, 2006

Hi! When it rains, it pours, right? I'm wondering if any of you know of a way to count true statements...

What I mean is, I have a query where I have a series of 5 different OR statements, meaning I want to see records with either a min. ed. number, or a years of experience number, etc...

The query returns every record with at least one of the criteria being met. What I need is to count the statements that are true for one record. Let's say I have someone who meets all of the OR criteria, I need a way to count the number of criteria that they met. Is there a way to do this? Maybe with a crosstab query??? Thanks!!

KellyJo

View 3 Replies View Related

Open Query If Field Is True

Jun 6, 2006

I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!

View 1 Replies View Related

Open Query If Field Is True

Jun 6, 2006

I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!

View 1 Replies View Related

Open Query If Field Is True

Jun 6, 2006

I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!

View 3 Replies View Related

Open Query If Condition Is True

Jun 15, 2006

I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.

View 7 Replies View Related

Too Simple To Be True? ...is This Design Proper?

Apr 30, 2008

New to Access, and having a heck of a time learning it...or rather learning how to correctly design databases.

All my expertise is with Excel. I'm creating a project where I use Excel to parse a non comma delimited text file, then feed certain figures into an Access database. This is all through VBA.

It wasn't until yesterday that I realized I had a problem. There are two text files with data that makes up one complete record. With what I already have built, and with what I have tought myself (ADO w/ VBA wise) I tought the easiest solution would be to create two tables that will hold the data from each respective text file. This is what I'm working with:

A store has a department with 5 areas of measurement that is collected daily. One complete record would be like this:

Date | Store | Dept | Sales | Cust Count | Item Count | Avg Price | Mix

...and there are (right now) 3 stores and 15 departments that are watched in this project. What I came up with for a table design was this. Fields with an "!" prefix reflects primary keys.

Table1 (using data from txt file 1): !Date | !Store | !Dept | Sales | Mix
Table2 (using data from txt file 2): !Date | !Store | !Dept | Cust Count | Item Count | Avg Price

In each table, I have to have a compound primary key to make up what is a unique record. I just learned I could use a compound index and an autonumber as my primary key. Either way, I'm using the 3 primary keys in each table with a 1 to 1 relationship. This seems to work if I make a query.

Now, will the way I did it hold up? Is there a better, more correct way to do it?

Thank you for the time & help!

View 4 Replies View Related

True Or False Query Issue

May 12, 2006

I have a query that shows banned users (I work in a school). When a student's ban has ended, a tick box is checked in a form linked to tblBannedUsers to show they are no longer banned.

The query itself simply queries all records in tblBannedUsers with a username field (UserID) taking search criteria from a box on a form.

However, I want to filter out the students that are no longer banned (those with a tick in the checkbox). To do this, I thought it would be a simple case of editing the query, and in the Ban Lifted field criteria, use =False to say I only want those records with a tick.

This doesn't work and I still get all records given the combo box filter (which are just filtering for a username...if I leave it blank it gives all records via Is Null).

What should I be putting in the criteria to filter out those records with a ticked checkbox?

Thanks,

Steve Swallow

EDIT: I've just done a test query and <B>No</B> is the criteria to use, but when I use this criteria in my query which also take data from a form's combo box it ignore the <B>No</No> criteria.

View 4 Replies View Related

Query Similar To Vlookup (true)

Oct 9, 2006

Hello Everybody!!!

I need a help.
I have one table that has a field called "Initial ZIP CODE", another field called "Final ZIP CODE", and a field called "AREA".

I have a data base that will give me the specific "ZIP CODE", that will be between the "Initial ZIP CODE" and "Final ZIP CODE", and I want to get the "AREA".

Anybody knows how to to that???

Tks in advance.

Paulo

View 2 Replies View Related

Tick Checkbox If Database Is True

Dec 3, 2006

Hi,
in my web page, i would like to user to see a ticked checkbox should the database = True however, i cannot seems to be getting it. Would anybody be able to help me out?

strSQL = "SELECT DeptHeadA FROM Employee WHERE EmpId = '" & strEmpId & "'"
nRecDHA = GetRecordset(strSQL, arrDHA)

if arrDHA(0,i) = True then
arrDHA(0,i) = "Checked"
else
arrDHA(0,i) = ""
end if

<tr>
<TD valign=top width="27%"><font size="2"><b>Department Head Alternate (1st) :</b></font></TD>
<td width="72%">
<input type="checkbox" name="DeptHeadA" value="<%=arrDHA(0,i)%>"></td>
</tr>

View 2 Replies View Related

How To Write Iif Statement Which Has Two True Cases

Aug 12, 2007

Hi, the syntax is of iif is that iif(case, true, false) right.
In my case I have role_ID to filter so my iif statement is something like
iif([role id] = "1", "2" or "3")
But it does not work, I don't know why
iif([role id] = "1", "2")
It works. So I think OR statement is something wrong.What should I do in this case?

View 6 Replies View Related

Using Date And A Value To Make A Text Box True

Mar 27, 2008

Hey all, i got another small problem with this bloody database!

The problem today folks is this, im making a booking system (well the booking is a small part of the overall system) so i have DateArrive, RoomID, and all the usual.

now what i want is to make a query that says
if DateArrive & RoomID (Booking Table) then make RoomOccupied(Room Table) true

if that makes sense, i tried some in sql and got the date bit working, but wasn't sure how to go further so as to make it so RoomID is involved

(ill say in another way If a room is booked on a specific day i want to make Occupied true)

thanks for anymore help u can give

View 6 Replies View Related

Visible Image If 2 Checkboxes Are True

Mar 14, 2005

Hi,

I have three checkboxes on my form and I would like to set the following properties:

I have an MP3, a WMA and Logged field.
If logged = yes and either wma or mpr = yes, then I want a play button to be visible.

I have tried using the following code:

Private Sub Form_Current()
If Me.checkboxname = True Then
Me.commandbutton.Visible = True
Else
Me. commandbutton.Visible = False
End If
End Sub

But this will only allow for 1 checkbox. Can anyone help?

View 4 Replies View Related







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