constant values

I have a very simple question, which I am not able to resolve though.

I have created a query, which combines records from two tables. Works nicely.

Now I would need to add several columns with either 

a) fix values, which will never change
b) values which apply for all records, but which I would like to enter 
within a form every time I would like to run the query.

The background is that I will use access as a mapping tool, so an original 
file will be uploaded and result in a table with the same name always. The 
query now takes the mappings for another table and combines the information 
to result in a file, which I will extract and load into the target system. 
So this is the reason, why some characters will always be the same and 
others need to be adjusted any time I run the query.

Currently, it always gives me the pop up, asking for the missisng values, 
but I would like to have this easier in a nice form, so it gets easier to use.

Thanks for your help,

Lukas
0
Utf
1/12/2010 12:00:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
680 Views

Similar Articles

[PageSpeed] 1

Type an expression like this into the Field row in your query:
    [Forms].[Form1].[Text0]

Provided you have Form1 open (not design view) before you run the query, and 
the form has a control named Text0, the query will read the value from the 
form instead of popping up the Parameter dialog. You could even put a button 
on your form, and use the OpenQuery action in its Click event.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lukas" <Lukas@discussions.microsoft.com> wrote in message 
news:A4F56F80-C2C1-4EE0-A8F8-80594407B507@microsoft.com...
> I have a very simple question, which I am not able to resolve though.
>
> I have created a query, which combines records from two tables. Works 
> nicely.
>
> Now I would need to add several columns with either
>
> a) fix values, which will never change
> b) values which apply for all records, but which I would like to enter
> within a form every time I would like to run the query.
>
> The background is that I will use access as a mapping tool, so an original
> file will be uploaded and result in a table with the same name always. The
> query now takes the mappings for another table and combines the 
> information
> to result in a file, which I will extract and load into the target system.
> So this is the reason, why some characters will always be the same and
> others need to be adjusted any time I run the query.
>
> Currently, it always gives me the pop up, asking for the missisng values,
> but I would like to have this easier in a nice form, so it gets easier to 
> use.
>
> Thanks for your help,
>
> Lukas 

0
Allen
1/12/2010 12:59:02 PM
Reply:

Similar Artilces:

sub-form to link using a range of values
Hello, Is there a way to link sub-form records to the master-form whereby the link falls within a range of values? Example: I'm looking for offsite lab procedures that have a [service_date] that occurred either on or in-between the hospital [admit_date] and [discharge_date] fields. So, (somehow) display the lab records where [service_date] >= [admit_date] AND [service_date] <= [discharge_date] I was envisioning the master-form having unique records for each patient stay with the fields for their med. rec. number, admission date and their discharge date. The sub-form would ha...

constant trouble
i am having real difficulty with office publisher, it wont open the wizard saying it is missing a pro11.msi file . Any help would be greatly apreciated. Thanks alot james Open help, click detect and repair What version Publisher? -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "James" <fatistuta2001@hotmail.com> wrote in message news:1b95101c44ff7$6f6b5cc0$a001280a@phx.gbl... > i am having real difficulty with office publisher, it > wont open the wizard saying it is missing a pro11.msi > file . Any h...

#value! #3
I have the following formula in my cell: =IF(D13<>"DMO",F12+E13,F12) when there is nothing inputed, I get a #VALUE! in the cell. Is there way to get rid of that #VALUE from showing up in the cell??? I'd like it to be blank -- Message posted from http://www.ExcelForum.com =IF(ISERROR(IF(D13<>"DMO",F12+E13,F12)),"",IF(D13<>"DMO",F12+E13,F12)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "alexm999 >" <<alexm999.163wzu...

Adding value to combo box..
I have a combo box thats rowsource is a table. How can I be able to add new entries into the combo box without going back into the table itself? Stephen -- Message posted via http://www.accessmonster.com There is an event on the combobox called "On Not in List" that fires when new data is entered that is not in the datasource. I suggest using this event to give the user some options about the data, making sure the user really wants to add that data to the table and it's not just a typing error. You can also put some validation in to make sure the data looks like you ...

Making field not required based on other field value
Hi all, Field "Street 1" on the Account form is defined as Business Required. I'd like to keep that way some of the time but change it based on the type of the account. This thread explains something similar but I could not get it to work for me. http://groups.google.com/group/microsoft.public.crm/browse_thread/thread/479ac315f13fa2f9/e54b1fb36bff8844?lnk=gst&q=required+business+field&rnum=2&hl=en#e54b1fb36bff8844 I'd like to keep the attribute definition as Business Required. When an account is opened to be viewed, if a certain picklist is populated, I want to...

How can i copy a value in a cell from a sheet and paste in anothe.
Hello, I ask my question with an exemple : I want to copy a value in C2 from sheet1 to D5 on sheet2. But , if i modify C2 on sheet1, D5 sheet2 has to be modified too. Do you know the function to do that? Thanks :) ( Sorry for my bad english, i speak french usually ) Try putting this in D5 on sheet2: =Sheet2!C2 Maybe this is what you want. Art Hi use the formula ='sheet1'!C2 -- Regards Frank Kabel Frankfurt, Germany "clapton" <clapton@discussions.microsoft.com> schrieb im Newsbeitrag news:F401E623-B2F0-463C-8E0E-04874B24F195@microsoft.com... &...

find highest values
hi, i have 2 sets of data on a spreadsheet both contain data about the number of occurrence a machine went into fault ie. grid 1 OP Occurr op 10 7 Op 30 9 Op 40 3 grid 2 OP Occurr op 10 9 Op 30 4 Op 40 14 what i want to do is compare the two sets of data and which ever is the greater place in another grid Result OP Occurr op 10 9 Op 30 9 Op 40 14 what the best way of doing this ? thanks kevin =MAX(VLOOKUP(J2,grid1,2,FALSE),VLOOKUP(J2,grid2,2,FALSE)) -- HTH Bob (there's no email, no snail mail, ...

#Value!
Hi In cell A1 I have a vlookup formula and =if(sheet1A1="","",sheet1a1) which returns the correct result when blank. In M1, I have the formula =if(a1="","",networkdays(k1,k3))-1 This also returns the required info when sheet1A1 has something in the cell, but when sheet1A1 is empty, M1 returns #VALUE! I have tried in M1 =if(iserror(networkdays(k1,k3))-1 etc, but get the same result (#VALUE!) any help appriciated to tidy up the spreadsheet by removing #VALUE! regards Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/...

array formula to provide all values associated with a given value
From an Access Query. I've got a list with the unique ID's for reports, and the next field indicates what Language they were written in. This was from a Many - Many relationship in Excel, and many of the reports were written in many different languages. ReportID CountryID 1000020 Td 1000020 Tz 1000020 Ug 1000020 Zm 1000020 Zw 1000035 Ao 1000035 Mz I've got over 10,000 records like this and would like an array formula that for each Report ID (and i'...

Date Values
I would like to be able to enter a date as 25.12.2003 and in the next cell over have it formated as December 25, 2003 If there a simple way to do this? Thanks for any help. Sharlene England If your regional settings have a short date style of 25/12/2003, you could enter this formula in the adjacent cell: =SUBSTITUTE(A2,".","/")+0 Select the cell with the formula Choose Format>Cells On the Number tab, select the Custom category Clear the text box, and type: mmmm d, yyyy Click OK Sharlene England wrote: > I would like to be able to enter a date as 25.12.2...

Changing combo to list box for multi select values
Hi all, can anyone help I have a variable listbox, which when i click another list box it fills the first listbox with value relating to my other list box eg when i click customer in one list box it fills the another with all customer name, when i click sales person it fills it with sales people names now what i need it to be able to select multi values in this listbox, it is not bound to any columns as it a variable, how do i get it to select multi value and where would i place this in my code for example would it be after update to that list box Thanks in advance ...

I need to hide points on a line chart if the value is ZERO
I'm using a line chart to display my info. I only have to show the points on the graph. I found where to hide the line so only the points show. The only problem is if I have a value of zero it's displaying a point at ZERO on the axis, this is not what I want. I can't find where / how to (remove or hide) point if a value of ZERO. Is this possible? Please advise Hi, If you do not want the zero to show you can do one of the following - delete it and have empty cell instead - type #N/A - use formula =NA() Select chart and use Tools > Options > Chart and make sure the Pl...

constants
How to you keep a number constant in a formula on the next cell? Example: Formula on line A5 =sum(A1*20) and I want the 20 to remain the same in the formula on the next cell. Thanks! Hi try =$A$1*20 -- Regards Frank Kabel Frankfurt, Germany "Confused" <Confused@discussions.microsoft.com> schrieb im Newsbeitrag news:68E23F17-70D6-45F0-AE23-536E49E78D67@microsoft.com... > How to you keep a number constant in a formula on the next cell? > > Example: > Formula on line A5 > =sum(A1*20) > and I want the 20 to remain the same in the formula on the next cell. &...

ZERO VALUES #3
--A1=1 AND A2=1 AND WHEN I DIVIDE A1 BY A2 THEN IN A3 I GET 1. HOWEVER, WHEN BOTH A1 AND A2 EQUAL ZERO THEN I WOULD LIKE A3 TO EQUAL ZERO IN STEAD OF THE #DIV/0 VALUE WHEN BOTH ARE ZERO. SO THERE STILL NEEDS TO BE A DIVISION CALCULATION BUT WHEN THEY ARE BOTH ZERO IT WOULD THEN BE ZERO IN A3.-- Maggie =if(iserror(yourformula),0,yourformula). ************ Anne Troy www.OfficeArticles.com "Maggie" <Maggie@discussions.microsoft.com> wrote in message news:11E79865-39A9-4CE5-B561-8C16D726E1B6@microsoft.com... > --A1=1 AND A2=1 AND WHEN I DIVIDE A1 BY A2 THEN IN A3 I...

Display columns or rows based on values
I am familiar with the custom views feature in Excel 2007. Is there a way to cause rows or columns to automatically be hidden or displayed based on values in row 1 or column A? For example, is there a way to select a custom view and show only those columns where row 1 = "show this column" as opposed to selecting each column and hiding it and then saving as a custom view. I have a spreadsheet with many columns and likely, many custom views and I am looking for a way to automate this. Thanks in advance, bmac It would be a lot easier if I knew that the columns to NOT s...

Outer Join showing default values instead of Null values
Hi, I have data for some days of a month. I'd like to have values for every day. So I created an outer join against a table with holds a dataset for every day of a year. The result shows null values for all days where I have no values. Is there a way to have an other value then Null? Thanks for any advice Adrian -- Adrian Miller Hi What values? Take a look at COALESCE function in the BOL "Adrian Miller" <adrian.miller@hotmail.com> wrote in message news:O1pjXwnoKHA.5696@TK2MSFTNGP04.phx.gbl... > Hi, > > I have data for some da...

vlookup for words content when there are duplicate values
Hi, Can somebody help me here. Thanks in advance. When I vlookup Material 1 to Material 2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. How formula to use so that when there are duplicate values in Material2, I want to have vlookup searching to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material 2 Duplicate? Project No. 224410 duplica...

How can a value be converted to words
Is there a fromula if I have a total i.e. 123 to be converted in words i.e. One hundrd and twenty three automatically. http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 or http://www.xldynamic.com/source/xld.xlFAQ0004.html tj "Azar" wrote: > Is there a fromula if I have a total i.e. 123 to be converted in words i.e. > One hundrd and twenty three automatically. ...

What has been apparent among all of the Bvlgari Company's watches is its amazing ability to constantly be ahead of its time. It constantly is producing timepieces that are amazing designing and new
What has been apparent among all of the Bvlgari Company's watches is its amazing ability to constantly be ahead of its time. It constantly is producing timepieces that are amazing designing and new features that no other watch company has displayed. And because of this, it has stayed in the light of the public and is growing every year in popularity. at・3校salereplicawatch.com Today, Bulgari Time in Neuchatel, Switzerland has a staff of around 500. It is one of the only companies in the world that is capable of providing assembly and its own production as well. Because of its car...

Hyperlink value crashes Access
If I assign a hyperlink value to a cmd button or in code using the follow hyperlink method, using a link value like http://www.cookbookplus.com/Activate.aspx, the page is accessed [Internet Explorer] OK, but the Access app crashes every time. However, if I instead just target the main web site http://www.cookbookplus.com that page is opened AND Access does not crash. Crashes also occur if a hyperlink field is populated with http://www.cookbookplus.com/Activate.aspx Again, that page is opened in IE, but Access crashes. The targeted page is a simple, safe, publicly available...

XmlNode.Value
Can anyone from M$ or anyone else for that matter explain to me the reasoning behind the intended usage for this property? I was quite surprised to find that this property may be null depending on the XmlNodeType. If one imagines a scenario where some hapless programmer adds a new node to an XML-document, e.g. childNode = doc.CreateNode (XmlNodeType.Element, "foo", ""); and then decides to set the *value* (the "string" between <nodename> and </nodename>) of this node, one could easily imagine that our programmer would look for a method or property ca...

Newbie: counting repeated values ???????
Hello all, I know little about Excel that's why i am posting this message here. I have a sheet with several columns, one of those columns have always one of this 2 values: YES or NO My question is, is there a way to count how many YES or NO are in that column and display the number in other cell? I already tried =+COUNTIF(D10:D240,"Yes") but i get a msg saying my form have an error. Thanks in advance, Helder Portugal Helder Take the + sign out of your formula =COUNTIF(C3:C8,"Yes") >-----Original Message----- >Hello all, > >I know little about Excel...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

Excel 2007 Chart with Zero Values
I have chart data for sales that I add to each day. I hide the lines for the days to come(ie if it's the 5th, I hide lines for the 6th - 31st) so that the chart doesn't go all the way down to zero for the 6th thru the 31st. I'd like to be able to leave all lines unhid, but not have the chart line go down to zero. I've tried putting N/As in those days that I don't have data, but the chart is still going to down to zero - I don't know if I have a setting wrong or what, but any help would be much appreciated. Thanks. Hi, Try either #N/A or th...

Adding to the cell value with a macro
I would like to select a cell with a product code (let's say cell B6 contains Part #ABC). I would like to activate that cell, click on a button to run a macro to change the quantity (lets say quantitiy is in cell A6.) If A6 is currently 0, a click would change it to 1. if it is 2, a click would change it to 3. On a sheet with many product numbers, the macro would start at the active cell, jumpone column to the left, and "1" and return to the cell to the right. I know this is possible, but can not seem to get there. Thanks in advance for your help Private Sub Comm...