=IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))????

Using Access 2000, I am creating a report that get info from a Query (QryOneContractFundDocs) whose statement is as shown below:SELECT FundingDocs.FundingDocTitle, FundingDocs.DollarAmount, FundingDocs.ContractNumberFROM FundingDocsWHERE (((FundingDocs.ContractNumber)='NMIPR029209245 DD448-2'));That ...

=IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))????

Using Access 2000, I am creating a report that get info from a Query (QryOneContractFundDocs) whose statement is as shown below:SELECT FundingDocs.FundingDocTitle, FundingDocs.DollarAmount, FundingDocs.ContractNumberFROM FundingDocsWHERE (((FundingDocs.ContractNumber)='NMIPR029209245 DD448-2'));That ...

append query with isnull function

i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.Thank you,Thomasinsert into tblcustom ...

Nz IIf Standard SQL, is there such a thing?

I have written a database as part of a project and it works fine so far. It's in Access 2000 and I need to make it web based and so have opted for Java for various reasons. I know I can link to Access (or any database) through Java, but I would like users (this will be freeware) to be able to use wh ...

Empty Spaces and Blank Lines and a long IIF

I've seen this issue covered to some extent in previous posts, but here's my "special case" sob story:Sometimes, address fields are blank (like no country information) and I want to move up the next field on the report so I don't print blank lines. I've concatenated my address fields, chec ...

IIf statement in query criteria

I have a query that the iif statement in the criteria of column "SalesRepID" will not acknowledge the true part of the iif statement of "Is Not Null". this only happens when i use "Is Not Null" and any operator like(>,<,<>,null,Like"*") in the iif ...

IsNull & NZ not working...

The following query:SELECT [Lease Assignment].Employee, Count(NZ(([Lease Assignment].[Tax_check_or_filing_fee]),0)) AS Tax_check_or_filing_fee FROM [Lease Assignment] WHERE ((([Lease Assignment].Date) Between [Forms]![Menu]![txtLA_PRfrom] And [Forms]![Menu]![txtLA_PRto])) GROUP BY [Lease Assignment] ...

IIF Statements

I am using an IIF Statement in an unbound field in a report which is retrieving the data from a query.The statement is as follows:-=IIf(IsNull([HomeAddress2] And [ActiveHome]=Yes),[HomeAddress1] & ", " & [HomeTown] & ", " & [HomeCounty] & ", " & ...

What is wrong with my iif and numeric data

I have been through all the posts that I could using the search function but, could not find the answer to my question, so I registerd and figured I would post it. Here is the issue, when I issue the following SELECT [Join all Tables].[Business Unit]FROM [Join all Tables]WHERE ((([Join all Tables].[ ...

Need Help with IIF Statement

I have a column that I am trying to populate. The logic is as followed:if [column a] is null and [column b] = "33220", "alan", [column a]I cannot seem to get the logic right for this expresion. Everytime I run it I still get a null. I have:Expr4: IIf(IsNull([full_name]) And ([cos ...

Expression/IIf statement help

I'm trying to use this expression in a make-table query.IIf(IsNull([ShopCartDesc].[Status]),Null,IIf([ShopCartDesc].[Status]="pre","pre",IIf([IM2_InventoryItemWhseDetl].[QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0,"new",IIf([ShopCartDesc].[Date Imported]>DateA ...

iif in query does not do what i want

i have this code in a queryTicketTotal: IIf(IsNull(DLookUp(" [qrySetShowsTempReport2]![SumOfTicketPrice] ","[qrySetShowsTempReport2]","[title]")),0,DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice]","qrySetShowsTempReport2","[title]"))it ...

IIF function

I have problem with the report, there is field called country, I used iif function, it always show #error. Could you please check it for me?IIF(IsNull([Country]),"",[Country])If I change to IIF(IsNull([Country]),"","This is Country field"). It will show "This is Co ...

sql in iif statement

working in a query in Access 2000 and creating a new field called Status with an iif statement. it's working, except that i have some values that are above the high end of number6.so i want to change this last one from a between statement to a greater than or equal to. problem is: when i do that, i ...

IIF syntax with table records and Null

x: IIf(IsNull([Equipment]![x]),[Equipment Compartments]![x],[Equipment]![x])Access reports syntax error?? I have two tables, Equip and Equip Compartments....I'm just trying to use defaults in the later if the former x value is null.... ...

Help with Nz/IsNull

HiI am a new user in access and created a database to use in accounting practice. I want to create a query control that will show the opening balance, plus fees less closing balance = 0 as all data are imported from another system.The problem is that the closing balance does not contain a full list ...

The isnull function requires 2 argument(s).

I know this is going to be easy to all of you, this is my first time performing an if else statement in SQL, after performing this query: Collapse | Copy CodeIIf(IsNull(EventSummary.[Event Information - Event Status]),"ACTIVE",EventSummary.[Event Information - Event Status]) AS [Event Stat ...

Parameterized queries - works in Access but not SQLS2k?

I have an application where users can enter data into any (or all) of 6 search fields, to produce a filtered query.This works fine using my Access version(see code below), but as SQLS2k cannot use "IIF", I tried to replace these bits with "CASE/WHEN/THEN/ELSE" lines, which does n ...

DISTINCT problems with ORDER BY random function

Guys,I have a problem trying to incorporate this SQL to show results in the program made in VB6, the purpose of this one is to show random results each time anyone enter to the window, however Access cannot display anything, it say ORDER BY have a problem with DISTINCT function....I have done a VB M ...

2 values from 2 sub-querys to one row

I have two sub-querys that I need to join together;-both sub-querys on the same row (provided the matching fields match)-if they do not match, take the rows separatelyFurthermore, I have got two values, A and B, table 1 holds values of A, where table 2 holds the values of B.I need to get both of the ...

Optimizing text file writing in VB 6.0

I am completing the coding of a utility that reads data from a database and writes the returnset to a text file. The writing to the .txt file is performed by the client. The text file can range in size from 1 line to 20 million lines. When a file is less then 100K, the following code works fairly ef ...

how to use SQLSetConnectOption API with RDO

I am facing problem while opeining recodset with SP with Sybase databaseIts cursor gets closed on server itself and I get ErrorRun-time error '40088':No open cursor or cursor closed.Can anybody tell me how to solve this problem or how to use this APIto resolve issue Code is somewhat like this Set nN ...

migrating views from ms access to mysql

i migrated an ms access database to mysql with the mysql migration toolkit. i encountered several problems with views. now i want to pass on information about the problems and how to solve them. hopefully this is helpful for the next version of the migration toolkit. the following shows the same que ...

Another access to mysql query

I have problem with the following query: SELECT tblCustomers.mcID, tblCustomers.mcFirstname, tblCustomers.mcLastname, tblCustomers.mcType, tblContactInfo.ContactType, tblContactInfo.ContactDetails, tblContactInfo.ContactDefault, Sum(iif(isnull(tblClaimed.Points) = true,0, tblClaimed.Points)) AS SumO ...

Help with Record formating

I have a table which contains customers' records. My goal is to print out the customer's address in a nice format. The customer's record contains address line 1, address line 2, address line 3, address line 4, city, state, zipcode, country. If any of the fields is blank or null, I don't want to prin ...

multiple if statements in a formula?

is there anyway for me to place in more then one if statement in a formula??like right now I'll give ya the basics what I have..or is there away to do what I need to??here's the line:iif(isnull({RECEIVABLE_LINE.CUST_ORDER_ID}),{RECEI VABLE.PREPAY_ORDER_ID},"")And what I need to do..is have ...

Datatype mismatch in function object :function1

I use vb 6.0 , Sql server and Data report . When i am go to show the report there is a problem.The Code is following. Private Sub cmdShow_Click()On Error GoTo ErrorhandlerDim cn As New ADODB.Connection' Dim rs As New ADODB.Recordset' Dim rs1 As New ADODB.Recordset' Dim rs2 As New ADODB.Recordset' Di ...

Problem displaying data in a Flexgrid in VB6

HiI am trying to display a table from Access in a Flexgrid.For some reason, the flexgrid only displays the first record from the table and doesnot show any other record!Can someone please help!!Here's the code:Set cmd.ActiveConnection = conncmd.CommandText = "SELECT WFJob,JobTitle,FLSA FROM Del ...

Null values in queries...

I am relatively new to SQL and Access...I am experiencing a problem that involves 3 queries in my 2000 access db.The first 2 queries each perform a different multiplication calculation, and both work fine when I run them individually. But on the third query, I am trying to sum the values derived in ...

Help! I have Access and I can't get up

I am having a problem with writing a modified querywithing a report field. i have a letter that pulls info from a dear field:=IIf(IsNull([Contact 1 Dear]),Trim("Dear Contributor:"),IIf([Contact 1 Dear]="",Trim("Dear Contributor:"),Trim("Dear " & [Contact 1 ...

more problems with odbc

Anyone know whats it's playing at this timeSELECT BoM2.PPART AS Parent, IIf([BoM2].[QTYPA]=0, IIf(BoM2_1.QTYPA=0, BoM2_2.CPART, BoM2_1.CPART ), [BoM2].[CPART] ) AS FirstCom, [BoM2].[QTYPA]+ IIf(IsNull(BoM2_1.QTYPA), 0, BoM2_1.QTYPA )+ IIf(IsNull(BoM2_2.QTYPA), 0, BoM2_2.QTYPA ) AS Weights FROM (BoM2 ...

is there a easier away of tracing X to X realationships?

basicly have a product and that product is made of other products so i have a 1 to many non obligitory relationship to it's self for example a DVD product is requires plastic, colour, film, pallet, srink wrap, a carton,... but the plastic is also made of multiple products and onnow to trace it i've ...

In operator with parameter

I have a problem with the following query SQL := PARAMETERS [Which Currency do you require ?] Text, [What start date do you require ?] DateTime, [How many days forward ?] Short, [Which Department do you want ?] Text, [Which NOSTRO do you require ?] Text;SELECT DISTINCTROW TSY_CASHFLOWS.TCF_CASHFLOW_ ...

ORDER BY statement

MS Access 2002SQL = "Select * from DomainsAndIPs Order By Service_Name ASC" I used the following query to sort results ascending, however in some records the Service_Name field is empty and those show up first. Is there a way to still sort alphabetically but have the records with the empty ...

Null values in Cross Tab queries

I have a crosstab query that produces a table with a large number of Null values. They prevent me from summing up the crosstab collumnar fields further down the road without doing dreaded =iif(isnull(field1),0,(field1))Does any one have any suggestions on how to handle possible Null values in MS Off ...

Invalid SQL statement?

I'm trying to link to an Access query by using ADO.Here is my code:Dim conndb as ADODB.ConnectionDim rsqryCAPAmount as ADODB.RecordsetSet conndb = CurrentProject.ConnectionSet rsqryCAPAmount = New ADODB.RecordsetrsqryCAPAmount.Open "qryCAPAmount", conndb <-------causing the errorthe err ...

Problems in redrawing fields in form

with MSAccess2000 i've made a form with columns based on a union query and am stuck by this problem:on PCs with Win98 or WinME operative systems the form works fine.on Asus notebooks with Win2K the form works fine.But...On a PC with Win2K the form has problems in redrawing text fields; if, while the ...

#error in report

When there is no entty at all (when the query is empty, I get “#Error”in the summary control.Can some help me find a way of getting a zero instead.This problem has been on the forum in januari 2003 with topic IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))???? izyrider give this explan ...

Hiding Query fields

=IIf IsNull( Me!NameOfControl1) ThenMe!NameOfControl1.Visible = False Else Me!NameOfControl1.Visible = True End if is it possible to run this code inside a query so that the query can run and if a criteria in the query=isnull then it will hide that field (not show it in the query output)? ...

creating another calculation from a sum calculation

Can the results of a "SUM" calculation be used to perform another calculation in an Access report?I use the following to determine the % of current paid of the current owed.=iif(isnull([CURRENT PAID]) or isnull([CURRENT OWED]),null,iif([CURRENT PAID]=0 or [CURRENT OWED] = 0,0,100*[CURRENT ...

Conditional prompts on report

Have got a field in a table which may or not be blank. I want this field to appear on a report and if it is blank at point of printing them prompt for a value, if the field already has data don't prompt.Can do it in code on the format page event but was wondering whether a far simpler way I have not ...

Simple criterion not working

I have several fields in a select query, two of which are "DueDate" and "CalDate". "CalDate" is calculated using an Iif statement involving several other fields in the table:CalDate: IIf(IsNull([Fu8]),IIf(IsNull([Fu7]),IIf(IsNull([Fu6]),IIf(IsNull([Fu5]),IIf(IsNull([Fu4 ...

Text Box Field Referrals

If I put the following code into the ControlSource of a Textbox, it returns "#Name?" when the form is run.=IIf(IsNull([RECORD]![CREATORUSER])," ","Created by: " & [RECORD]![CREATORUSER] & " on " & [RECORD]![DATECREATED])If I change the code to:=IIf ...

Access 2002 - Object Doesnt Support this method or property Err No 438

In Access 2002 Application when we are trying to add node to treeview in following fashion it gives following error can anybody tell me how to get rifd of it maintaing same code or minor changeObject Doesnt Support this method or property Err No 438Here tvw is Treeview Control called from another Pr ...

in Access TreeView doesnt support Add method??

In Access 2002 Application when we are trying to add node to treeview in following fashion it gives following error can anybody tell me how to get rifd of it maintaing same code or minor changeObject Doesnt Support this method or property Err No 438Here tvw is Treeview Control called from another Pr ...

JOIN in Access problem

Anyone know how to get this quesry to work in MS Access?It tells me this Join expression is not supportedSELECT P1.FirstName + IIF(ISNULL(P2.FirstName), '', ' & ' + P2.FirstName), C.FlatHouseNo, C.StreetName, C.TownCity FROM (Contact C INNER JOIN Person P1 ON (C.ContactID = P1.ContactID)) LEFT J ...

CR LF in access report

how do I put a CR LF in text field of report ?I add additional text to a text box, but want it on a new line.The text box contains:="bit of text" & IIf(IsNull([TestComment]),"","CHR(13) & [ Notes: " & TestComment])this gives me a little box before the " ...

Dlookup across multiple tables

I need to be able to use DLookup to look for data in two separate tables.Code for DLookup utilizing just one table (this works):30day: DLookUp([tbl_Ticker_Sheet01].[ticker],"[tbl_030day_Sheet01-a]","numb=" & [Forms]![frm_HistData1]![txt_MaxNumb])What I thought would work for ...

Join Query

I have two queries one is called "QryInfantPending" SELECT Count(tblInfants.InfantID) AS [Total Infants], IIf(IsNull([tblInfants]![EnrolledDate]),"Pending","Enrolled") AS InfantStatusFROM tblInfantsGROUP BY IIf(IsNull([tblInfants]![EnrolledDate]),"Pending", ...

Help ! SQL problem in mySQL

The below, using a stored query, works fine in Access but not in mySQL, which doesn't support it ( sigh ).Currently, my query is:SELECT PLogin, PID, PDate, PTitle, PApplyOnline, PExpiry, PStatus, IIf(IsNull([Nbr]),0,[Nbr]) AS NbrCVFROM Posting LEFT JOIN ActiveApplicationByAPID ON Posting.PID = Activ ...