I've got a form that has drop down menus to select info from a particular table for search criteria in Access 2003. If I select a certain set of data I get results in from query. If I happen to leave one of the drop down menus blank than I get no results, when I know there are results availabe. What type of language do I need to put in the criteria part of the query to allow a blank search of one or more of my drop down menus? Say I have 10 drop down menu criteria, and I want to use only 3 of them as criteria for a particular search, so I select the appropriate items and leave the rest blank. But using the same form, be able to do a different search using any combination of the drop down menus with a specific response or blank to search every respoonse in that field.
That way I can have one form to do multiple types of queries depending on the information you select in the drop down menus. And if the menu is blank, the query assumes any entry in that column is open to results.
Any ideas? I am a newbie to Access, so I'm probably just missing something simple, or I'm trying to do something far more technical then I ever thought it would be.
in the most straightforward situation, you create a form based on a table...
now if i create a query with fields from all the tables.. eg..
job date, job time, driver name, price
if i create a form in the basic way, you cannot add fields from several tables (in the direct sense i mean, forgettin the use of subforms.. etc)
if i create a form based on the query, will this allow me to add as many fields, and most importantly from as many tables, as i wish.. choosing only the relevant ones for that particular form..
will everthing automatically link with the job id if my user fills in this form..
if this is the case then i can see my self making one large query.. with all the relevant job details, and then using a tabbed form to separate them.. is this a common practice.. is this the correct way to go about things?
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID UNION SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet) Set db = wrkJet.OpenDatabase("DW", _ dbDriverNoPrompt, True, _ "ODBC;DATABASE=DW;DSN=DW2") 'Set rs1 = db.OpenRecordset(strSQL)
Is there a way to open a form from a form in a different database? I'm pretty sure it needs to be done in VBA and have seen some code floating around that suggests it can be done, but I'm thinking there's more to it, like maybe one or both of the DB's need to be in a different format?
I have a database with a Main Menu Form, containing a Button that loads my main data entry form. When the Button is Clicked portions of the data entry form that is loading shows through the Main Form Background (e.g. portions of the navigation bars, and portions of the boarder on the form that is loading.)
Im trying to create a database for a small hotel but Im having trouble making each booking individual. I have customers who I can make individual by making a autonumber - customer ID and I have a seperate table for rooms but in making a booking table i can't have two autonumbers, as in I cant have an autonumber for customer ID and one for Booking ref. any help?
I was presented with a challenge to create a form that users will fill out online. There are a handful of questions mostly with check box answers, and a couple of areas to type your name and the date.
NOTE: this form is currently only on paper...
Now how do I go about making this form into an "online" version?
Basically I need users to be able to fill out this form online and then have the results collected. And possibly have the admin be able to generate a report of the forms that have been submitted.
I have 10 databases . ALL identical and used for different vendors. All have same table structure ( invoice, price, etc , etc tables) I want to run a query where I type in the invoice number and it should query all the database and display the appropriate information that I need.
Would appreciate any ideas or suggestions. Thanks
ALSO databases are all on one server.I would need to connect using ODBC
Is it possible to open a database from a form in a different database? Not necessary to do so; it would just be handy to do from the form I already have up and looking at rather than having to go open another instance of access and go to that database.
My form enters the data into my table just fine. when i view the data in the TABLE, I can see all records and use the record selector at the bottom of the window, just fine...it shows 1 of 9 or howver many records. here's my problem. I want to enter as well as view my records via the FORM and when I open the form, it let's me enter stuff but it only update the record selector according the current session.
Example: I open my form, enter 4 records. The record selector shows 4 of 4. I then close my form. When I open it again, the 4 records are in the TABLE, but the FORM (blank and ready for my new entries) shows 1 of 1 again. The entries (records) go in but the record selector never reads (counts) anything entered in the previous session, even though the records ARE THERE in the TABLE (as they should be). HELP!! What control have I mixed up?
I know this is pretty basic, but it is annoying me. Whenever I bring up a certain form in my database, it seems to call up the database form. How can I get rid of this.
Im creating a form to add data to a database and my issue is that when I hit the save record button it overwrites the first line in the database and I can't figure out why it would be doing this. Any ideas?
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
I am currently using a large Access 2002 database in order to generate various reports.
My two main tables are despatches and returns from which they hold around 1,200,000 records and 100,000 records respectively.
The problem I have is that the reports use various expressions within various queries to generate a single result (percentages per channel etc.) This is obviously very time consuming and it may take up to around 10 to 15 minutes to get a result from a chain of around 5 queries.
Can anyone suggest alternative methods to generate similar results in quicker time? (Please note that the tables can not be downsized and records can not be archived)
I have a database with all of my contacts... I have a report that shows my contacts in alphabetical order and I have a form for adding new contacts. I was wondering if there is a way to have a report or query that will look up a contact by typing in the person or the company name.. Is this possible and if so.. how do I do this?
I am building a simple assets database to help me with my work. This is what I did:
I created a table with following columns:
1) Bill ID - unique number of the asset bill, created by the accounting software.
2) ID of the supplier
3) Month
4) Type of the bill (code such as RI for investments, RO for activated assets etc.)
5) Date
6) Basic non-taxed value.
There is a 20% tax on assets. 19% OF the 20% tax is added to the base non-taxed value - I get the starting value of the asset before amortisation begins.
So far so good, my query works perfect, does everything right, however:
Every now and then (less than 10 times per year) I get assets from suppliers that are free of tax. That means that the value on the bill = starting asset value, and there is no need for adding the 19% of the tax back to the asset value. I don't know how to exclude those bills from the process.
The way I figured it theoreticaly, it can be done like this:
1) I could store the tax-free supplier codes in a separate table and make the query perform a check against the column 2 (supplier ID), and if it findds the supplier there, the base value is taken as a final value.
2) I could make all non-taxed bills have an unique tag for the non-taxed bills(column 4 - type of the bill), and make the query check against those.
I have no clue how to make any of those, so I hope someone here could help me.
Hi; i designed a database it is controlling the stock and the test results but there is a problem when i was using the query because i entered the table data like "1111 Microsoft Access" "2222 Microsoft Excel" when i executed the query i must enter the exact data like "1111 Microsoft Access" but i wanna only use "1111" for executing the query.
Hello, Ok this may seem simple, but I can't think of an "easy" way to do it.
How can I query all tables in a database (that are not related in any way, and shouldn't be... for a reason) to find a specific string.
The example is, the db contains tables of pc's at multiple schools, sort of a primitive inventory. Each school has its own table at this point, because we may eventually give each school its own db in the future (and once we clean this one up a little!). If I am looking for a pc's service tag, but don't know what school's table it's in, I have to open and individually do a "find" in every table. Is there a way to simply query all tables in a db without the tables being related? Is this a union query? If so, is there a limit to how many tables can be part of that?
is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people... i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible what do u think? *j
but im having some problems with the search on asp. the problem as u can see is the fact it has to be the exact item for the search to work, atm the query is
Select * From PLIST Where PLIST.DESC Like "::ID::"
but i need it to work on single words from teh database rather then the whole thing, anyone got any ideas if i can change that so it will pick up on words rather then the whole item?
Amiga'Ferrari Formula One'
use that if you need to find an example of data, its got all the stock records of the shop dating back for years :P
