General :: Populate List Box With Distinct Months From Date Field

Nov 27, 2012

I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.

Code:
strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null"
Me.lstSampleListBox.RowSource = strSQL

I want to return the month in <mmm yyyy> format.

View Replies


ADVERTISEMENT

General :: Populate Field By Entering Date In Another Field?

Oct 24, 2013

I am wanting to populate a field by entering a date in another field. I am trying to determine age(years, months, and weeks) of something by entering a date in another field. Is that possible in Access?

View 1 Replies View Related

Queries :: Date Criteria - Query Previous 12 Months Including Months With No Data

Mar 22, 2013

I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.

Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:

March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013

...so even if June 2012 has no records, it is included in the query with a value of zero.

This is what I have so far:

WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))

View 2 Replies View Related

Need Distinct Records Of Whle Table But Distinct On One Field

Sep 15, 2006

Hi, Wish if some one could help me ASAP.
I have a table which contains name, tel, email
i need to import only records which have distinct email.
I do need need to import data of all three fields but only which has distinct email.
As there are number of record which are duplicate.
They have different names but same email.
So i need to condition only for distinct email but dump the data in a new table with all three records.
so same names can have different email.
but same email can't have duplicate email.
So need only records which have distinct email.
Please help .......

View 1 Replies View Related

General :: UPDATE Field In Table Based On DISTINCT Values

Mar 12, 2013

Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.

PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1

View 6 Replies View Related

General :: Pulling Certain Names From A Query To Populate A List Box?

Jun 19, 2015

I have a form set up (in a list box) to show our salespeople what parts they have yet to get out of inventory but have a sales order for. The list box shows a list of all the salespeoples names. My manager wants me to show ONLY the salespeople that have populated fields in this list box. The list box currently shows all the salespeople, but I want to see only the ones that have inventory that has yet to shipped. How do I go about this?

View 4 Replies View Related

Populate Field From A List Box

Jun 29, 2005

In my list box I have two coloums, Surname and Christian Name, now can I get both names to go into a text field. I can get one of the names i.e surname or christian name by changing the bound coloum from 1 to 2. But I need both names to go across?

View 2 Replies View Related

Forms :: Auto Populate Date Field Based On Another Date Field

May 13, 2014

I am building an Access database for a client. It is an employee staffing database. With that being said they would like the ability to automatically populate the "T2PPCD" date field based on what is entered into the "Report Date" field. (Same table)

The date is the Monday after 180 days from the report date.

I already know how to get it to auto fill 180 days from the "Report Date" but I'm not sure how to tell it to give me the Monday after 180 days.

View 9 Replies View Related

General :: Populate List Box With Event After Update In Combo Box - Run Time Error 13

Sep 9, 2013

I am trying to populate a list box with an event after update in a combo box. I can get the formula to work using 2 criteria, the problem is i nee to add a third criteria. When I try to add it I get the run-time 13 error.

Here is the code I am trying to use:

Private Sub cboStatusRFQ_AfterUpdate()
Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool.RFQ Contact] " & _
"FROM Consolidated_Master_Req_Pool " & _
"WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool.RFQ Supplier] = '" & Nz(cboStatusRFQ.Value) & "'" And "[cosolidated_master_req_pool.Status] = '" & "[SUPPLIER_RFQ FOLLOW-UP]" & "'" & _
"ORDER BY [Consolidated_Master_Req_Pool.RFQ Contact];"
Me.cboSupplier = Null
End Sub

View 2 Replies View Related

Populate List Boxes When A Form Field Changes Value

Nov 28, 2005

I have three list boxes on a form (lstSubjectName, lstTargetGrName, lstCountryName). The list boxes are populated from queries
based upon tables that have a many-to-one relationship to the main table. The
queries are the following:

SELECT tblProjectSubject.ProjectID, tblProjectSubject.SubjectName
FROM tblProjectSubject
WHERE (((tblProjectSubject.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectSubject.SubjectName;

SELECT tblProjectTargetGr.ProjectID, tblProjectTargetGr.TargetgrName
FROM tblProjectTargetGr
WHERE (((tblProjectTargetGr.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectTargetGr.TargetgrName;

SELECT tblProjectCountry.ProjectID, tblProjectCountry.CountryName
FROM tblProjectCountry
WHERE (((tblProjectCountry.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectCountry.CountryName;

The form is based on the main table (tblProjects).
I want to re-populate/update the list boxes when the ProjectID on the form
changes. I have tried to use a macro (ProjectID_AfterUpdate) for requery of
the list boxes, but can't get it to work.
Any other solutions? Thanks.
Niels

View 5 Replies View Related

Forms :: Populate Field With Drop Down List

Oct 25, 2013

Is there a function that will populate a field with drop down menu based on two criteria?I want the the fields with first and last name to populate with drop down lists based on the employee code I have inputted in the form and job title from a query.

Path: looks at employee code from form > looks at specific job title from query > pulls out all first names in one field and all last names in another field with the same job title in drop down list from query

Employee Code: 100

Returns all employees' first names in first field with same job title:

Prince
Tina
Greg

Returns all employees' last names in second field of form with same job title:

Fey
William
Jones

Here's what the query looks like in datasheet view:

Code:
Location # First Name Last Name Job Title Employee Code
1 John Smith Technician 100
2 Jane Doe Manager 100
2 Greg Jones Engineer 100
1 Prince William Engineer 100
1 Tina Fey Engineer 100

I've been trying to get dlookup to work, but no luck. Here's one of my formula:

Code:
=DLookUp("[Last Name]", "[Employees tb]", _ "[Employee Code] = Form![Employee Code]" & "[Employees tb]", _ " [Manager]"
SELECT EmployeeCodeONLY.[Employee Code] FROM EmployeeCodeOnly;
SELECT [Employees tb].[First Name] FROM [Employees tb] WHERE ((([Employees tb].[First NAME])=[Forms]![Form1]![Employee Code]));

The first is linked to a separate table that only contains employee codes because query I am working with has duplicates due to multiple records.The second is trying to link both the table and query together to populate only first name.how to include the second criteria, job title, to refine it more.

View 1 Replies View Related

Modules & VBA :: CBO Change To Populate Listbox With Distinct Entries

Jul 21, 2015

I have a combo box that populates many listboxes based on a selection (listbox values are coming from another table [Master]) - simple enough. I want the listboxes to populate with DISTINCT entries - also seems simple enough (right?).

As you'll see below, if the user selects "All" from the combo box (cboSite), I want the listboxes to populate with distinct values from all sites; otherwise, any other selection is a unique site and the listboxes will populate with distinct values based on that unique site.

This works BEAUTIFULLY if I select a unique site, but when I select "All", there is always one (and only one) duplicate value in EACH listbox. That is to say: all values but one in the listbox are distinct.

FOR EXAMPLE:Select unique site (not "All") from "cboSite"

The listbox "lstMajEquip" (which only has values "Yes" and "No") populates to show "Yes" and "No"

Select "All" from "cboSite"

The listbox "lstMajEquip" (which only has values "Yes" and "No") populates to show "Yes","No", and "No" (what?!)

As mentioned, this happens for all 8 of the listboxes, not just "lstMajEquip"...

Using Access 2010

Code:
Private Sub cboSite_Change()
If Me.cboSite = "All" Then
Me.lstBusiness.RowSource = "SELECT DISTINCT Master.Business, Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.Business) IS NOT NULL) And ((Master.Business)<>''));"

[Code] ....

View 7 Replies View Related

Automatically Populate A Date Field Based On Value Entered In Another Field

Nov 10, 2005

I need to create a New Form control for this situation:

If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.

For example:

If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.

Any help is greatly appreciated.

View 2 Replies View Related

General :: Calendar Icon In Textbox To Populate Chosen Date

Oct 14, 2013

I have started converting our Access 2003 to Access 2010 applications. In the past we have used the Calendar to pick a date to place in a textbox. I noticed that this feature will not work in Access 2010.

So I looked around and saw several references to using the calendar pick feature to a textbox...but without success.

How to have a textbox with a calendar icon next to it to populate the chosen date in the textbox?

View 2 Replies View Related

Populate A Date Field With A Check Box

Sep 26, 2006

Hello,

I need to have a date field auto populated with the current date, when a check box is selected on the same form/table.

I am OK with access but self taught, so treat me like a rookie.

Thanks in advance for any help that can be provided.

View 1 Replies View Related

List Dates 3 Months Ago

Mar 19, 2007

Hey

I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating

View 14 Replies View Related

General :: Looking At Certain Record And Populate Comment Field

Feb 13, 2015

I have Field "BC1Chng" which requires user input. I want to be able to write a code to reference to "BC1Chng" if there is an input in that field for any record...I want to copy the Remarks into each record in the Remarks Field.

I was able to get the remarks1 field to loop through each record copying what was in that field into each record.

Now I want it to look at the BC1CHng field and only copy to remarks1 field if there is any input in that field???

View 9 Replies View Related

General :: Populate Calculated Field Into Table

Aug 6, 2013

I need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.

I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.

E.g. SK 456 849, SP 6789 4356.

I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.

Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).

I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?

View 10 Replies View Related

General :: Populate A Field In Form From A Query

Mar 21, 2013

I'm trying to populate a field in my form from a query. I've been trying to figure it out from the web but can't get it sorted. I've got a combo box that selects a member of staff from a table, and then a query runs which takes the value from that combo to retrieve the staffs current rate of pay.

I've added this code to the on change event on the combo.

Private Sub cboStaffID_Change()
Me.txtunit.Value = [qrycurrentpay]![Unit]
End Sub

But it doesn't seem to work,

txtunit is the field I want to fill on my form, and Unit is he field name of the value I want from query qrycurrentpay.

View 2 Replies View Related

General :: Populate Form Field With Value From Query

Mar 24, 2014

I am trying to calculate the keystage and year group of pupils on my database. I have created queries that successfully calculate the data, but I cant work out how to add the results from my queries into an already created form. I thought I could use DLookup, but this just populates with a random value from the data and not the value for that particular pupil.

View 3 Replies View Related

General :: Lookup Into A Table - Populate Same Field

May 28, 2014

I have a table holding a list of post codes, and their servicing depot EG -

ID Postcode Depot
1 AB10 Edinburgh
2 AB11 Edinburgh

Then a form, which has a field for depot ( Fld_Depot )

What i would like is, when the user clicks on the field, a msg box prompts, asking for them to input a postcode

Once the postcode is entered, it populates that field with the relevant depot from the table....

View 3 Replies View Related

Limited List Rows In Distinct Row Query

Nov 21, 2006

Hello,
On a form I have a way to search for a record by using a listbox that has a Distinct Row Query from the table that the main table that the form is bound to. It looks for the Sample ID's that are associated with the samples that we test. The list ends at record 87877. We are WAY past that number in our numbering scheme but the list box does not display all the records. When the users type anything over 87877 the auto complete doesn't work and the last record shown in the list is 87877. Does anyone have any idea how to make all records show? The users use this to navigate quickly between samples but now it is broken. Is there some sort of limit?
Thanks
Greg

View 4 Replies View Related

Reports :: List All Clients And All Months

Apr 11, 2014

I need to create a report from a query. I have created the query that pulls the below data from the relevant tables:

ClientName;invoiceAmount;InvoiceDate

Using the wizard I have created a basic report that shows the information in the query, but I need it to list all clients and all months - even if some months show no invoices sent.

Ideally it would show as follows (with an option for the relevant year to display):

______ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec
Client1
Client2
Client3
Client4

Do I need to create a separate tables for Month/Year and link these to the query? Or is there a simple function I can put into the query to achieve this? It seems a simple task, but after playing for a week I've got no where

View 9 Replies View Related

Populate Option Group When Date Field Is Entered

May 24, 2005

I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?

View 6 Replies View Related

Populate Option Group When Date Field Is Entered

May 24, 2005

I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?

--- OR ---

Can it notify the user that they need to change the Option Group?

View 3 Replies View Related

Modules & VBA :: Auto Populate A Date When Clicking On Another Field?

Apr 22, 2015

I would like a date field I have set up to auto populate the current date when I click on another field to enter information.. how would I do that?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved