Calculated Field Based On SQL In Text Box

Feb 13, 2005

Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)

I keep getting #name? as an error.

I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.

=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP
FROM QryInout &_
WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_
GROUP BY QryInout.ProdType &_
HAVING (((QryInout.ProdType)="Freight"));)

Note that I have tried with and without the &_ and also with or with out the brackets..

Thanks

View Replies


ADVERTISEMENT

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Calculated Field Based On The Value Of The Next Record

Nov 11, 2007

Hi,
I have a query that contains the results of repeated tests on a number of persons. The goal of treatment is to get those test results below 50. I need to find, for each person, the period of time (with a start and an end date) during which their test was below 50. The dataset looks like:

ID + LabID + LabDate + Result + Undet
----+------+-----------+-------+------
101 + 21 + 1/1/2000 + 780 + 0
101 + 21 + 2/2/2000 + 320 + 0
101 + 21 + 3/3/2000 + 50 + 1
101 + 21 + 4/4/2000 + 50 + 1
102 + 21 + 6/6/2005 + 1000 + 0
102 + 21 + 7/7/2005 + 900 + 0
102 + 21 + 8/8/2005 + 50 + 1
103 + 21 + 9/9/2005 + 50 + 1
103 + 22 + 9/30/2005 + 567 + 0

What I need is a query that produces the following:

ID + StartDate + EndDate
----+-----------+---------
101 + 3/3/2000 + 4/4/2000
102 + 8/8/2005 + 9/9/2005

I can also provide an attachment with more data, if that would help. Thanks in advance,

View 12 Replies View Related

Possible To Filter A Form Based On A Calculated Field...?

Apr 12, 2006

Hi.
I have an Order form that is based on an Order table. In this form there are also a number of text boxes (not bound to table) with calculations, and one final text box with a deadline based on these calculations.
Is it possible to filter this form based on the deadline? I can do this by making the form based on a query, but then again I'm not able to edit the information in the form.... Please anyone???

View 2 Replies View Related

Calculated Field Based On Accounting Year

Feb 8, 2006

Within a query i am trying to run historical pension values based on 3 fields from a table:

Value
Year
Period

Period: Is aligned with the accounting year April to March rather than Jan to Dec. So I cannot define Year as 2005 and period 1 to 12.

I am looking to define year as 2005/2006 and then calculated the sum of period 1 to 12.

Has anyone experienced this before and found a solution?

Thanks

View 2 Replies View Related

Question On Query To Flag A Field Based On Calculated Data

Apr 25, 2005

I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?

View 4 Replies View Related

How To Use A Calculated Field To Get Average Of 4 Text Fields

Sep 2, 2013

I have 4 texts fields [DEVICE INSTALL] , [INSTRUMENT STAND], [BRACKETS RACEWAY SUPPORTS], [TUBING CAPILLARY] all can be either a percentage from 1-100 or "N/A" except [DEVICE INSTALL].I would like to calculate the [percentage complete]

my calculation in the field is as follows:
([DEVICE INSTALL]+
IIf([INSTRUMENT STAND]="N/A",100,[INSTRUMENT STAND])+
IIf([TUBING CAPILLARY]="N/A",100,[TUBING CAPILLARY])+
IIf([BRACKETS RACEWAY SUPPORTS]="N/A",100,[BRACKETS RACEWAY SUPPORTS]))
/4

this seems to work to a degree. but for some reason when the [INSTRUMENT STAND] value is not "N/A" the percentage calculated is a wacky value much higher than 100. how could I possibly change my division #?

View 4 Replies View Related

Reports :: Report Thinks Calculated Field Is Text

Oct 21, 2014

I'm creating a report to check for over- or under-stocked items. The report is working fine, gets all the records etc. except that it thinks that the OnHand field from my inventory query is text or something, at any rate not a number. I have successfully set the format of the field in the query to General Number, but that doesn't seem to have worked. Here's the SQL for the report:

Code:
SELECT DISTINCT Signs.SignCode, Signs.SignDescr, Size2.XYdim, qryOnHand.OnHand, Bins.Rack, Bins.Level, Bins.BinNum
FROM (Size2 INNER JOIN (Signs INNER JOIN (Items INNER JOIN qryOnHand ON Items.[ItemsID] = qryOnHand.[ItemsID]) ON Signs.[SignID] = Items.[SignID]) ON Size2.[SizeID] = Items.[SizeID]) INNER JOIN (Bins INNER JOIN InventoryDetail ON Bins.[BinID] = InventoryDetail.[BinID]) ON Items.[ItemsID] = InventoryDetail.[ItemsID]
WHERE (((qryOnHand.OnHand)<=[Check for signs with fewer than:]));

View 14 Replies View Related

How To Make Calculated / Concatenated Text Field Unique

Feb 12, 2014

I have a calculated field which consists of a few other fields concatenated, i.e. [field1] = [field2] & [field3] & [field4].

I need field 1 to be unique when its concatenated, but fields2-4 don't need to be unique. Is there a way to do this?

View 3 Replies View Related

Reports :: Appending Text Item To Each Value In Calculated Field Data

Oct 20, 2014

I would like to append a text item to each value in a calculated field. consider the field name is "Division" and I want to append the word "Division" to the values put out by the field "Division"

My attempt was: Division & " " & "Division". This produced an error in the report.

View 4 Replies View Related

General :: Text Box Based On Field Value

Jun 26, 2013

Is there a way to have a text box filled out based on the value of a field. There will be only 2 possible phrases for the text box.

=IF( <Expr> Forms![Workorder]![Disposition])=("Return As Is","DISAPPROVE","APPROVE")

This is what I have tried but I get syntax error.The form is Workorder and the field is Dispostion. When the Disposition is "Return As Is" then I want to have the text box yield "DISAPPROVE" otherwise it should be "APPROVE".

View 14 Replies View Related

Return Value From A Table Based On A Text Field

Aug 27, 2006

I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.

I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).

I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?

Any help would be appreciated.

View 2 Replies View Related

Print Text Based Upon Field Contents

Jun 23, 2006

I want to print text based upon the content of the field but need more than the IIF statement. For example, if the field contains an "A" I want to print "Active", "C" I want to print "Closed", "N" I want to print "New", etc.

Thanks in advance for any help. You guys have always had an answer in the past!

View 1 Replies View Related

Selecting Based On A Text Field And Most Recent Date

Jul 16, 2007

Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:

device1 data_1a 15.2.2000
device1 data_1b 15.2.2001
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3a 15.2.2001
device3 data_3b 15.2.2002

So what I'm looking for is:

device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3b 15.2.2002

Thanks for any help you can offer!
-Eric

View 2 Replies View Related

Generate Value For Text Field In Report Based On Query

Jan 20, 2008

Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report.

Basically if the field HOTOtal shows any vaue between:
16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below)

I thought I could do a case statement on report open but I am getting an error that my expression contains no value.

Here is the case statement

Private Sub Report_Open(Cancel As Integer)

Select Case Me.HOTotal

Case 16 To 30
Me.HOType.Value = "Definitely evening type"
Case 31 To 41
Me.HOType.Value = "Moderately evening type"
Case 42 To 58
Me.HOType.Value = "Neither type"
Case 59 To 69
Me.HOType.Value = "Moderately morning type"
Case Else
Me.HOType.Value = "Definitely morning type"

End Select

End Sub


What else can I do?

View 2 Replies View Related

Selecting Records Based On Part Of A Text Field

Oct 12, 2006

I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"

I want to run a query against the table to select records for any one country, but not sure how to do this.

Any suggestions please?

View 4 Replies View Related

Forms :: Formatting Text In Report Based On Yes / No Field

Jul 25, 2014

I am having problem on formatting text on a report based on a Yes/No field using Conditional Rules Manager in Access 2010. When adding a rule [field name] = Yes the font color does not change. I have tried setting the expression to [field name] = True and still does not change the color. Adding a Yes or True in query works OK.

View 12 Replies View Related

Forms :: Color Text Field Based On Value Of Combo Box

Feb 28, 2015

I have a table named ColorValues with the following three fields:

- ID
- MunsellCode
- RGBValue

30 fields from another table called Color_Analysis are linked to the above with one to many relationships (I've already thought of other ways to normalize data, but this is the most efficient, so no need to go into this topic).

Now, I've constructed a form for Color_Analysis and all 30 fields recording color are included as combo boxes bounded on the ID in ColorValues and displaying the MunsellCode for the user. I've also created unbounded text fields next to the combo boxes and want to use them to display the color that the user selected in the ComboBox. The question is how do I do this?

Essentially I need a piece of code that picks up the value of the combo box (this is essentially the ID in ColorValues), looks up that value in the ID column of the ColorValues table and uses the corresponding RGBValue of the same table as the .BackColor for the unbounded text field.

View 8 Replies View Related

Creating Relationship Based On Part Of A Text Field

Jan 27, 2014

Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.

Things I've tried:
* Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields
* Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on

View 4 Replies View Related

Modules & VBA :: How To Make Particular Text Field BOLD Based On Value From Query

Jul 20, 2015

I am using MS Access 2007 and have a report which display the list of values (say 10 fields) from the basic SELECT * FROM table query.

I need to dynamically set the BOLD based on the particular value for the fifth field which was assigned from Query. if it doesn't match it should display normal.

E.g
If Field_5 = "Agent" then
Field_5.FontBold = True
Else
Field_5.FontBold = False
End if

I tried the aforementioned code and it dont work. ALso i tried achieve it using below listed options.

Me.Field_5.FontBold = True
Field_5.Properties("fontweight") = 700

But nothing worked.

View 5 Replies View Related

Tables :: Populate Date Based On Specific Text In Another Field

Mar 27, 2013

I would like the "DateOfConfirmation" to populate with today's date when "SSurvDiagThisYear" is Confirmed.

The DateRecordCreated is a simple =Date() that populates when the record is entered into the table.

SSurvDiagThis Year is the case outcome - Pending, Probable, or Confirmed. It is possible that the case could be confirmed on the same day it was entered into the table but that is RARE.

I am hoping for the The DateOfConfirmation to capture the date the case is confirmed so that I can gather some duration between the case being opened to confirmed.

SSurvDiagThisYearDateOfConfirmationDateRecordCreatedIdentificationNoPending12/31/2001269Pending4/1/1999270Confirmed7/29/2001338Pending5/14/2009375Confirmed2/20/2012440Pending3/30/2001543Pending7/1/2000552Confirmed3/30/2001596Pending8/3/2001649Pending6/15/2001672

View 1 Replies View Related

Forms :: Conditional Formatting Text Boxes Based On Yes / No Field

Jan 8, 2015

I am trying to get the text in a couple text boxes to turn green if a certain Yes/No field is left blank.

I know the conditional formatting is working, because condition 1 and 3 are working fine (those expressions are referencing text fields).

I would like to have Condition 2 look at [DM_Approval] from the bound query and if it is unchecked, execute the conditional formatting.

I've tried all kinds of statements, but i'm starting to think that the expressions in the conditional formatting window just won't work when referencing a yes/no field.

Specifically (at the very least) i know i tried [DM_Approval]=False, [DM_Approval]=No, [DM_Approval]=0, and [DM_Approval] is null.

View 3 Replies View Related

Forms :: Sort Values / Text Based On Active Field

Feb 5, 2014

In a form is there a way to change sort order based on which field is active?

I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.

View 3 Replies View Related

Forms :: Checkbox Based On Status - Updating A Text Field

Mar 8, 2013

My database has a text field "Status" where the text is either, A, W, C. There is a form to update this field; currently it uses a standard Access created text field. The users want a checkbox which will show up as checked when the status is W, if the status is anything else, the checkbox will be blank. If the user clicks the checkbox within the form the status will be changed to W.

If [table.status] = 'W'
then checkbox = 0
else
checkbox = -1

and then somewhere on the update it would be

if checkbox = -1 then [table.status] = R

Except that Access doesn't think the way I do.

View 1 Replies View Related

Modules & VBA :: CASE Statement - Display Specific Text In A Field Based On Value Of Another

Sep 22, 2014

I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.

View 2 Replies View Related







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