I am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work
Code:
CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
I am working on a hired plant database and want to limit the combo box on the hired form to only list items not on hire.I thought I could do this using the IssueDate and ReturnDate fields within a query with an IIf statement forcing the Yes/No field to choose Yes when there is a date in the IssueDate field and No when the ReturnDate field has a date entered.
View 14 Replies View RelatedI've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
.fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration
ORDER BY t_asset_personnel.LAST_NAME;
I'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.
I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.
My SQL for the query is currently:
Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)
Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch! Location1))<>False)
And ((Services_1.Location)=IIf(forms!ServicesRCSSearch !Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location2))
And ((Services_2.Location)=IIf(forms!ServicesRCSSearch !Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location3))
This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.
I have two tables each with an ID field (autonumber/PK/No Dup etc).
I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.
What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.
Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)
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?
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.
A query TaxType_Query produces the following results
TaxType; FromDate; ContactID, FullName
W2; 7/1/15; 1, Tom
1099; 4/1/15; 1,Tom
W2; 1/1/15; 1, Tom
W2; 1/1/10; 2, Dick
1099; 1/1/09; 3, Harry
I need the query to show only the rows with the maximum FromDate for each ContactID (result would be the 1st, 4th and 5th rows only). I assume there is a quick SQL statement that will work better than Totals / Max criteria method. If I set the FromDate criteria to Max the TaxType field is a problem as they have different values I cannot group by that field.
I am needing help with how to set up the code to select different queries based on different conditions.
The database is to be set up so that I can pull studies at different time points based on if they are under long term, accelerated or stressed conditions. When this condition is selected there are only certain time points which are to be run,
example.....
Long term: 3,6,9,12,24,36,48 and 60 months.
Accelerated: 3,6,9 and 12 months.
As you can see I have the dates calculated for all of the weeks and months that the sample could be pulled at, Inventory Transactions Table. And I am able to generate throught the Pull Dates Form a list of only the records to be pulled under Long Term conditions. I would like to have the form set up so that when I choose the Accelerated condition that it would pull from another Query like the Pull Dates Long Term, but only have the Accelerated time points listed.
I am not sure if I need an If/Then statement and if so how do I set this up.
I have text boxes on a Form that I need to update with figures each week.
I have tried to have the entire field selected by right clicking with the mouse but I keep getting an error.
The coding I am using in the 'OnClick event' is:
Private Sub Ctl1_Click()
Me!R1.SelStart = 0
Me!R1.SelLength = Len(Me!R1)
End Sub
R1 being the txtName.
The errors I have received are Object doesn't support this property or method.
I have tried to replace the Ctl1 with R1 but still get errors.
Can anyone please shed some light on what I am doing wrong.
I am using Access 2002.
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54
Maths 69
Maths 41
English 71
English 55
Art 44
Art 43
Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
Maths 69
English 71
Art 66
I have a table containing a list of dates in "MMM YYYY" text format from "Jan 2010" to "Dec 2020".
I would like to restrict the list from the first record (Jan 2010) to the current month using today's date (eg in this case Dec 2013).
I know about
Code:
Format(Date(),"mmm yyyy")
And have tried the criteria
Code:
<Format(Date(),"mmm yyyy")
But doesn't work and haven't got much further....
I am doing an outline design of a database system to handle the results from an archery competition in a couple of weeks.
Currently I have a table with a primary key to hold the information on the competitors (name, age etc.) and this is linked to a table via a foreign key (the archers name) to enable multiple scores to be recorded against each archer.
For the purposes of the competition only the first score shot each day is valid and so I am using a yes/no box to filter out these results.
What I would like to do is then filter the results such that only the largest of these first session scores is displayed for each archer. (i.e if archer A shoot 578 on day 1 and 585 on day 2 i would like the database to only report the 585, and similarly for the other entries).
I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):
--------------------------------
Name Result Date
--------------------------------
Bob 5 1/1/2014
Bob 6 2/2/2014*
Bob 7 3/3/2015
Bob 8 5/4/2015*
Len 5 1/1/2014
Len 6 2/2/2014*
Len 7 3/3/2015
Len 5 7/4/2015*
---------------------------------
how I would write a query that gives me the latest result that each person earned in each year (I put a * symbol against the rows above that I am talking about)?
Bob 6 2/2/2014
Bob 8 5/4/2015
Len 6 2/2/2014
Len 5 7/4/2015
(The database is normalised and this table has an ID field).
I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.
Respondents includes
RID
Name
PostCode
Questions
QID
Question
Answers
AID
QID
RID
Answer
The answer table contains answers to all the different questions.
How can I query this to compare questions ? grouping answers to spot trends in the data
So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.
Any help would be much appreciated, or even a point in the right direction.
I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.
Thanks :)
I have a form with bound textboxes used for entering currency values. When the form loads the fields are automatically populated with default values. When the user clicks on one of the fields, I would like to automatically select all of the data in the field, so as new values are entered, the defualt value is overwritten. I imagine that I should use the OnFocus property, but is there a command to highlight the data?
-Don
I am trying to combine fields (different columns) into one field. However I am trying to have a criteria for the fieldname. For example I wish only to combine the columns that contain the text 2003 (ie [Value 2003],[Price 2003]). I have tried to use wildcards in SQl ie select * from [query] where * = %2003. I must be missing something because I can't seem to work it out. It must be simple. I have also tried several ways through the design grid with wildcards in expressions. I also don't want to union the columns by using the exact names ie [Value 2003]& [Price 2003] because additional columns may be added.
Any help, extremely appreciated, I have wasted a whole day with this.
Deesy.
I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.
I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.
So, I have three tables.
Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost
Table: t_AllMonths
Fields: MonthStartDate
Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate
Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.
My sql looks like this:
SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);
I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."
After running a query is it possible to select just one record and place it on a report or print it? If so how do i go about it?
View 3 Replies View Related