I created a personal database for keeping track of my meeting schedules. My table has Date field, Time field and Subject field. I have made several entries. On a given day, I have several meetings at different times.
I created a combo box in my form to choose the Date and see that day’s meetings. The meetings list of the date chosen by combo box should appear in my form. But here I think I made a mistake. Each record has date, time and subject. When I click the combo box, the drop down list shows the same date many times (as I have entered the same date but different time and subject for each record). How do I make the combo box display the date only once and not same date several times? Do I have to change the table set up? Please guide me.
I have looked but have been unable to find the answer in past posts and this must be a common problem. I wish to include all records up to and including a date passed in the prompt "Criteria: <=[enter end date]". I changes the date format in the back end to short and I am in the US. However I get only dates less then the the prompted date. Thanks for any help Jim
I am trying, and getting nowhere...so would appreciate your help... to create a query where records with dates from 8 weeks, or two months ahead appear in the query.
That is I have contracts ending in two months in my table and I want to run a query on who I should call now to renew contracts.
I have a contract end date in table.
I have tried >=DateAdd("m",2,Now())<=DateAdd("m",2,Now()) only to get all sorts?
>=DateAdd("d",60,Now())<=DateAdd("d",68,Now()) only to get all sorts of things appearing?
Anyone that could help would be great. I have contact info in a TBLCONTACTS and linked to TBLMOBILES via COMPANY_ID
I am using Access 2007 and I have had a curious issue arise.
I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)
When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)
The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.
I Have a table that I collect data for numerous ID's. One ID may have 5 dates that is was serviced. It is due to be serviced at set intervals which is calculated from the most recent service date. I'm calculating the next service date in a text box with the DateAdd func. =(DateAdd('m',[Cal Freq],[MaxCalDate])) with the MaxCalDate being a Max func in a different text box for selecting the most recent sevice date. I'm trying to update the [Next Due] field in the [Master Asset] table with the calculated next service date so I can do queries on items that are due service within a date range. If I do an Nex Due Expr1: in the queries to calc the next due service date then do a criteria on that Expr1: such as >=[Start Date] And <=[End Date] it states datatype mismatch? Is there a way to select a requested data range on a calculated type date/time? Or How can I update the [Next Due] filed in a different table?
I am currently trying to create a query that will allow me to do the following:
Prompt the user with a pop up box, asking:
Please enter start date Please enter end date
It will then select everything from my table between those two dates (this works), and then it will ignore all those items that have a time after 7am or before 7pm (this works), but I need it to also select all entries that fall on a saturday or sunday. Now because my raw data comes in the form of (australian dates): 7/09/2005 9:39:10 AM, I am using an update query to split the field into two fields: DateFrom and TimeFrom (both are date/time fields). I then need to create another field called Day, which takes the data in DateFrom and converts the date to dddd (using Format(Date()) i assume), giving me a field with the day name in it, ie: Sunday.
I can then use that to differentiate between weekdays and weekends.... as I use MySQL and PHP more often than I do access, I have no idea how to do this.
So in short:
How do I (this is what i think i need):
Select * from tblData WHERE Day!=Sunday OR Saturday AND TimeFrom > 7:00pm OR TimeFrom < 7:00am
Select * from tblData WHERE Day=Sunday OR Saturday
Both of those selections, will of course need to be performed within my date constraits specified by the user.
I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales). It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!
My problem is as follows:
I have a form for my products and a subform for the suppliers, each product can have many suppliers.
I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(
can anyone tell me how to select only the supplier with the lowest supplier price for each product
I am building a database to evaluate football team performance. I have a table with results (tblResults) where a team will have records in either the Home Team or Away Team fields depending on the fixture.
I want to include a "Team Form" calculation that selects the last 6 games for each team (both home & away) and allocates points to calculate a recent form stat for comparison with the opposition.
I have tried using the TOP VALUES option in a query, but I can't seem to get it to extract the top 6 in descending date order for every team.
I have a database that allows the user to order clothing items. Each size of each item has a different stock number.
I have [StockNo] - [StaffNo] - [Qty] i need to generate [OrderNo].
The problem i have is that [OrderNo] is incremented after every five five items (ie[StockNo]). This gives me my first problem of selecting just the first five records of a table in order to allocate an order number.
The second problem i have is that in order to allocate an order number, i have to summarise the [Qty] by [StockNo]. This gives me the opportunity to select the order number but i lose the individual [StaffNo] information. This information, together with the [OrderNo] is needed for a master order table.
I have created a crosstab query which summarised the [StockNo]but how do i retrieve the [StaffNo] information from the column headings.
I am new to Access and would be grateful for any help or comments as to whether i am going about solving this problem in the best way.
ok so I want a form that has a combo box with [item_id, item_name] on each row that i select. when i select one it displays the relevant quantity and allows me to edit the quantity.
I'm trying to write an app that will allow the user to search for records based on database fields, then select a subset of those records to be manipulated by other functions. The VBA book I'm reading led me to believe that a RecordSet would be the best way to store this subset of records, but searching around on the web has pointed me toward using a DAO.QueryDef. I can't seem to get either method to work!
Here's a quick overview of the app: everything is placed on one form. I have two tab controls that make a sort of upper and lower set of pages. One of the lower pages has the search functions. The user enters his search criteria into one or more textboxes (correlating to database fields, i.e. Last Name, SSN, etc), then hits the Search button. This should query the database, then populate a listbox with the search results. The user can then select one or more records from the listbox, hit another button, and the selected records are copied to another listbox on a page on the upper half of the form, where they can be further manipulated.
Can someone advise me on the best objects to use to accomplish this? A short code sample would be awesome.
By the way, does VBA have some sort of online API reference (like Java)? I know that MS Access has the object browser, but it doesn't give descriptions of the objects, nor does it list methods that can be invoked on them. Could someone also point me towards some good programmer's resources?
I am using an unbound box on the top of a form where I would like to select a clients name and have the form populate with their info. My problem is that the box will pull up the record by last name, but only pull up the first record with that last name in alphabetical order.
Example, If I have the following names in my database
Ben Smith John Smith Tim Smith
If I select "john smith" from the drop down "ben smith" will show up, if I select "tim smith" "ben smith" will still show up. If I select "ben smith" the correct record would show up.
We have product orders that get held in process for a variety of reasons (contract not signed properly, missing drawings, etc). It is our Order Processing Rep's (OPR) responsibility to review the requirements, follow up with the salesman, get the proper documents together and get the order released for product assembly. We capture the date the order was originally held with a date-formatted text field, and then we later capture the release date with a similar field, entered by the OPR.
The OPR is graded on the average time it takes to release a held order. Since they can enter text into the release date field, they can fudge their metric. I'd like to fix this by having a button on the form say "Order Released" and, when it is selected and verified by a second click, the date that occurs is captured in the table under "Release Date" but is hidden from the OPR's ability to edit it.
Can this be done? Any help is greatly appreciated!!
I want to select a subset of table called TRP. It should extract me all records
which got the property Valid to date < enddate.
enddate is a date selected by the user in a form .
Somehow it doesn't recognize the enddate in the following :
Code: Public Sub test() DoCmd.SetWarnings False DoCmd.RunSQL "SELECT TRP.* INTO [TEST] " & _ " FROM TRP " & _ " Where TRP.[Valid to] < enddate" DoCmd.SetWarnings True End Sub
I tried the Crystal Reports forum, but no responses. Can anyone see what I'm doing wrong with the below statement in Crystal Reports 8.5. I'm trying to select reecords that exceed 7 days based on a Crystal Reports parameter. The parameter has the format of 2/27/2006 (Discrete value). The 'LastRouteDate' has a format of 'datetime'. Thanks ! ! !
I have a problem I should be able to solve, but I can't think my way around it.
I need to automate a series of tasks to perform the following:
1. The user clicks a button on a menu form and arrive at a select records form. 2. The user makes choices regarding the contents of certain fields (blank, not blank, equals a text string, etc.) and presses OK. 3. On the OK click, the fields are applied to a query, the results of which are exported in an excel spreadsheet.
I've been doing step 2 and 3 by hand in that I alter the selections in the design view of the query and save it. I then run a macro that deletes the data in the current table, runs the append query with the new criteria, and creates an excel spreadsheet of the new data.
However, I now have to make this functionality available to a non-tech person, thus the need to create a GUI menu that can launch a form on which the user can select options and with a single click, launch the macro.
I've tried adapting select forms that I've used with forms and reports using the doCmd.openquery, but they will only work for select or crosstabs, not appends. I considered having the form create the query and then qrite the append query off that, but I can't seem to get that to work.
Can anyone give me some direction on where I can take this?
I was wondering if someone could help me. I have a simple table with data that gets updated daily. I need to split the data into 5 equal parts for work distribution. Because the amount of records always changes I can not go by numbering so I need to go by percentage, to split it into 5 ( 20% ) parts. Please help.
I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.
I guess I am having to go the VBA route but dont have a clue on how to do it.
Cheers in anticipation.
Andy.
INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] ) SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2 FROM Schedule WHERE (((Schedule.Departure) Like "man*"));
Is it possible to have a combo box on a form and allow users to select multiple entries from the drop-down list? Thats just the tip of the iceburg. If you can, then I'll probably be asking more questions soon.
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.
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."
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code: Option Compare Database Private Sub cmdCal_Click() Dim sql As String Dim rs As Recordset Dim qdef As DAO.QueryDef Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.
Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.
