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 RepliesI've created a pop-up data entry form to enter charges by year.
How do I customize the format so users are required to enter the 4-digit year in all year fields.
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] .....
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
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.
I use filter Combo boxes in a lot of places so that users can filter records easily. (Not combo boxes for input).I have them labeled as filters but nonetheless users keep trying to input into them for some reason.How would I add a message box to it so that it states that 'this box is not for data entry etc'.
View 2 Replies View RelatedI 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
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"
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,
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
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
I'd like the user to be able to search a subform only by month and year. We have lots of reports with different days, but none of them are pertinent to searching.
I don't mind if it looks a little bit messy (such as using the calendar drop down and it discounting the day) but I'd like to have it all in one box, preferably where the user can just select ex. 05/2013 and it would filter the subform, showing only reports from that month.
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 RelatedI have a form linked to table tblINCIDENTS. When I create a new incident, I select DATE_LOGGED in a text box. This updates the field in tblINCIDENTS.
I would like to show on the form (and store in the table), e.g.
"Record 5 in Year 2013"
I already had the new record number as an autonumber, but now I created 2 extra fields in the table - recordno and year.
I have a hidden text box which, on DATE_LOGGED.CHANGE, finds the Year() from DATE_LOGGED. I have tried using a Query to find the MAX RECORDNO of YEAR. I managed to display the Max (having manually populated the fields!) in a textbox, but am now going round in circles!
trying to get something working on a form. When creating a new client, due to privacy, we have to create a Unique ID for each one. The ID consists of their initials, the year, a serial number and the location. I have it mostly working except for the year. Let me show you what I have and the results, then what I would like the results to be.
In the control Source of the text box is:
=Left([FName],1) & "" & Left([LName],1) & "-" & [Forms]![frm_20_Enter_New_Client_Info_01]![NICN_Date] & "-" & [Forms]![frm_20_Enter_New_Client_Info_01]![NICN_Serial] & "-" & [Forms]![frm_20_Enter_New_Client_Info_01]![Combo1]
Form Input:
FName = John
LName = Smith
Date = 2014 (Using Date format and 'YYYY') and default Now()
Combo1 = NW
Results: JS-4/2/2014 9:32:51-18-NW
Desired Results: JS-2014-18-NW
So everything is working but I'm getting the entire and I just want the year.
I need to create an "autonumber" field in the following format:
FYYMMXX
Where F is a constant, YY is the year, MM is the month, and XX is an incremented number. So for example, the first record in August of 2013 would be "F130801".
I've been checking other threads but haven't been able to follow them to a resolution. I think there is a way to do this within my form...
How do I increment record id like given below:
2014/1
2014/2
2014/3
And when year ends
2015/1
2015/2
Please could someone advise me how to set the YEAR in a query - all my data for 2006 is fine but I have uploaded some data for today (01 Jan 07) and ALL by queries / reports now show to 31/12/2006 then at the top, instead of the bottom, reads 01/01/06 ?
Regards
Richard
I would like to run a query to return all dates for a year like 2007. I need to ignore the day and month so the user will be asked to enter the year “2007” they require a form list on.
I have used the between date query:- Between [Start Date] And [End Date], and it’s okay but it would be much faster if only one parameter can be typed to return a list of the full year.
Any help would be appreciated, I cant find anything here which helps.
Thanks!
I have two combo boxes in a form. The 2nd combo box is influenced by the selection from the 1st combo box. The record source of the 1st combo box is a table and filters a query OK according to the parameter query I have written. The 2nd combo box correctly lists data influenced by the selection in the 1st combo box via a record source from the same query, but I am struggling to execute/run the query to filter according to the 2nd combo box. I have tried a second parameter criteria in the same query but when I do that it blanks the selection of 2nd combo box.
View 2 Replies View RelatedI was able to enter data into my form but I made a few design tweaks and added another table into the form and now, in form view, I''m unable to enter data into any field.
View 11 Replies View RelatedCan I open a "Enter Parameter Value" dialog box to capture and use the input without running a query?
something like
Code:
ParaBox ("Enter the Hostname", vbEntParameterVal, "Hostname Required")
or
Code:
Set pDialog = Application.ParaValueDialog(mso?????)
I need a short string input from the users at application load, and I'd like to use the "Enter Parameter Value" dialog.I'm about to build a workaround that will invoke a SQL statement to save the entry to a temp table, but it'd be nice if I could just call up that dialog box and store the entry as a VBA variable.
I can not remove the Enter Parameter Value. In the attached date base, go to frmEvents, and double click on one of the shows, and you will get that error. I have deleted lines of code, text boxes, and anything else that is associated with the txtSearchS.
View 14 Replies View RelatedI have a form which contains a listbox sourced from a query and several text boxes where the user can enter filters for the query. The query has references back to the form for the criteria, i.e. the filter for "Company Name" is handled in the query criteria as
Code:
Like "*" & [Forms]![HooversProspects]![SearchText] & "*"
When i exit this form in my un-compiled version it does not pop up an "Enter Parameter" box, however, when i exit this form from the compiled version it pop ups the "Enter Parameter" box for every filter I have.
How i can fix this? Perhaps change the rowsource on the query to null upon exit?
I work for a foundry and we bring in lots of raw material for conversion to alloy. Each incoming lot is assigned a number on arrival and is then broken down and processed in batches, typically 9. Each of these batches is then assigned a number as well, again a consecutive number which also includes a letter (the letter designates the product). Process data for each batch is kept and final QC analysis data is also recorded.
My question is: how do I set up a form on which I input the lot number, customer, and source once but that fills in that info for each row in the table with which the form is associated? After conversion, I would like to be able to analyze the process data by one of those 3 values so I need them filled in in the table (I plan to pull the data from the Access table into Minitab for statistical analysis).
I have a database with three tables:
Participants (with name...), Events (with Event Name, date..., Event Attendance (Participant ID, Event ID, Date).
Relationships all work and reporting works. I now want to create a form for entering attendance into a single event as a batch. I only want to enter the event ID one time and then be able to enter participant ID's for all attendees. The event coordinator will have a tablet running the db at the door and each participant will use their ID card to be scanned into the event.
I don't want the coordinator to have to reselect the correct event between each participants scan but instead to enter the event once and have multiple participants ID's entered for the one event. The date field is defaulting to current date/time [Now()].