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"
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.
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).
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
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?
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.
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?
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.
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];
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.
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.
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.
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.
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 "*"));
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"
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?
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
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.
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!
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.
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.
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
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.
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
