Code Help In Selecting Queries.
Dec 14, 2004
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.
View Replies
ADVERTISEMENT
Aug 18, 2005
HI,
I have an input form on which I have a combobox.
The user can choose from the list of products. However, I want to use this input in a code and I need the productid to work with in the code. How can I arrange it that when the user selects, they can see the name of the product and the id (not necessarily), but once the input is chosen only the id serves as input and thus in my code I only use the id ( number)???
Stacey
View 2 Replies
View Related
Oct 13, 2015
I am at my Login Screen, I want it to return to the Login Screen if you select "NO" and Close the DB if "YES"
Here is my current code:
Private Sub Form_Close()
If MsgBox("Would you like to EXIT the Database?", vbYesNo, "Quiting Database") = vbYes Then
Application.Quit
Else
???
End If
End Sub
View 5 Replies
View Related
Aug 11, 2015
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.
View 1 Replies
View Related
Nov 26, 2014
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
View 4 Replies
View Related
Dec 11, 2013
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....
View 6 Replies
View Related
Mar 9, 2014
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).
View 2 Replies
View Related
Dec 18, 2014
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).
View 5 Replies
View Related
Jan 24, 2014
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.
View 1 Replies
View Related
Aug 27, 2013
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])
View 3 Replies
View Related
Oct 18, 2006
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.
View 4 Replies
View Related
Jul 31, 2013
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."
View 5 Replies
View Related
Jun 3, 2015
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?
View 7 Replies
View Related
Jun 26, 2013
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
Aug 30, 2013
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?
View 5 Replies
View Related
Aug 16, 2013
I have 2 table,
Table1 has fields
docno (text)
title (text)
progress (number)
sample data is like below:
doc-001 test1 90%
doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number)
icon(attachment type)
1 image1
2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
Doc-001 test1 90% image1
Doc-002 test2 25% image2
View 2 Replies
View Related
Oct 31, 2014
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies
View Related
Feb 3, 2014
I'll first of all explain the purpose of the query. I've built a database to record the admission details of patients admitted to hospital. Amongst other things, the database captures date/time of admission and date/time of discharge.
The query I am building needs to show patients with multiple admissions, and in particular, the number of days that have elapsed since they were last discharged. Within my query is the following subquery that I had written to show this aspect of it:
(SELECT TOP 1 Dupe.[Discharge Date]
FROM [Inpatient Database] AS Dupe
WHERE Dupe.[CHI Number] = [Inpatient Database].[CHI Number]
AND Dupe.[Discharge Date] <= [Inpatient Database].[Admission Date]) AS [Previous Discharge],
Note: "CHI Number" is a unique reference number assigned to every patient.
I then display in a column [Admission Date] - [Previous Discharge].
This works fine where a patient only has one previous admission. However, where a patient has multiple admission, the subquery always returns that patient's first discharge date instead of their most recent discharge date (because I have used the "TOP 1" predicate). It seems that it should be straightforward enough, however I can't seem to work around it. Anything I try results in errors, and so I keep defaulting back to my "TOP 1" solution.
i.e. What I want it to show is:
Name.......Admission Date...Discharge Date.... Previous Discharge... Days Between Admissions
Joe Bloggs ..01/01/2014 .......10/01/2014
Joe Bloggs ..15/01/2014 .......20/01/2014 .......10/01/2014 ..............5
Joe Bloggs ..27/01/2014 .......01/02/2014 ........20/01/2014 .............7
However, what it actually shows is:
Name ......Admission Date ...Discharge Date ...Previous Discharge ...Days Between Admissions
Joe Bloggs ..01/01/2014 ......10/01/2014
Joe Bloggs ..15/01/2014 ......20/01/2014 ........10/01/2014 .............5
Joe Bloggs ..27/01/2014 ......01/02/2014 ........10/01/2014 .............17
View 2 Replies
View Related
Jan 30, 2008
If one has a query and vb routine that does the same thing, in an overall Access environment, is there any advantage to using one over the other ?
Just curious.
View 2 Replies
View Related
Feb 19, 2008
As an example, I have 2 separate queries:
SELECT Orders.QtyAdd
FROM Orders
WHERE Orders.ProdID = 1;
SELECT Output.QtySub
FROM Output
WHERE Output.ProdID = 1;
What code do I use to subtract these 2 queries?
And also, where it says .ProdID = 1; what is the variable so it picks the ProdID from a basic single row form being shown? I guess it doesn't follow the Table.Field pattern?
Thanks, hope that isn't confusing.
View 4 Replies
View Related
Feb 14, 2007
I have a problem where I can create queries in code using functions such as Left() and they will work fine on my clients machines with a complied MDE file but if I try to use the same function in a saved querie they get an error: "Function is not available in expressions in query expression..."
The Queries work fine on my machine but not on those using Access Runtime. From my research it appears to be a problem with them not having the correct Reference on their machine. If that is true then which Reference do they need and is there away of installing that Reference by code?
Thanks for your help....
View 1 Replies
View Related
Apr 13, 2015
I need displaying the max date sql code below;
PHP Code:
SELECT TblDietPlan.DietPlanID, TblDietPlan.ClientID, TblDietPlan.MealDate, TblDietPlan.MorningSnack,
TblDietPlan.AfternoonSnack, TblDietPlan.EveningSnack
FROM TblDietPlan
WHERE (((TblDietPlan.ClientID) Is Not Null) AND ((TblDietPlan.MealDate)=Max()))
ORDER BY TblDietPlan.MealDate DESC;
View 4 Replies
View Related
Apr 18, 2013
I'm a having a problem with a Yes/No datatype. Fundamentally, my code looks like this:
strSQL = "SELECT * FROM tblAddresses " & _
" WHERE YPID = " & Me.Parent.IDNUMBER & " AND CurrentAddress = yes"
Set rs = db.OpenRecordset(strSQL)
Now this statement returns 3 records even though only 1 Current Address exists.
false also returns 3 records
true also returns 3 records
no also returns 3 records
-1 also returns 3 records
0 also returns 3 records
1 RETURNS 0 records!!
If I look at the table in Access, I only have 1 CurrentAddress record for my IDNumber.
View 1 Replies
View Related
Mar 11, 2014
Why the code is showing error.
Private Sub Ref_Click()
Me.Ref.DefaultValue = Date
End Sub
I want that when I click the Ref field in the form the date field in the form get populated with todays date
View 3 Replies
View Related
Oct 4, 2013
I'm fairly new to SQL and I'm trying to input data into this form I've created but it tells me that "this Recordset is not updateable". Here's my SQL code, I'm not certain this is the problem though.
Code:
SELECT [Annual Instructor Report County Summary].*, [Camps, SS Events, Events, Total Members].*,
[Archery Members].[numArchClubs]+[Archery Members].[numArchNon4H]+[Archery Members].[numArchCamp]+
[Archery Members].[numArchSSEvent]+[Archery Members].[numArchEvent] AS totArchMem, [Rifle Members].
[Code] ....
View 6 Replies
View Related
Aug 21, 2013
i have a combo box ,and when i should select "all",this button after update code should show me all the column of table test ,but looks like for no reason the record set shows nothing and its not working.
Code:
Private Sub cboTaskListName_AfterUpdate()
'On Error GoTo cboTaskListName_AfterUpdate_Err
Me.Refresh
Dim db As DAO.Database
Dim SQL As String
Dim rs As DAO.Recordset
If Me.cboTaskListName = "111111" Then
Set db = CurrentDb()
SQL = "SELECT no1 from test"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
End If
End Sub
View 1 Replies
View Related
