Queries In A List Box

Dec 10, 2007

i would like create a form with a single list box and that have all the Queries showing so the user can just click the button and run the query.
i have tried one but its not showing the queries. please help!
Thanks for your help

View Replies


ADVERTISEMENT

Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View

Mar 28, 2014

In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.

So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.

I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.

VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);

VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery

The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.

Any hints, how I have to use the * for getting the complete list on the initial view ?

View 5 Replies View Related

Queries :: Return List Of Records From Original List

Apr 23, 2013

I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.

I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.

eg:
keywords:
look
billy
magic

list:
"have a look and see"
"spanish dave"
"who is billy brag"
"looky looky I go hooky"
"who's the man from argentina"
"could it be magic now"

my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".

the sheet has the keyword in each row and next to the column:
=COUNTIF(list,CONCATENATE("*@",B13))
where "list" is the external data.

View 3 Replies View Related

Queries :: Creating A List Based On Multiple Queries

Jun 23, 2015

I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error

ODBC-- call failed ODBC Driver SQLBase.....

Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.

Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.

View 2 Replies View Related

Query To List Multiple Records In A List From Multiple Queries

Jul 11, 2013

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

What I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.

At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.

View 7 Replies View Related

Exporting My List Of Queries

Dec 27, 2006

HI, I have a number of queris within my access database and I wish to export the list of queries. Is there any way in which I can do this? Or Am I going to have to retype the list in word? :-(

View 9 Replies View Related

List Boxes Fed From Queries

Jun 25, 2007

Hi peeps

I have a list box that contains 4 fields and the list box is located on a form.
One of the fields on the form is [Date logged]. This is also colum 2 of the list box.
I am trying to write some code to say that if the Date logged on the form is different to the one in the list box, run an append query.
Basically, the principal behind this is to have an audit trail of when records have been ammended. The first time a record is created, a base record is created. It is this base record that will populate the list box along with all other record changes that occurr from that point on.
I am trying to say that if the [Date logged] does not match the last record in the list then it knows it needs to append the record to the history table again as a change has been made. I would really appreciate some help here folks. Thanks so much! :-)

View 2 Replies View Related

Crerating A List Of Queries For Documentation

Jul 17, 2006

I'm attempting to create documentation for an existing Access database - literally, it's a work-flow document.

It was relatively easy typing out the list of tables, modules, and Macros, but I have god-only-knows how many queries.

Is there any way to export a text list of query names to include in my documentation?

Regards and thanks!

Susan

View 3 Replies View Related

List Boxe For Multiple Queries

Jan 12, 2006

Hello,

I have a form on which I have placed a list box which will return data from a query via cmd button.

Is it possible to use the same listbox to see different queries, using different cmd buttons?
I will place multiple button each one of which will run a query that will show its data in the list box.

Anyone knows if this is possible?

Thanks.

View 1 Replies View Related

Queries :: How To Join One Value To List Of Values

Jan 2, 2014

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

View 7 Replies View Related

Queries :: List Latest In Each Group

Mar 23, 2013

I have a table of SOPs/WIs. Each, with time, gets a new version - this I keep in the child table.

tblVersions
------------
VersionID (PK)
SOPID(FK)
Version (integer)
IssueDate (date)

I want to make a list of the currently valid versions, i.e. for each SOPID pick the VersionID with the latest date.

One can do many things with subqueries and Max - functions, but after some experimentation I have stumbled upon this:

Code:
SELECT Last(tblTest.VersionID) AS LastOfVersionID
FROM tblTest
WHERE (((tblTest.IssueDate)<Date()-10))
GROUP BY tblTest.SOPID;

The function Last has a bit tricky reputation and description on the web. It does yields the right result in my testcase though , see the attached db - run query3.

Query3

Code:
LastOfVersionID
100
90
20
40
666
888
55
8

Update:
Well, it isn't. Just changing the VersionID numbers to something other than an ascending sequence renders an incorrect result with the above query. So what is the best way to get the desired result? (Access 2007)

View 4 Replies View Related

Queries :: Clearing List Box Then Requery

Sep 24, 2013

I have two list box that control 5 subforms. i have a Run button that works in sorting the subforms according to the listbox. I have a rest button that i want to clear the selections of the listbox and then requery the subforms as if the criteria were null - or return all records. i have the selections clearing, it is just my forms are not "going back to all" on the requery.

Private Sub Command62_Click()
Dim varItm As Variant
With clinicLbx
For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm
End With

[code]....

View 1 Replies View Related

Queries :: Drop Down Lists Or List Box

Dec 2, 2014

In my table I have the following fields that are tick boxes

Vee
Cathedral
Round Bilge
Bilge Keel
RIB
Semi-Displacement
Keel
Lifting Keel

Each record can take a couple of these options. For example record 1 could be ticked for a 'vee' and a 'cathedral' whereas record 2 could be ticked for all of them.

I have been trying to use these to make a search form. I want to be able to chose on ie. 'Round Bilge' and it will search for a record that is ticked 'round bilge'. I have done this for both a combo box and a list box and have used the formula for each field [Forms]![Boatsearch]![hulltype]

When I have clicked run query I have an error message saying: 'This expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables'.

View 3 Replies View Related

Queries :: List Field Result In One Row

May 3, 2013

How can list the contents of one field columns in one row in a report.

Example:
ID: 1
A1
A2
A3
A4

Should be: ID1 - A1, A2, A2, A3, A4

View 3 Replies View Related

Queries :: Selecting Dates From A List

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

General :: List Box Row Source Queries

Mar 27, 2015

If I have a form with multiple list boxes, lets say named 10, 20 and 30. and multiple text fields called 15,25,and 35. Now I want to get a value in the list box based on the text box value so I make row source queries that looks like this.

SELECT [table].field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![15]));
SELECT [table].field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![25]));
SELECT [table].field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![35]));

So now my question... Is it possible to make one query that picks up the 15,25 or 35 depending on wether it is being queried from 10, 20, or 35. The difference in name is always the same except for the first number so could it be made to reference where it is asked from?

View 6 Replies View Related

Queries :: How To List Records That Aren't There Yet

May 15, 2013

I'm new to query design. I have a database where operators will enter readings from 14 different compressors, twice per shift, three shifts per day. In the Rounds table for each record the following fields are included:

Timestamp (includes date)
CompressorID (1-14)
Shift (1st, 2nd, 3rd)

Round (each set of readings is a round, each shift is supposed to do 2 per shift, the rounds are numbered Rnd 1 and Rnd 2)

Status (this is one of many readings they will record for each round/machine)

On the paper form it is easy for the operator to tell which machine they haven't recorded a round for yet because the space for it on the form is blank. In the Access form they are simply presented with a blank space to enter a new record. I'm trying to develop a strategy to make it easier for the operator to see at a glance which compressors they haven't entered readings for yet. Management also needs a weekly report of which rounds were done and which were missed.

I'm thinking of creating a datasheet from a query that will list the missing rounds for each compressor/round/shift based on the current date. I think I can put that datasheet onto the form the operator is using to record the readings or perhaps on a subform they can access with a button. I'm having trouble designing the query. So far I've created a query that lists all the rounds for a specific date, then another query that uses those results to list the compressors that are missing a record.

View 14 Replies View Related

Queries :: Return Values From A List

Feb 12, 2014

I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.

Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.

E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography

I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned

View 2 Replies View Related

Queries :: Delete Records From List Box?

Apr 20, 2015

I have list box named [lstItems], a table named [tblItems] and the field name is [Model]

I would like to (from a list box) delete a record from a button. I have looked and tried lots of delete code, but none of them seem to work.

View 14 Replies View Related

Using Multi Select List Boxes To Run Queries

Apr 21, 2006

I have a simple question that I know will have a very complex answer


You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables

Here is an example of the structure
TblSourceOne
SourceOneID
SourceOneData

TblSouceTwo
SourceTwoID
SourceOneID
SourceTwoData

TblSouceDetails
SourceDetailsID
SourceTwoID
SourceDetailsData

As you can see TblSourceTwo contains a foreign key from TblSorceOne and TblSorceDetails contains one from TblSourceTwo

The main table for the second set

TblTargetOne
TargetOneID
SourceOneID
TargetOneData

The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo

TblTargetTwo
TargetTwoID
TargetOneID
TargetTwoData

So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo

Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails

TblTargetDetails
TargetDetailsID
TargetTwoID
TargetDetailsData

Now the user has completely copied all of the necessary record for both table.

Finely the question

How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.

View 2 Replies View Related

Queries :: List Of Rooms Available Between A Period Of Two Dates

May 20, 2013

I want to make a query in my system which finds out if a room is available for a person to stay in. I want the query to show me a list of rooms that are available between a period of two dates. So if I enter a arrival Date (12/12/12) and then a checkout date (13/12/12) the query then shows me a list of rooms that are free for those dates. How would I go around doing this?

View 1 Replies View Related

Queries :: Create A Query That Will List All Items

Dec 1, 2014

I have the following tables:

tblOrderDetailsKeyItemIdOrder QtyPO#001A204001002B204001003C304001

tblTransactionsKeyTransDateItemIDReceivedPO#0011/1/2014A1040010021/2/2014B2040010031/3/2014A54001

I also have a table that list the items and on that table the column ItemID is the key.I want to create a query that will list all items ordered on a PO# and how many have been received so far. With that I will calculate the outstanding qty. I want to see:

qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B202004001C30030

But all I see is this:

qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B20200

It is missing item C because I have not received any yet so there are no records in the Transactions table for it to select.

View 4 Replies View Related

Queries :: Requerying A List Box Based On A Query

Oct 3, 2014

I have a list box (List25) that is generated from a query (qryThisBox). One of the fields in that query uses the criteria [Forms]![MyForm]![SelectedField] to filter.I also have combo boxes in the form that List25 displays in. Unfortunately, I want to requery the list box in the form, using a combo box that calls the same underlying table field, SelectedField.

How can I do this? Can I somehow dynamically change the criteria in qryThisBox to change when it gets requeried?How do I stack combo box requeries, eg: Combo Box 1 gets selected - does a requery

and/or

Combo Box 2 gets selected - and filters the results of the requery for Combo Box 1.

View 1 Replies View Related

Queries :: Task List Max Tasks And Max Hours

Jun 14, 2014

I would like to make an action list based on tasks. I try to make a query that does the following things:

-Taken.Priority=1
-show maxium 5 tasks sorted on make date(taken.ingevoerd)
-OR with a total duration of maxium 480 minutes (Taken.[Geschatte duur (min))

The last one is to hard for me to make. For example if the duration of 4 tasks is 420 minutes, the fifth task (100 min) should not be shown, because the criteria of maximum amout of minutes is reached.This is what I have made so far:

SELECT DISTINCT TOP 5 Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
FROM Taken
GROUP BY Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
HAVING (((Taken.Priority)=1))
ORDER BY Taken.Ingevoerd DESC;

View 2 Replies View Related

Forms :: Queries With Joins To Filter List Box

Feb 13, 2014

I have 2 forms which allow the user to first select a catergory. They can then select a sub category based on the selection made in the first box.

I have the form working 90% but can't get the list to filter based on the previous selections.

The code I'm using to generate the listbox rowsource is shown below;

"SELECT tblcatctry.CtryID, tblcatctry.Country, tblcatvtry.zoneID " & _
"FROM tblcontactsCountry RIGHT OUTER JOIN " & _
"tblcatctry ON " & _
"tblcontactsCountry.CtryID = tblcatctry.CtryID " & _
"WHERE (tblcontactsCountry.CompanyID IS NULL) OR " & _
"(tblcontactsCountry.CompanyID <> " & VarCompanyID & ") " & _
"GROUP BY tblcatctry.CtryID, tblcatctry.Country " & _
"ORDER BY tblcatctry.Country"

I want to be able to add in a join to the table tblcontactszone which has the selections previously made for the fields, ZoneID and CompanyID. How do I filter the above further?

View 14 Replies View Related

Queries :: Using Multi List Box And A Combo Box In Query

Jan 23, 2014

I currently have a database of customer information and form set up with a MultiList box based on the regions those companies are based. You select the region(s) you require from the list box, click a button and it runs a query.

However I now want to be able to filter the results further using a combo box with business sectors in also.

The code used for the multilist box is as follows (I don't totally understand all the code if I'm honest, but it works )

Private Sub Command2_Click()
Const cstrQuery As String = "CAMPQRY"
Dim strNames As String
Dim strSelect As String
Dim StrWhere As String
Dim varItm As Variant

[Code] ....

View 2 Replies View Related







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