Allow Multiple Values In Unbound Field For Query Parameters

Apr 30, 2014

I am trying to find a way to allow the user to enter multiple numbers in an unbound box that I will pass to a query as parameters. I already have it set up to pas the parameters from the form but if i try to do more than one number it doesn't work.

I have tried:

1306 or 1307 or 1308
1306, 1307, 1308
"1306 or 1307 or 1308"

I was reading somewhere that when the value is picked up from the form it is not like you are typing it right in the criteria box of the query.

So are there any other options here? Is there a way to enter all the number in a box and then use vba to create an array and then pass that to the query?

View Replies


ADVERTISEMENT

Multiple Parameters [for The Same Field] For A Single Query?

Nov 23, 2005

I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.

I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.

So they can get "Accounting" and "Admin" both in the records that are returned.

The code:
SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date]
FROM T_ElainesMaster
WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?]))
ORDER BY T_ElainesMaster.Section;

Thanks!

View 1 Replies View Related

Queries :: Query With Field Containing Multiple Values?

Jun 21, 2014

modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query.

SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema
FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];

View 11 Replies View Related

Saving Previous Unbound Field Values

Oct 5, 2005

hi

i have a form (A) with some unbound combo boxes which generate a subform (continuous) recordsource and the records output

after i filter the subform, i click on a subform record and open another form (B), and close form (A)

i do a bit of editing on form (B), then i click a button to go back to form (A).

is it possible to open the form (A), with the unbound combo boxes values at the same value as when form (A) was last closed, and as such the subform records output as per when the parent form (a) closed

if this is possible i can then have a button on form (B), to go to the next or previous record on the form (A) subform

apologies if this sounds confusing, many thanks in advance

View 1 Replies View Related

Modules & VBA :: Insert Unbound Field Values Into A Table?

Mar 11, 2015

I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid

View 2 Replies View Related

Query Searching Multiple Tables' Field Names (not Values)

Jan 22, 2007

Hi. Please do not lecture me on database normalization, as this truly is not in my control.

I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)

If I design a query showing all 2 dozen tables and their fields...
Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)

If I am only as clear as mud, please let me know, and I will try again.
Or, by asking if I can search for field values querying by field name, am I any clearer?

Russ

View 14 Replies View Related

Query Multiple Parameters, Help Pls!

Jan 4, 2006

I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...

strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"

Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.

SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;

Your help will be greatly appericated.

View 3 Replies View Related

Union Query - Multiple Parameters

Oct 31, 2005

I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.

SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));

View 1 Replies View Related

Multiple User Specified Parameters For Search Query?

Mar 30, 2006

Hello

I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.

I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?

For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:

Any help gratefully received!!!
:)

View 2 Replies View Related

Using Multiple Criteria / Parameters For Same Record In Query

Jul 3, 2013

I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.

However, it would be useful to be able to see all stages at once, next to one another.

So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.

It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.

View 1 Replies View Related

Access Query To Accept Multiple Check Box Parameters

Nov 9, 2007

Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you

View 13 Replies View Related

Modules & VBA :: Multiple Selections In List Box - Query To Accept Parameters?

Mar 27, 2014

How to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory

[Code] ....

I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:

1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner

The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).

I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?

View 4 Replies View Related

How To Make Unbound Field Auto-populate Conditionally From Another Unbound Field

Jan 30, 2015

To make it simple, I have a list of contact names and their email. I want to have a form with no control source and have 2 simple drop down boxes for their Name and their Email. What I would like to happen is the user choose a name from the drop down in field 1 and then the field below auto-populate their email in field 2. I understand I can use conditional IF/THEN and list out each email, but the contact list is ever changing, so I want to first drop down to link to the TblContacts, have the user pick the name from the list and then have the 2nd field autopopulate from that same TblContacts with their email respective to the Name entered in field 1. My form has two field [Traveler] and [Email]...The tblContacts two fields are [Name] and [Email]

Code:
DLookup("[Email]", "tblContact", "[Name] = [frmEmail]![Traveler]")

View 5 Replies View Related

Tables :: Linking Multiple Field Values To A Field Selected From Combo Box

Feb 16, 2014

I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)

Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)

Forms:
* Player form
* Match form

Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).

View 1 Replies View Related

Concatenating Multiple Field Values Into One Field Separated By Commas

Nov 18, 2014

I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:

[Field1]&", "&[Field2]&", "&[Field3] and so on.

However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?

View 10 Replies View Related

Multiple Parameters Doesn't Work On Multiple Fields

Jun 27, 2006

I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?


SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));

View 6 Replies View Related

Multiple Values In One Field??

Feb 14, 2006

I have a list of Vehicles in which each have their own maintanance schedules. Most of the maintanance for the Vehicles overlap. Instead of entering the same information over and over I'm assigning each type of vehicle a code number (example '05 Cargo is a 1, '04 Cargo is a 2). Now what I want to do is assign each maintanance the vehicle code, for instance I have 13 different types of vehicles that all need oil changes, so I want to assign the Oil change maintanance 13 codes. Is there a way to assign that maintanance 13 codes without having to enter it 13 times??

Thanks, Joe

View 1 Replies View Related

Multiple Values Field

May 7, 2012

Is it possible to have a text field in a table to store multiple values by using a comma or some other type of seperator? I do not want to set the field to a lookup field because the number of values that would be included would be to many to continuously look through to select. I would prefer to just manually type in what I need and use a seperator to create a multiple value field.

View 7 Replies View Related

Multiple Field Values In Relationships?

Jan 21, 2013

I have a table for keeping track of software keys This table has all the software keys and an ID field in it.

I then have a table for computers that currently I have it set up so that there is

OSID Field linked to SoftwareID
OfficeID1 Field Linked to SoftwareID
OfficeID2 Field Linked to SoftwareID

On a form I would like it to list ALL software no matter if it is linked or "installed" and show for those that are installed the ComputerID that they are installed on.

Currently no matter how I set it up I can only get it to show the ComputerID for the OS only or for the OfficeID1 only. I want to see if the software is on a computer no matter if it is in the OSID, OfficeID1 or OfficeID2. How do I do that if it is possible?

View 3 Replies View Related

MS Access - No Values Given For One Or More Parameters

Oct 8, 2014

I have an ongoing error in ms access vba, being pretty new to the app i am stuck, i have a split database where in i am trying to execute a query in vba but it is throwing the error"No values found for one or more given parameter" however when i debug.print the query and run in ms access it works fine,

Below is the query:

qry = "SELECT * FROM Core"
qry1 = qry & " WHERE (((Core.Activity='" & UCase([Forms]![frmReports]![cmbrptactivity]) & "') AND (Core.Unit='" & UCase([Forms]![frmReports]![cmbunit]) & "') AND (Core.Dt_Upload Between #" & [Forms]![frmReports]![txtDate1] & "# And #" & [Forms]![frmReports]![txtDate2] & "#)))"

[Code] .....

View 7 Replies View Related

Multiple Lookup Values Into One Table Field

Apr 4, 2006

Hi,

Is there anyway to have a single field in a table which is populated via the use of a lookup onto another table, but allowing multiple value selection out of the lookup table and populating those into the field...

For example

Table 1 is customer details
Table 1 field 3 = areas of interest

Table 1 field 3 is populated via a lookup into Table 2 interests

Table 2 has 4 records

Sport
Household
Motoring
Family

I want to be able to select 1 or more of the Table 2 values and populate them into Table 1 Field 3....

Help my head hurts....

View 3 Replies View Related

Multiple Field Values Need To Be Returned In ONE Line

Jul 21, 2006

I have no idea if this is possible or not.
I have a requirement to pull a list of Doctor's, their address, and which counties they serve. Sounds fine, right? Well, they want all the counties on one line... One record for each unique address.

So, my query would return this
Dr Address County
Dr Smith Main Street Plymouth County
Dr Smith Main Street Sarasota County

But they want it to look like this
Dr Address County
Dr Smith Main Street Plymouth County Sarasota County

Any idea on how to pull that together for them and put it in an Excel spreadsheet?

Many many thanks.
Tess

View 5 Replies View Related

Queries :: Calculate Multiple Values Same Field

Feb 22, 2014

I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.

Below is a code that works, but only when i have each vehicle with the same vehiclenumber.

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;

View 4 Replies View Related

Putting Unbound Query Field Into Report

Jun 11, 2007

Hey guys im really stuck on this one and hope some one can help me out.

I have the following structure:

Table: Products
ID
Product Name

Table: Suppliers
ID
Supplier Name

Table: Prices
ID
Product Name
Supplier Name
Supplier Offer

Query: QrySuppliers
Prices.ID
Prices.Product Name
Prices.Supplier Name
Prices.Supplier Offer
Percent Off Trade: IIf(IsNull([Products]![Trade Price Euros]),0,([Products]![Trade Price Euros]-Nz([Prices]![Supplier Price],0))/[Products]![Trade Price Euros])

Because the "Percent Off Trade" field is unbound to a table I cant get the data into a report, instead every time I open the report it asks me for Products!Trade Price Euros :(

I've never tried doing complicated calculations in Access and would reallly appreciate any suggestions you guys could give me.

I tried to attatch the database but its 508kb so if anyone has a spare second and wants to check it out heres the link http://www.jeron.co.uk/Products.zip

once again thanks!

View 2 Replies View Related

Queries :: Using Unbound Field As Query Criteria

Dec 13, 2013

I have a value in an unbound field on a Form, which is 1234 OR 765 OR 356.

In the QBE criteria grid, I used builder to reference this form:

Forms!myform!myunboundfield

The column this is in is for the ID field, which is a number.

However, it is not filtering the data correctly. If I copy the above text and paste it into the QBE grid, then it will work. But when I reference it, it fails. If I change the value to just a number on my unbound field, it works. So the issue seems to be that its bringing across the text as a string and so perhaps effectively puts quotes around it when referencing it.

View 11 Replies View Related

Modules & VBA :: Inserting Multiple Records From Multiple Unbound Text Boxes

May 6, 2014

I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table

the code I have started off with is

Code:

CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"

which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down

is it possible to add all 15 records at once? do you think Im going at this the right way

View 5 Replies View Related







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