IIF Statement Not Working
Nov 23, 2007
hi i am trying to use the following iif statement in a query.
IIf([Forms]![MainForm]![cmb_ExcessStatus]="Resolved",1,IIf([Forms]![MainForm]![cmb_ExcessStatus]="Outstanding",2,IIf([Forms]![MainForm]![cmb_ExcessStatus]="All Records",([MainTable].[Status])=1 Or ([MainTable].[Status])=2,([MainTable].[Status])=1 Or ([MainTable].[Status])=2)))
So, if user selects 'Resolved' then use 1
if the user selects 'Outstanding' then use 2
if the user selects 'All records' then i want both 1 and 2 but this will not work. i also want the default to be 'All records'
Any help would be much appreciated.
View Replies
ADVERTISEMENT
Aug 3, 2006
Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))
FixedExpenseOccurance is the the field and "*" is the values which get looked up from a table. I am trying to create an expense report. The query runs but no values are entered into the box?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
View 10 Replies
View Related
Aug 3, 2006
Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))
I have this in a query and the query runs, it just doesnt do anything. FixedExpenseOccurance is the field name, "*" is the field values (they get looked up from a table). Any clues why this is not working?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
View 8 Replies
View Related
Aug 10, 2006
(IIf((NextActivtion)>=(Today)),((DueToday)="yes"),((DueToday)="No"))
Says I have the incorrect number of Arguments? Any clues? These are all field name:
NextActivation (Is a date)
Today (is today's date)
DueToday (is a yes/no)
View 13 Replies
View Related
Mar 2, 2008
i have an sql statement:
aa = "hello"
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
View 5 Replies
View Related
Oct 9, 2005
hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.
-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]
------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"
View 4 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data tyle mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 1 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data type mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 3 Replies
View Related
Dec 5, 2006
hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.
I am trying to do an append table but i keep getting an error message
Pricing ID is the pk autonumber in tblPricing (parent table)
historyid is the pk in tblHistory and pricingid the fk(child table)
oldprice should be a currency value
the other fields are text.
this is my code:
strSQL = "Insert into tblHistory (DateChanged, Edit_UserName, OldPrice) Values(#" & now() & "#, '" & getusername() &"', " & Me.txtPrice.OldValue & ")
but i get the error message
You cannot or change a record because a related record is required in table tblPricing
any advise... i know i need to select somehow the pricingId from the parenttable, but how do i do that?
View 8 Replies
View Related
Jul 8, 2015
why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.
Code:
If Nz(Me.txtrefNo, 0) = 0 Then
Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1
tmpRefNo = Me.txtrefNo
'testing variable value
MsgBox (tmpRefNo)
Else
MsgBox ("What's going on?")
End If
PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?
View 2 Replies
View Related
Jun 21, 2013
I am trying to created an IIF statement in a field I have in a form.
I have entered the following statement but it keeps coming up with the following error and I cannot see where I am going wrong.
The expression you entered contains invalid syntax, or you need to enclose your text data in quotes
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100),IIf RenewalInstallment]<>0,(12-[MFAccepted])*[RenewalAnnualPremium]/12)*(1-[DiscountAccepted]/100)
If I enter the first part of the If statement i.e.
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100)
The field calculates correctly however there seems to be something wrong with the nested if.
View 6 Replies
View Related
Apr 8, 2015
Access 2013
I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.
IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))
View 6 Replies
View Related
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
Dec 27, 2014
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
View 1 Replies
View Related
Jan 15, 2015
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
View 13 Replies
View Related
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
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
May 10, 2005
Hi all,
I'm making a simple query to tell me how old a list of members are in a database.
I figure as I have their birthday, armed with todays date access should be able to tell me (in years only) how old they are.
Any help is appreciated
View 14 Replies
View Related
Jul 19, 2005
Hi,
I have 2 computers (computer 1 with Office XP and computer 2 with Office XP SP1). An MDE file created from an MDB file on computer 1 will not work on computer 2 (run time error), but an MDE file created on computer 2 also does not work on computer 1 (error message was "database corrupted").
Does anyone know why and what I can do to make the MDE work on both machines?
View 6 Replies
View Related
Jul 21, 2006
Hi. I have got a small database, I have split it into front and back end. I then try to make an MDE out of the front end and after going to tools>db utilities>make MDE file, i got the save as box open up and i pressd save to my desktop,the box disappears and the screen is just blank(like there is no database file has been open,at the bottom of Access it says Make MDE/ADE.
And it just crashes.. it doesnt come up with any errors.
If anyone has any information pleeeeeaaaseee help.......
View 8 Replies
View Related
Aug 2, 2005
My Combo Box works and displays new entries in text boxes in my form. The selection appears in every record. I would like the selection to be stored only in the displayed record and not for each and every record. Any ideas?
My combo box works on an existing query( Qry1). I would like the data from my query (Qry1) to be displayed in the record (in Form1) which I am viewing. When I go onto a new record (IN Form1) I would like to make a new selection from my query (Qry1) and then store these details in the new record in Form1.
I am a little frustrated - please help.
View 3 Replies
View Related
Sep 20, 2006
This seems so simple, but, being a novice, I'm scratching my head.
I'm using an unbound text box in the form footer to sum the value of a column in the form ( =Sum([LineTotal]) ). [LineTotal] evaluates correctly, yet the sum is always #ERROR. Is there something I am missing, or am I correct in thinking that this should work?
Thanks to anyone who can shed some light.
-Don
View 7 Replies
View Related
Nov 8, 2004
I open a form that is bound to a linked SQL table. On that form a have a list box that shows a series of records based on a query. The listbox DOES contain the PK field "Record_date" in its query.
I am expecting that if I click on a given record in the list box the form would change to that matching record. What am I doing wrong ?
List box code:
Private Sub List212_AfterUpdate()
' Find the record that matches the control.
Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[Record_date] = #" & Format(Me![List212], "mm/dd/yyyy") & "#"
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End Sub
If I change the form record and click the list box, its always takes me back to the 1st record - not the matching record selected in the form.
Help ?
View 3 Replies
View Related
May 8, 2005
Hy,i've got three tables:
products:
product_id,product_name
months:
month_id,name_of_month
calculations:
calculations_id,month_id,amount
Also have this query:
SELECT DISTINCTROW products.product_name, Sum(calculations.amount) AS [Sum Of amount]
FROM products INNER JOIN calculations ON products.product_id=calculations.product_id
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value)
GROUP BY products.product_name;
My problem lies in this part of query :
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value).
I've got problem when i want to get values from my textbox on my form.
If i instead (forms!frmMyForm!txtMyTextbox.value) put (1,2) my query works fine,but when i wanna get this same values from textbox on my form it returns me an error.
I have also noticed one more thing:If i put in my textbox just value 1 it works fine,but if i use more values separeted by comma instead of result it returns me an error.
Why is this,and how to avoide this problem?
Thanks!
View 8 Replies
View Related
Jul 29, 2005
Private Sub propertypass_Click()
If [Forms]![UpdatedFullInventory]![PAM_ID] = [Tables]![UpdatedPropPassInventory]![PAM_ID] Then
MsgBox "A property pass for PAM ID " & PAM_ID & " has already been created. Please search the property pass database for more information!", vbOKOnly, "Error!"
End If
My debug message says
Microsoft can not find the field 'l' referred to in your expression.
View 1 Replies
View Related
May 13, 2005
Hello,
For osme reason this MS Access database I'm working on will not let me debug it. First off, even if I mistype a variable name it is not breaking and giving me an error message. I checked and I do have 'Option Explicit' set as well as "Error Trapping - Break on All Errors" in the Tools - Options - General.
Also, when I set a breakpoint in my code, it doesn't work either.
Any ideas as to what is going on??
Thanks,
Brian
View 1 Replies
View Related
