Query Containing Iif Statement - Scope Issue

Feb 15, 2008

I've come across an issue today that I didn't think was possible in Access or it's possible I've not met these conditions before..

A field containing an if statement in a query is brought into another query. The iif statement then appears to be working within the top query as well, when I am just expecting to display the results of the initial query.

I've struggled to describe this clearly, this is my best attempt so far.

For example

I have a query (Q1) returning 3 rows, the first field is a unique field, the second field is an optional field, the third field is an if statement. eg iif [field 2] is null Then "default" Else [field 2]

Output should be 3 rows: Default value = "No response"

Field 1___________Field 2__________Field 3

1________________Yes____________Yes
2_______________________________No response
4________________No_____________No



I am then joining Q1 to a table (T1) with 6 rows and one field, the unique field.

I want all 6 from T1 and matching rows from Q1.

I am expecting to see this result.


T1.Field 1_________Q1.Field 2_______Q1.Field 3

1________________Yes____________Yes
2_______________________________No response
3
4________________No_____________No
5
6


However, I am getting

T1.Field 1_________Q1.Field 2_______Q1.Field 3

1________________Yes____________Yes
2_______________________________No response
3_______________________________No response
4________________No_____________No
5_______________________________No response
6_______________________________No response

You can see that Q1.Field 3 is now returning a default value for the rows that aren't actually in Q1.

The iif statement is working on the larger dataset when I want, expected it to only work within Q1.


Phew, I hope someone can make sense of that and secondly help me out.

Is it supposed to work like this? (I don't think so)
How do I stop it working like this? - I have made the result of Q1 into a table and linked that to T1, giving me the right answer. However, I just can't believe this is correct as it would stop you using a query within a query.

Thanks in Advance.

View Replies


ADVERTISEMENT

Scope & Limitations Of MS Access And SQL Server

Apr 24, 2008

Hello everybody

My office gave me assignment to find out the scope & limitations of the two Microsoft database applications - MS Access and MS SQL Server. After the findings, we have to chose the more appropriate database for our new project, which will have a humble start but may grow big in a year or two.

I will apreciate the info in facts & figures (not only in general).

View 7 Replies View Related

General :: Duplicate Declaration In Current Scope?

Jul 6, 2012

I am getting a duplicate declaration in current scope error. How do I fix it.

Sub SetupTargetDate()
On Error GoTo ErrorHandler
Dim dtTime As Date
Dim dtTemp As Date
If IsNull(Me.Date) Then Exit Sub
If IsNull(Me.Time_Received) Then Exit Sub

[code]....

View 5 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

Need Help With Query/sql Statement

Sep 22, 2006

Hi all..

Here is what I have:

I have a query that is running a search form. The query is made up of three tables:

1.tblMain
2.tblSpouse
3.tblChildren

When I have the query looking at just tables 1 and 2 it works fine. But when I put in table three my query doesn't work correctly.

Here is what it is doing. On my search form I have a main window that lists all of the entries into the database. I then have two subforms one for spouse and one for children. The spouse now shows this:

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name
Janine
Janine
Janine
Sally
Sally
Sally

(two wives, 1 current, 1 past)

And the children show this

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name

David
David
Mines
Mines
Foy
Foy

And the one entry that does not have any children is no longer showing up in the main window with the other entries.

Here is the sql of the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber, tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName, tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber, tblChildren.LName, tblChildren.FName, tblChildren.MInitial, tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber = tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And [tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;

Sorry for the long post but I wanted to make sure most if not everything was covered.

Thanks
R~

View 14 Replies View Related

Query V SQL Statement

Mar 29, 2007

If I had a Form 'FrmForecastReview' Based on a Query 'QryForecastReview' I am wondering whether i can dispense with having a query and just use SQL statement in the properties of the form to query and retrieve the data i am looking for?

If yes would this be a technically and/or faster way for Access to produce datasets?

Thanks in anticipation of your pearls of wisdom

View 8 Replies View Related

Help With Statement In Query

Apr 24, 2007

This is my first post and would like help with the following:


I have a warranty database that I have to create adjustment claims. There a re two pricing structures depending on the customer type. One customer type is SH and the other is DD.

I need to create an expression within the query that will allow me to add 1.0639 to the SH pricing (SH*1.0639) for all customers = DD.

So, when I pull a DD customer 1.0639 (6%) will be automatically be added to the List Price.

When I pull a SH customer the price will be the regular list price already housed in the database.

What type of statement will I use here. IF, SELECT or what? and if so, what would it be?


I hope this is clear and appreciate any help or feedback that I can get on this.

Thanks

View 2 Replies View Related

If Then In A Query Statement

Dec 5, 2007

I am stumped and need some help I have a simple select query used in a combo box to filter what is shown. As shown below.

SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition]
FROM [Task Type Drop]
WHERE ((([Task Type Drop].[Task Category])=[Forms]![Task Category and Area Look up]![Combo158]))
ORDER BY [Task Type Drop].[Task Type];

Is is possible to only have it use the criteria if a nother field [Combo158] is not null?

Simply put I would like it to be if [Combo158] is null

SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition]
FROM [Task Type Drop]
ORDER BY [Task Type Drop].[Task Type];

If not null

SELECT [Task Type Drop].[Task Type Id], [Task Type Drop].[Task Type], [Task Type Drop].[Task Category], [Task Type Drop].[Task Type Definition]
FROM [Task Type Drop]
WHERE ((([Task Type Drop].[Task Category])=[Forms]![Task Category and Area Look up]![Combo158]))
ORDER BY [Task Type Drop].[Task Type];

View 2 Replies View Related

Help With IIF Statement In Query

Dec 18, 2007

I have a database where I list prices for products. The prices are based on the type of customer.

I have a table that lists
Customer
Customer Type (can be DD or SH)

My other table with is the tire table has the following:

TireID
ITem Number
Description
Tire Price
Sh Price
List PRice

If the customer type is a DD - I need to use the List Price
If the customer type is SH - I need to use the SH Price

Here is the statements I've tried, but none of them are working..

Tire Price: IIf([customer type]=[dd],[SH Price],IIf([customer type]=[sh],[List Price]))

Tire Price: iif ([customer type]=”dd”, [SH Price], iif([customer type]=’’sh”,[List Price]))

Tire Price: IIf([Customer Type]=[DD],"[SH Price]")

I was using this IIF statment in my query but it is giving me a parmeter box with dd and then pulls up another box with sh before running the query. The query is not working.

I'd appreciate if someone can help me fix this issue.

Thanks

View 3 Replies View Related

Query Or SQL Statement

Jun 3, 2005

I want to show cell values in a form from a table the form is not sourcing data from where the primarykey of both tables commensurates and tried do this thru a query, but id doesn't work. what would be the expression in the text box or the SQL statement for this.

thanks in advance

View 3 Replies View Related

Help W/a Statement In A Query

Apr 2, 2008

I need to know how to take the following statement:

TOTAL: Sum(Nz([TblPurchases]![Qty])*Nz([TblProducts]![PointValue]))

and modify it so that any records in TblPurchases where the field called "ProductID" is =42 is excluded from the calculation.

If anyone can help me out, I would sure appreciate it!!

View 6 Replies View Related

Tax Band Query Statement

Jul 25, 2005

I am trying to convert an excel formula into a new access query statement.

The statement itself will refer to a tax percentage column within the same query.

Example of excel formula:

=IF(Tax Percentage>22,40,IF(Tax Percentage>10,22,IF(Tax PercentageN2>0,10,0)))

The output I hope to achieve is:

If Tax Percentage is greater than 22 then = 40
If Tax Percentage is greater than 10 but less than 22 should equal 22
If Tax Percentage is greater than 0 but less than 10 should equal 0.

Hope this makes sense?

thanks

View 10 Replies View Related

Update Statement Or Query From VBA

Dec 24, 2004

I am trying to update the fields of a table from comboboxes and textboxes on a form. I don't want all the fields to receive data from the objects on my form directly. Some of the bound controls are not visible and data would be entered into them from visible comboboxes instead. I am not sure exactly how to do that. I tried to devise a query that would take the values from the comboboxes and equate them to those of the hidden controls, but that didn't work. I tried inserting an update command in code but that came up with an error. If some one could help me I would be thankful.

View 7 Replies View Related

IIF Statement Is Acting Up In A Query

Aug 12, 2005

I have a query which is based on 2 tables (Log2000 & Customers) and criteria is entered by the user via a form called 'SearchMarkets'.

In concept, if the user chooses a 'Market' on this form, the query uses that value, otherwise it runs everything. My SQL looks like this:


SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE (((Customers.ID)=IIf([Forms]![SearchMarkets]![Market] Is Null,([Customers].[ID]) Like "*",[Forms]![SearchMarkets]![Market])));


For non-null values it works grand but the ([Customers].[ID]) Like "*" part isnt returning anything. However this piece of SQL does work when used on its own like this:

SELECT Log2000.[Call ID], Customers.ID
FROM Customers INNER JOIN Log2000 ON Customers.CustomerNumber = Log2000.Customer
WHERE ((([Customers].[ID]) Like "*"));



Can someone please help me overcome this issue?

Thanks in advance!

View 4 Replies View Related

If Statement In A Query Not Working?

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

Too Many Brackets Query Sql Statement

Mar 1, 2006

WHERE (((zEnquiries.Enq_Forname) Like '*' & Forms!Enquiry_Search!Search2 & '*'))

this statement im sure has too many brackets.. can someone correct it for me

View 2 Replies View Related

Iff Statement For Query Criteria

May 30, 2006

A database I'm creating contains records of events within an academic year. There will be many events in the table, and I want to create a query to show me only those events in the current academic year.

I figure that I need to use some sort of iff statement to make this happen; something which says:

IF the current date is before 31-Aug-CurrentYear
THEN display records in the range 01-Sep-LastYear and 31-Aug-ThisYear
ELSE display records in the range 01-Sep-ThisYear and 31-Aug-NextYear.

I've had a go at writing this, but can't get my code to work. Can anyone help?

Thanks in advance,

Gary

View 8 Replies View Related

Help With Search Query Statement

Oct 16, 2006

Hi,
I would like to display records on a webpage and am unsure of the query statement and would like to seek some advise.
I have a lists of contact numbers(home, mobile) and they are from various Sports groups and different teams. Captains from the different sports group will be able to view the details of their teammates of the respective sports they belong to.
An example of what should be returned is shown in the attached file

Database Info:
Surname
GivenName
Sports
Team
HomeNum
MobileNum

Thank you.

View 1 Replies View Related

Query Help Using A Iff Statement And Relationships

Jun 12, 2007

Here I have three tables.
Control type – has a field control – 1 row
Table1 – has fields: Acct (text), table 1(text), table 3(text) – 8 rows
Table2 – has fields Acct (text), table 2(text) – 4 rows

What I am looking for is to combine Table1 and Table2, replacing table 1 with table 2 if the control type is 4. if its not then it uses table 1.

I was able to do that with the query test except I lose that data in table 3 that doesn’t match table 2.

I would like to have the information kept on Table1.[table 3] and if nothing matches [table 2]

It sounds confusing so I am attaching the database.

The Query test shows it working but losing the extra data from Table1.[table 3]
The Query test2 gives the SQL Err I received when it runs the way I thought It should.

Any help or suggestions would be welcome.

What I am looking for is a way to set a stactic varable that only changes when I set it. and is available for 4 or 5 different queries. run under the same macro.

View 2 Replies View Related

IIf Statement Problems In Query - Please Help!

Jun 13, 2007

Hi there - 1st post so please bear with me!

I have currently got a form set up with unbound combo boxes which draw their list info from seperate tables. My idea was to have a user make a selection from these combo boxes, and then use that data to search (please reference form "Inputs 1b - Generic Search for a Bike" in attached sample file).

When 'Search" is clicked, a query (refer query "Generic Search for Bike") determines the selections from the form, compares it against the master storage table, and I will eventually output it to another form.

My problem lies in the Expression required in the query to read from the input form, but more importantly, in dealing with the Null values if one of the fields is untouched.

The expression I would ultimately like to use is:
IIf(IsNull([Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear]),[YearID] Like "*",[YearID] Like [Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear])

so, if there is a value selected, search with that as the criteria, and if the field has been left blank (Null) then search as if the criteria is a complete wildcard - ie return all results. I have tried many different expressions, with Nz(), Like, etc, to no avail.

I'm baffled, because when I break down the expression into it's component parts and run them as the query (eg the IIF returning simple values, or just the Like [Forms]![Inputs 1b - Generic Search for a Bike]![SearchYear], etc) they all work. It's when the expression is combined that no results are returned from the query.

Incidentally, is there a way of running through the expressions in Expression Builder step by step, as with VBA code or Excel functions?

I'd greatly appreciate any help anyone can provide!

Cheers

View 3 Replies View Related

Nested Iif Statement! Not Sure Why This Query Will Not Run...help!

Aug 12, 2007

I'm wanting to run an update query but first I want to see the results by using a select query to make sure that I'm getting the correct results.

See the following query statement:

SELECT ([Goodrec - All] INNER JOIN WH_ACCTCOMMON ON [Goodrec - All].ACCTNO = WH_ACCTCOMMON.ACCTNBR) INNER JOIN [Trial-GL-Reference] ON WH_ACCTCOMMON.CURRMIACCTTYPCD = [Trial-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode], [Goodrec - All].CURRBAL = IIf(Not IsNull([chgoffamt]) And Not IsNull([partsold]),CCur([grossbal])-CCur([chgoffamt])-CCur([partsold]),IIf(Not IsNull([CHGOFFAMT]),CCur([grossbal])-CCur([CHGOFFAMT]),IIf(Not IsNull([partsold]),CCur([grossbal])-CCur([partsold]),[grossbal]))), [Goodrec - All].PCTOWNED = IIf(Not IsNull([origamt]) And Not IsNull([partsold]), CCur([origamt])-CCur([partsold]/CCur([origamt]), IIf(IsNull([partsold]), [grossbal]));


This is the error that I receive each time I attempt to run it and I'm not sure what I'm missing.

Error message:

MICROSOFT OFFICE ACCESS
Syntax error(missing operator) in query expression '([Goorec-ALL] Inner Join WH_ACCTCOMMON ON [Goodrec-All].ACCTNO=WH_ACCTCOMMON.ACCTNBR) inner join [TRIAL-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode]'


Any help with this issue would be greatly appreciated.

Thanks.

Govmate!

View 3 Replies View Related

IIf Statement In Query Criteria

Aug 28, 2007

Is there a problem with using an IIf statement in the criteria of a query. If ther isn't then is there a problem with using a Between...And statement inside the IIf statement. Or, does anybody see a problem with this IIf statement being in the criteria of a query.

IIf([blinks_test_end].[Blinks]<>[blinks_test_start].[Blinks],([daily_extract].[Reading_Date]) Between [blinks_test_start]![Reading_Date] And [blinks_test_end]![Reading_date],Null)

[blinks_test_end] and [blinks_test_start] are queries that use the table [daily_extract] the query that I am trying to run uses these queries and the table, however, I have nothing joined. The IIf statement is in the criteria of the [daily_extract].[Reading_Date] field.

View 1 Replies View Related

Need Help With Variable Within My Query Statement

Oct 30, 2007

Hi All,

Basically I'm trying to filter records from a view in my listbox based on a value in a text box. As such...

Private Sub Form_Open(Cancel As Integer)

Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = " & Form_frmMain.txtName

End Sub

**Note that this the value I'm trying to pull is from a seperate form.
My statement works fine as long as I don't use a variable ie.
Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = 'Agnew' "

I'm sure it may be a simple syntax problem but I fairly new at this. Any help would be appreciated.

Thanks

View 2 Replies View Related

Can Anyone Modify This Query Statement

Apr 6, 2008

hi im a first time user of access,

can anyone modify this query statement...

select IDno, LName + ', ' + FName as Name, course, Status,
(select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno
)
as TotalBooks
,
(select sum(io.Penalty) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno) as Penalties from borrowers b


i also want to set the TotalBooks and Penalties to 0 when their values are null thanks

View 3 Replies View Related

Simple Query Statement, Not Getting Anywhere...

Sep 20, 2007

I think this would be a simple query but I have not been successful in getting anything to work at this point. So I thought I would through it out to the pro's here! Thanks for any help you might give.

Table looks like this:
EventID EventDate Score EventType
46 09/20/2007 72 1 Day Net
46 09/20/2007 72 2 Day Net - Day 1
46 09/20/2007 68 1 Day Best Ball
48 07/14/2007 69 1 Day Net
48 07/14/2007 34 Nassau


What I am trying to get from this data is the AVG score, but not include Nassau or Best Ball events. So I have used the Not Like "%Nassau%" etc, in ASP (since ASP requires % rather than * like access. The results I would like to see are one score from each DATE avg'd. That means with this sample data we should get...
46 09/20/2007 72 1 Day Net
48 07/14/2007 69 1 Day Net

Avg score 70.5

I don't care to have the Event Type listed, if that causes grouping issues. I just am not getting anything to work. Thanks for any help.

In short If I could get the avg score using only one score a day that is not a Nassau or Best Ball event, I would have to rejoice! Thanks so much.

View 2 Replies View Related

Queries :: WHERE Statement In Query

Sep 11, 2014

I have a Form that is used to print a report and the report is based off of a query.I dont like the WHERE statement in the query. I want the user to enter the employer codes and not have to continue to press enter when they are done entering 5 employer codes.

Example: The want to send letters to 3 Employer codes but after they enter the 3rd employer code the query will continue asking for 21 more employer codes. How can I provide the user a way to not be asked for 21 more employer codes? The current code for the query is:

Code:

SELECT Format([Enter Letter Date],"mmmm dd"", ""yyyy") AS [Letter Date], dbo_partfile.prt_employer_code, dbo_employer.mis_description, UCase([kn_key_name]) AS UID, Trim([prt_last]) AS [LAST], Trim([prt_first]) AS [First], dbo_partfile.prt_middle, dbo_partfile.prt_addr1, dbo_partfile.prt_addr2, Trim([prt_city]) AS City, dbo_partfile.prt_state, dbo_partfile.prt_zip_code, Format([prt_zip_ext],"0000") AS Zip4, Format([Enter Term Date],"mmmm dd"", ""yyyy") AS [Term Date], IIf([prt_sex]="F","Ms.","Mr.") AS Title, dbo_partfile.prt_status, dbo_partfile.prt_local_nbr

[code]...

View 4 Replies View Related







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