Select By Year In Combo Box
Sep 26, 2006
Hi all,
I have a database that use to record the training information, and there are two column that holding 2 date, Training From and Training To.(I use medium date as my Input mask.)
Now i wish to create a combo box to let the user to select by Year and output to a report to print. But how to make the combo box to show just year but not month and day?? eg. I have a training from 10-Dec-06 to 15-Dec-06 and another training from 05-Feb-07 to 10-Feb-07. so now i need a combo box that can show print by year 2006 or 2007 and the output report will showing all the training which trained in year 2006 if i selected 2006, how to make it? any idea?
Thanks!
Regard,
alex
View Replies
ADVERTISEMENT
Aug 28, 2014
How can I select the first record of each year working with a table like this:
Code:
ID, value, date
0, 30, 01/01/2000
1, 40, 03/02/2000
2, 20, 10/03/2000
3, 10, 02/05/2001
4, 20, 09/08/2001
5, 10, 01/02/2001
I'd like to get this result from my Query:
Code:
30, 01/01/2000
10, 01/02/2001
View 2 Replies
View Related
Oct 20, 2005
I have a database where all reports are by month. I want a simple way to allow the user to select the month/year and want to know if I can use the header portion of the calandar control for this (although I can't seem to figure that out). The alternative is to use two combo boxes (Month and Year) and in a hidden text box I concatenate them into a date since that is how the report query reads the month (using datediff). I would prefer the calandar control because it is more visually pleasing. If anyone has any ideas or suggestions please !
View 1 Replies
View Related
Jan 15, 2014
i need 2 comboxes. 1 with year and 1 with month. the year is easy just do this in rowsource sql:
SELECT DISTINCT (DatePart('yyyy',uitvoering.begindatumtijd)) AS Jaar
FROM uitvoering;
but the month is a lot harder because it needs to show the months where the year is the same as the combox with year and only show months where there is data in my table. for example i have data for only 3/12 months in a year then i need to show only those 3 months.i made this in vba because i think it can't be done in rowsource with sql code.
Private Sub Tekst19_Click()
Dim year As Integer
year = Me.Tekst17.Value
CurrentProject.Connection.Execute "SELECT DISTINCT (DatePart('m',uitvoering.begindatumtijd,,)) AS Maand FROM uitvoering WHERE (DatePart('YYYY', uitvoering.begindatumtijd)) = year"
End Sub
View 1 Replies
View Related
May 6, 2014
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
View 1 Replies
View Related
Aug 12, 2011
I have a form in which you can select a year from a combo box in a subform. I want to create a query to be a subform which uses that combo box to select the year between 1/1/Year and 1/31/Year OR between 12/1/Year and 12/31/Year. The year combo box is in the sub form called YearSubForm and is called Yearcmb. I used Between #01-01-2010# And #01-31-2010# Or Between #12-01-2010# And #12-31-2010# in the query view as a criteria when I wanted 2010, but I want the year from the combo box.
Also, in the form I have a subform that is located on top of a text box. I was wondering how I can have the text box show over the subform. I tried 'send to front' but that did not work.
View 7 Replies
View Related
Feb 28, 2014
I am trying to use a combo box to enter a year value into a query.I want the values to list each year from, say 2000 to the current year, with a default value of "Current".will I have to code each year individually either into a value list or table, and update it each year.
View 3 Replies
View Related
Nov 5, 2013
i have a table with three column Named
1-State
2-City
3-Customer
on a form i m placing 3 combo box for each column how can i filter combo 2 from table after select value from combo 1
View 1 Replies
View Related
Mar 4, 2015
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])
If I select the year from the combo, it works, if I select "ALL" nothing is shown.
here is the full SQL
SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth
[Code] .....
View 5 Replies
View Related
Apr 25, 2006
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
View 7 Replies
View Related
Apr 25, 2014
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"
View 2 Replies
View Related
Aug 1, 2005
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.
This is being done in a query and we're using Access 2000.
Any help would be greatly appreciated.
Thanks,
View 6 Replies
View Related
Jul 3, 2014
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
View 6 Replies
View Related
Jan 10, 2012
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year
y = Number of the day of the year (1 to 366) 'can this show three digits all the time?
hh = Hour in two digits (00 to 23)
nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I would prefer to see 120101304
View 4 Replies
View Related
May 2, 2006
I have a form that uses a drop down to populate another drop down (field#2). The information in field(#2) is pulled from a table where the condition in the first drop down is matched. In effect the form drills down and offers specific choices based on the original condition.
The form works correctly when first opened. If the selection in the first drop down changes the associated information in the other field does not update to reflect the new value to match. The form has to be closed and re-opened before it works properly.
Any ideas?
View 4 Replies
View Related
Sep 5, 2006
I have some combo boxes on a form with values that I have typed in directly.
I would like the user to click on these and the option selected will dynamically change a resultset.
Unfortunately, when I click on the combobox, the optionsare displayed, but clicking on each of the options doesn't do anything... i.e. the option does not become selected.
Am I missing a setting somewhere ?
View 12 Replies
View Related
Dec 11, 2006
I am probably doing this all wrong!
I have 2 forms (linked). One is plan, the other is plan contacts.
The plan contacts form is a continuous form and will hold a record of all the contacts for that particular plan. To select the contact (from that contacts table) i want to show, company, department, name.
As there will be hundreds of contacts i need to narrow this down for the user. My idea was to do this by using combo boxes. The first one will show a list of all companys, once chosen the second one will show all that companys departments and then finally all that deapartments contacts.
Any ideas? I am assuming this can be done through an SQL query?
Thanks
Kev
View 4 Replies
View Related
Oct 14, 2004
I'm using a select query which gets its value from a combo box.The combo box has three choices: Male,Female,Both.
Filtering male and female is no problem. Is there a way to select all in the query?
View 1 Replies
View Related
Oct 9, 2006
I am having an issue with a combo box on a form with the following query. It works exactly as it should, but unfortunatly, I need a little more.
cboASC is off a table, cboQCode is from values, If I don't fill 1 of the combo boxes I get a blank record (just fields).
What I need is an option to choose all Primary Locators and/or all Qcodes for a general report instead of a local report.
SELECT CallsEntered.[Work Order Nbr], CallsEntered.[Date Entered], CallsEntered.[Time Entered], CallsEntered.[Primary Locator Code] AS [ASC], CallsEntered.Headend, CallsEntered.Node, CallsEntered.[Grid Id], CallsEntered.[Q Code], CallsEntered.[Problem Code 01], CallsEntered.[Primary Finding Code], CallsEntered.[Primary Solution Code], CallsEntered.[Cancel Code], CallsEntered.[Scheduled Date], CallsEntered.[Wo Status], CallsEntered.[Date CheckIn], CallsEntered.[Assigned Installer], Calendar.Week, Calendar.Year
FROM Calendar INNER JOIN CallsEntered ON Calendar.Date = CallsEntered.[Date Entered]
WHERE (((CallsEntered.[Date Entered]) Between [Forms]![frmServiceCalls]![txtStartDate] And [Forms]![frmServiceCalls]![txtEndDate]) AND ((CallsEntered.[Primary Locator Code])=[Forms]![frmServiceCalls]![cboASC]) AND ((CallsEntered.[Q Code])=[Forms]![frmServiceCalls]![cboQCode]));
Your time and help is appreciated.
View 2 Replies
View Related
Dec 12, 2005
Is there any way to have a Multi-Select Combo Box fill a single field in a table? I've been messing with this for some time now and just can't seem to figure it out.
Thanx in advance
Karen
View 4 Replies
View Related
Oct 24, 2006
Have a subform:
Name: frmProjectSub1
Default View: Continuous Forms
A combo box within the subform:
Name: cboProjectName
Control Source: Nothing
Row Source Type: Table/Query
Row Source: SELECT ProjectName FROM tblProjects WHERE tblProjects.Status LIKE "*" ORDER BY ProjectName;
Column Count: 1
Bound Column: 1
Visible: Yes
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
OnClick Event:
Private Sub cboProjectName_Click()
With Me.frmProjectSub1.Form
Me.RecordsetClone.FindFirst "[ProjectName] = " & Me.cboProjectName
If Me.RecordsetClone.NoMatch Then
MsgBox "Can't find task " & Me.cboProjectName
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End With
End Sub
Within the continous form, if I name the Control Source as ProjectName, and traverse through the records, the value in the combo box changes to the current selected row.
Problem: In the combo box, I do get the dropdown list of projects, when I click on a project - Nothing Happens! The listbox stays open on the item that I clicked on -- does not close-up and put the selected item in the combobox window. I'm stumped...
View 3 Replies
View Related
Jan 28, 2005
I have a database of customers, and I'd like to be able to pull up a specific record on a form. Currently the only way I know how to do this is by ID number (the primary key). I'd like to be able to search by their last name and first name (of course we have people with the same last name), so for instance I'd like to be able to input "Smith" and then have a list of all the smiths to choose from. Is there a way to do this? Thank you!
View 2 Replies
View Related
Mar 14, 2006
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
View 3 Replies
View Related
May 13, 2005
hi All..
I could set the Combobox.Value to one of the values in the combo box and combo box displays that value. But when I try to use Combobox.Value subsequently it does not have any value. I noticed that the value property is populated only when the combox box item is manually selected(highlighted).
How can I highlight the selection in a combo box?
Thanks in advance
View 1 Replies
View Related
Jul 11, 2005
I'm having trouble creating a query.
I work for a company which have a load of cd's which i've put into a database.
the database consists of a table which lists the contents, date created, department etc.
I've created a form where users can select (using combo box's) criteria to search the database.
It works well if the user selects relevant data from each combo box but i have now added a "select all" function to each combo box incase the user doesn't know the specific criteria.
This is where the trouble starts. I cant work out how to create the query so that if the combo box is on "select all" how it will show all records for that particular field.
I have tried to use an IIF function for each field in the query but i always get errors.
Please help.
View 1 Replies
View Related
Mar 14, 2007
Hi,
I currently have a select query that has a where statement to select a value depending on the value of a combo box in a form.
WHERE (((tblSampleSubmission.SubmissionNumber)=[Forms]![frmReportPreview]![SubNumber]))
What I am wondering is there any way that the where statement can be for another form depending on what form is currently active by using an "or" statement?
Basically what I am trying to do is make the db as easy to use as possible and not have to type in the value again.
View 2 Replies
View Related