New Field Result From Concatination

May 30, 2007

:confused: This seems like a very basic task, but I am confounded. I am trying to display results in a separate field from the concatination of 15 text fields in the same table. This result will eventually display as a string on a web (intranet) page.

I got the concat to work in a query, but getting this same result in the table is eluding me. I am working in Access 2003.

Thanks in advance for any advice anyone can give me.

View Replies


ADVERTISEMENT

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

Reports :: Show A Field Based On Result Of Another Field

Dec 4, 2014

I have a list of all previous employers for a job applicant. I can enter up to 10 but I only want the boxes to show if there is a previous position for that box. So, if there are 3 previous positions, I want to see 3 lines. If there are 10, I want to see 10. I have fields [Company_1]. [Company_2], etc. I only want company 2 to show if the person has 2 previous positions entered into the form.

View 1 Replies View Related

Can One Field In A Table Be Used To Limit The Result Of Another Field In The Same Rec

May 25, 2007

Hi,

I have a fairly simple database that looks like the attached. It is the beginnings of a school database. Unfortunately, the MS template doesn't quite do it.

We have around 700 pupils in 5 year groups. Each year's intake is assigned the year of that intake (e.g. 2002, 2003 etc).

The youngest pupils are in Year 7 whilst the oldest are in year 11.

Each Class is assigned based on the intake year but given the name of the schooling year - (e.g. year 7, year 8 etc). And there are 6 classes per year - hence names 7.1, 7.2, 7.3 etc.

My "Students and Classes" table should combine both the classes and pupils. However, I want to be able to firstly select the class to put the pupil into (and hence infer the induction year), then just show in the next filed only
the pupils that belong to that induction year and hence go into the selected class.

However I design my lookup or query, I can't seem to figure out how to do this, or if this can even be done.

Any help is much appreciated.

Chris

View 14 Replies View Related

Field Calc No Result

Feb 4, 2008

Ok, I know it's simple but...

subtracting field B from field A.
Field A has data in all records, field B only some. The 'only some' I get a result but the ones that have nothing in B I get nothing even though A has data.
Both are currency and default is 0.
How do I take nothing from something and get nothing you ask ?

View 6 Replies View Related

General :: Adding Count To Result Of Query Depending On Month And Result

Aug 18, 2013

I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?

I'm using access 2003.

View 4 Replies View Related

Calculate Query's Field With Two Result

Sep 28, 2005

Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.

View 1 Replies View Related

Putting Query Result In One Field

Nov 7, 2005

Hi,

I have a two column table and I want to display all records in the second column but I want it to be displayed in another single field.

Anyone has a trick on this?

Example:

Column1 Column2

001 Basket
002 Jar
003 Pillow
004 Hat


Result should be something like this:

Basket, Jar, Pillow, Hat

View 1 Replies View Related

Populating Field With Query Result

Oct 17, 2006

I have got 2 tables:

TAT table with fields: ID, SPB, SET, DAYSIN, HRSIN
SETS table with fields: ID, SPB, SET, DAYSIN, HRSIN, plus a few other fields.

Is it possible to do the following:

If (SPB.TAT = SPB.SETS) AND (SET.TAT = SET.SETS)
then store
(DAYSIN.TAT in DAYSIN.SETS) AND (HRSIN.TAT in HRSIN.SETS)

Thanks for any help with this.

View 4 Replies View Related

Queries :: List Field Result In One Row

May 3, 2013

How can list the contents of one field columns in one row in a report.

Example:
ID: 1
A1
A2
A3
A4

Should be: ID1 - A1, A2, A2, A3, A4

View 3 Replies View Related

Forms :: Updating One Field On The Result Of Another

Jul 2, 2013

Updating one field on the result of another, again this I can do BUT how do i get it to do this for all records? I have tried the on load property but it only updates the first record?

View 3 Replies View Related

Combobox To Display Result In A Text Field

Apr 12, 2006

Hi All, I hope someone can help with this, I have 2 tables, 1 main table that holds all my part data ( ie part num, description, etc) and a second table with vendor info. On my Form I have all my fields that display the record. I placed a combo box on my form that I need the user to be able to select a vendor, which is working but I need the form to show the vendors part number in a field. The main table has a manufactures part number, and I have 3 fields that have my 3 vendors part numbers for that part in it. I have the combo box so that it shows the vendor name but how do I get it to look at a certain field for the vendor part number. Im still really new to Access and am clueless any help would be greatly appreciated. I know I have not explained this every well so I am attaching a sample of the DB so you can see that I have Thanks again

Thanks everyone..

View 4 Replies View Related

Filling A Table Field With A Query Result

Nov 23, 2004

I have a query_ReimburseResult
that counts the yes/no answers in field Reimburse from tbl_Survey.

How do I take the results from the query and put it into a field in another table?

I have tbl_Result and a field called ReimburseResult. I set the type to long integer and then what?

thanks!

View 2 Replies View Related

Search Result Based On The First Letter Of A Field

Sep 29, 2006

Hello,

I need to create a query that will seach through a column and only return a result with product codes starting with A.

An example of a product code is AA0000034567.... however there is no realy consistancy. Some start with the AA and some don't. Some have 5 zeros before the numbers start others may only have 1, 2, 3, or 4 zeros before the number.... There are approx 76,000 product codes in total.

My end goal is to create a query to find all the records that have a product code starting with A so that I can eventually delete the AA00000 portion of the code and strip the number back to the point where the 34567 starts.

I figured that by separating out the product codes that start with AA would be a good start.

If any one has any suggestions please let me know.

Peter Vav

View 4 Replies View Related

Form Field Value Updated From A Query Result

Jun 15, 2006

Hi

I have a Form called Products. Each product is uniquely identified with a primary key called [ProductID].

Products contains a calculated field called [UnitsOnHand]. [UnitsOnHand] gets its values from a subform called ProductTransaction that exists within the Products Form.

ProductTransaction contains a field callled [Received] . At the moment [UnitsOnHand] will add up all the values it finds on the [Received] field to derive the [UnitsOnHand] value for that product at any particular time ie =Sum(nz([Received])) on [UnitsOnHand] .

I also have a Query AutoSell that outputs a value for each [ProductID].
How can I use these values for each [ProductID] generated by the Query to substract from the values on the [UnitsOnHand] calculated field on the Product form.

ViRi

View 1 Replies View Related

Queries :: Copy Result Of Expression In A Field

Dec 5, 2013

I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.

exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)

"exp4" is the clean name of the song.

Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?

View 2 Replies View Related

Modules & VBA :: Update Field With Calculated Result

Mar 19, 2014

I am trying to update a field with the result of what is in the field plus the contents of a unbound form field.

So if crm_order_det.Qty contained the number 5 and the form field (labelled qty) contained 7 I would like the field crm_order_det.Qty to end up with 12.

The code I am using

sql = " Update crm_order_det SET [Qty] = (" & _
frm!Qty + crm_order_det.Qty & ") where crm_order_det.part = '" & frm![SearchResults] & _
"' and order_id = " & frm![order_id] & ";"
DoCmd.RunSQL sql

However I get the following error

Run-time error '424': Object required.

View 3 Replies View Related

Forms :: Populating A Field With Result Of Pull Down

May 16, 2014

I have a combo box [Description] populated with items and a corresponding cost in [PriceRetail] within the same table. I require the price to be added into the price field once the item is chosen in the Description pull down. This is what i have, but the price field never populates.

---------------
Private Sub Description_AfterUpdate()
Me.Price = Me.Description.Column(3)
End Sub
---------------

The source table has 4 records in this order; ID, Category, Description, PriceRetail

View 1 Replies View Related

Queries :: 2 Inputs For Same Field - Query Result Not Correct

Sep 4, 2014

I have a query that allows 2 inputs for the same field ( batch no )

if you enter "pco0093" only you get 4 results
if you enter pco0094 only you get 4 results

both have the same part numbers is the results

When I run both together pco0093 and pco 0094 and use the "group" function on the field batch no I get 8 results ( as expected)

if I use the " where" function I expected to get the 4 results but with the " quantities doubling

What I am getting is more than 4 results but 5 with a part that was not on any of the single results

File attached ....

View 9 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

Forms :: Validate Field Data If Result Exist In Query

Jun 19, 2013

I have a form that contains two text fields i.e.; [Scat_text] and [STyp], which each can be changed by the users. I would like to combine the results of the two fields together and validate if the results exists in a query. What would be the best way to accomplish this?

View 1 Replies View Related

Queries :: Field Is Empty - Query To Update Table But No Result

Feb 26, 2015

I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.

View 2 Replies View Related

Result In Query Not Showing Because Field Not Filled In (Joined Tables)

Apr 23, 2014

I have a query, that I have a criteria to show appointments in the past (< Date()) but one result doesn't show up although the appointment end date is a past date, it only shows up when I fill in a field that is in another table that is joined and part of the query. But there's no criteria there for it to not be null.

View 3 Replies View Related

Forms :: Control Setup - Bind List Box Result To Table Field

Nov 5, 2013

I want a List Box on a Form that takes its available drop-down values from tblDiscipline and puts the value selected into the Discipline Field of tblEquipment.

The problem is I set the Control Source of the List Box to tblDiscipline (so the List Box can use these values for the drop-down selections) and when I make a selection it tries to put it back into tblDiscipline instead of tblEquipment, because I can't find anywhere to set the table that the result should be Bound to.

How can I set up a control, such as a List Box, so that it gets its values from one table but returns them to another?

View 1 Replies View Related

Queries :: Two Tables To Result In Single Column With Field Name Identifier For Each Record

Apr 27, 2015

How to get this one to display in a single column.

I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.

Customer Table with PK Customer_ID.

There are two tables with FK Customer_ID.

1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.

2. Table Lease2 - Has 1 field with 0 to Many records.

Goal:

The Type shows up in a single column.

Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)

Challenge:

Lease1 table has 3 fields that need to be transformed into a single column.

Lease2 table has 1 field to be appended to the single table.

Then, there is the column that identifies where the data came from based on the column name.

View 6 Replies View Related

Queries :: Calculated Field - Return Zero If Negative Result (Access 2007)

Oct 10, 2014

I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.

The only way I know how to do this is by an IIF statement :

Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...

(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)

Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.

View 2 Replies View Related







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