Queries :: Table Of Companies - Allow User To Define Field On A Form
Feb 14, 2014
I have a table of companies, with fields that contain data for 2008, 2009, 2010, etc.
I'd like to design a query that allows the user to define on a form the field, or the year, they want to query. (by text string or some other way, I am good enough with the VBA that I can figure this part out once the beginning part is figured out)
I want 2009 data, I type in 2009 and get 2009 data from a table with many years' worth of data. But I only need one query for all the years.
I notice that this is easy with reports, just use SQL in the the wherecondition, argument, but I can't find the equivalent for queries. I tried putting the text field from the form in the SQL in the query, but could not get that to work.
I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.
The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.
I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.
The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but everytime I try it I get a box appearing saying Property not Found.
I have created an union query to pull together the same data from 2 ODBC tables (seperate countries).As there is a clash in the client number I have added a "N" prefix to differentiate NZ from Australia, as below:
The problem I now have is that I get type mismatch when linking to another table. How could I change the SQL below to dictate the field format to number (providing it will accept the "N" prefix) or all to text?
I would like to define a field (mailingaddresscounty) as type lookup using a field (county) from another table (Zip). But instead of the user selecting from a long list, I would like the selection to be automatically made based on the value of another field (mailingaddresszipcode).The two tables are Organizations and Zips. The user enters the 9 digit zipcode in the organization table (mailingaddresszipcode). I would like the county field in the organization table (mailingaddresscounty) to draw from (link to?) the corresponding 5 digit zipcode in the Zip table (zip) and return the correct county for that zipcode.
I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.
Here is what I need to do:
When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.
So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.
I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.
Maybe I don't need a history table but something else?
I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.
I created a query with one expression field that updates a user inputted date field on a form. The expression adds a certain amount of time to the field (usually six months) so I know when the next inspection should take place. Everything works great except when I put a parameter in the expression field. It will not return the property dates. If I simply remove the expression, and input the date manually, it works just fine. Am I not allowed to use date parameter with an expression? It returns every date within the correct month, but will give me future years as well.
The expression is - NextInspectionDate: DateAdd("m",12/[InspectionFrequency],[LastInspectionDate])
The parameter is - Between [Forms]![Preventative Maintenance Dates]![Sta
I am trying to save my table and I get a message saying "Cannot define field more than once". I only have 14 fields and none of them are repeated. My field titles are: First Name, Last Name, SSN, Wage, Salary, DOB, Hire Date, Years Employed, Phone Number, Alternate Phone, Address, City, State, and Zip. What am I doing wrong?
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
This table holds variables that I want to declare to use throughout my application. I have been told in another thread that tempVars are the best way to do this.
I have written the following code, which works on a limited basis:
Private Sub btnSetVAr_click() TempVars.Add "udvVar", Me!My_value.Value End Sub
This defines a single variable on each button press, fine to work out how the code works, but not much use. What I really need to do is when the initial menu screen loads to call a routine to assign all the variables stored in the table using a loop to do this. The idea is to make all variables values easy to edit or add to, rather than have to edit code each time we need to change them.
The variables table holds 14 records so far, such as:
My_Var, My_Value
EuroRate, 0.885 ConDisc, 0.9 MollDisc, 0.8
As well as holding numbers, they hold strings and date values.
So, on loading the initial form, use an event to assign the variables from the table using the tempVar name as the value held in "My_Var" and it's value as held in "My_Value"
I'm just to work with Fox Pro, and I am therefore puzzled with how to define the exact number of digits in a field in a table in Access. If I need 9 digits + 2 decimals in a number filed, how do I define that in design view?
Also, I need to know how to export a table into a text file, with a format without any spaces, and each record is divided with a new line. This an old IBM text format file.
I would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult.
Here is the function that I use in Alpha:
FUNCTION udVars AS A (udTabName AS C ) dim codeStr as c t=table.open_session(udTabName,file_ro_shared) t.fetch_first() while .not. t.fetch_eof()
[Code] ....
It opens a table reads in the records and then makes them into a string the string would look something like this:
"Public EuroRate as Single = 0.885"
I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...
The table would hold strings for all the above so you would have:
My_Var (variable name) My_Typ (type of variable e.g. Single) My_Val (value e.g. 0.885)
The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.
I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.
One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:
Code:
SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc FROM tbl_OSRImport WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));
This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:
In the Query, the expression is: VarPerc: [OverShort]/[NetSales]
When the Query runs, the full numerical result is: -4.27103159497526E-02
Which visually is output as: -4.72%
Mathmatically (on a calculator using the same values) the equation is as follows:
-11.22 / 262.70 = -0.04271032
I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.
Query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).
This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).
I would like to design and build a database that can match one to one meetings between companies with specific time slots.
For example, we have two sets of people: buyers and sellers. A buyer could specify which sellers they would be interested in meeting. The database would then work out which buyers could meet with which sellers, and when.
I have a table that has a date field set to text. I use the Mid function and get just the month in a query. I have a textbox on a form that when the user adds a month or removes a month from a selected listbox it adds or removes that months number from the textbox.
I want to us that text box on the form as the criteria for the month (the Mid function getting the month) and I can seem to get it to read what's in the textbox and use it as the criteria. I can manually type in a number and it works fine.
I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
I have a form (Datasheet). I need to define some variable in form as string, which can work with any event. E.G in column "A"on event after update, in column "B" on event after update...
I will try to explain with a simple example : form (datasheet) columns: "A", "B"
on event after update in coulmn "A", I could have some like this: (variable what I need to define) = 3
on event after update in coulmn "B", I could have some like this: If (variable what I need to define) = 3 then msgbox "ok" end if
Now I try to work around the problem and use another column "c" to keep the value from after update A - but I know that, it's bad solution -Right?
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code: Option Compare Database Option Explicit Public Sub Populate(lngParameter As Long)Dim frm As Form
I have researched the 'party' data model but it is a bit too complex for what I'm seeking. For those familiar with it, I don't really need the intermediary relationship from-to tables.
I'm interested in ideas about setting up a data structure that will allow users to search contacts or select contacts in dropdowns regardless if the contact type is a person or an organization.
Obviously the fields needed for both are different and the biggest issue is the name field because the person contacts are
The way I am accomplishing it now is writing the company name, or "first name " & "last name" for a person, to kind of a bridge table when a new record is inserted into the person table or the organization table...kind of inefficient.
Is this a relationship thing or should I just write a function to create a temporary recordset when needed?
I have created a query with the parameter for the Domain field. however on the form the user enters this information via a drop down menu. i was just wondering could the parameter box be set to a drop down box as well to save the user entering in the full Domain field name?
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
I managed to count the performance of users. The only thing I need to do is to put the names of the users on the form (under different tab) so I can select them and they show their performance. The best would that they are showing up there in the form till I change the names. I use now a text field to write the name, but when I close the database and open, I need to add the name again.
If I could use somehow the names from the users stored in a table, that would be great.
