I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
Sub Service ID (primary key) Sub Service Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
I need to run a parameter query in which you enter a product number and the query (a) returns the list of components and (b) then automatically reruns the query for these components. In other words, I enter an input (a product number) the query gives me outputs (component numbers) and then reruns the query with each of these outputs as the input (i.e. I want to know what are the sub-components of these components). This process stops when all the components are raw materials -- this will be easily identifiable.
Does anyone know how to set-up this type of query?
I am trying to perform a search function query in access but am having trouble. I have two tables. One table has a column in which I need to search the contents of this field by referencing data in another field/table. I am able to write the query searching the data manually (not referencing the other table) but can't seem to get the query right when referencing the other table!
this query works = SELECT * FROM Sample_Data WHERE (((Sample_Data.[Affected_Frequencies]) Like "*451.425*" Or (Sample_Data.[Affected_Frequencies]) Like "*451.400*"));
but, i put together a frequency table (Table name = Frequencies) and the column within that I am searching for is called Frequency. im trying to reference Frequencies.Frequency using a LIKE statement.
Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rates.
For example:
Usage up to the first 500KHW is billed at .067 per KWH Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH Usage from 1000 up is billed at .0318
So if my usage was 1200 KWH...
((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86
I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?
i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like: =IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
Can anyone tell me how I can query a number field for odd and even number. I have table with a field that contain only number which I would like to query out all the odd and all the even number to be able to print on a report.
The fields are somehow related where values of B (unique) is taken from A(unique). I trying to do an SQL such that if I input a single value A=9 or 6 or 5 or 3 or 1, the following should be the output:
A B 9 6 6 5 5 3 3 1 1 Null
Or in another case where I input a single value A=8 or 4 or 2 the following should be the output:
I use a query to return values between two dates, here's the code I use:
Between [Select Start Date:] And [Select End Date:]
However because my dates are time stamped (they need to be!) the query omits anything on the end date, for example:
Between [01/09/2007] And [05/09/2007] will return values for the 1st, 2nd, 3rd, 4th but not the 5th - because (I think I'm right in thinking this but I might not be!) it only returns values upto midnight on the 4th? so 05/09/2007 13.42pm won't show up because it's after 11.59 on the 4th.
I can't ask people to enter in an extra day because quite alot of people who use our database won't remember and it'll cause alot of problems when they forget and get the wrong figures.
I've tried adding the following: " & "11:59:59""
onto the code but Access says the expression is typed wrong or is too complicated to be evaluated.
Has anyone ever encoutered a problem where when running a query to return a dataset which includes a memo field, the query only returns part of the info contained in the memo field?
Do queries limit the return of memo field data to 255 characters? I have run other queries on other memo fields in the past, and do not recall ever having this problem.
FYI, there is no indexing, criteria or joins/relationships on the memo field - whilst returning other fields, I purley wanna return whatever is contained in the field.
I am using the QBE grid and am writing a select query to select only records with an empty Date Closed Field. The Date Closed field is a Date/Time Field. I am using Access 2003. When I use in the criteria IsNull([DateClosed]) I do not get any records selected which have an empty Date Closed field, have I a bug? and if so please could anyone point me in the right direction.
I'm quite new to Access and have inherited a database from a colleague. It is a database of references from which we have extracted pesticide active ingredient, pest species and crop species using forms. I am trying to create a query that will allow us to search and return unique active ingredient x pest x crop species combinations (but which will return all instances of this combination).
The complication is that the pest and crop species names are within the same field ('taxa'). They are distinguished by a label ('pest' or 'crop') in an associated field ('PestorPredator'), but a query using 'OR' will only return a crop OR pest, whilst a query using 'AND' will return no records (because something can't be a pest AND and crop).
I want to be able to create a table that shows the active ingredient, associated pest species AND the associated crop species each in separate columns. We have tried a couple of methods, including UNION and UNION ALL....
I have a db tracking vacation times for staff. One of the fields tracks a members vacation start and end dates. I need a way to pull a report to see who is on vacation based on Date() (today).
For example:
A record for John Doe has him start vacation 08/19/2014 and end vacation on 8/28/2014. If I wanted a report that shows who is on vacation today, 08/25/2014, using the date() function), how would I do this?
I've got a table set up with sales info and all other kinds of data. How do I get a formula to work? Here are the inputs I have: Total sales (by outside company), total purchases (from my company). I want to know what percent of their total sales are made up of the poducts they purchase from me and have that field update automatically. Its a simple formula really, I just don't know how to enter it properly. Thanks.
i need to have a currency field in one of my tables, the user will enter the price in euros, and the output will be in pounds... therefor i need the following formula to work:
[enterdprice]*[current exchangerate] = price in pounds
it displays a div/0 error which i want to remove, i know i need to put this calcualtion in an iif statement but whenever i try and make the formula it never works
A nice easy one for you. I am creating a database, one of the fields is total cost. Is there an easy way to have another field, which calculates the VAT without using a query? I would like this done as soon as the total is entered.
I have done this using a query, but didnt like this way.
I am sure there must be a very simple way of doing this but it has escaped me.
I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
I'm sure this is a simple issue, but so simple I can't find the answer.
I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.
Not sure how or where to create this formula to populate a field in the DB.
As you can tell I'm new to all this and any help would be appreciated.
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
I have a date in "date to engineering" of 13/ 01/2010 but I am not getting a value in my field which should be 1479 my field is just always returning an empty field
Code: =IIf(IsDate([date to engineering]),"",IIf(IsDate([date from engineering]),Now()-[date to engineering],[date from engineering]-[date to engineering]))