Forms :: Expression On Text Box Returning Wrong Value
Mar 29, 2013
I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.
I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]
The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did.
I have an unbound combo box that I set up in a form using the combo box wizard in Access 2010. I selected the option that says, "Find a record on my form based on the value I selected in my combo box." The problem is that the combo box will not always display the correct record. It seems to only display the first record of a group of similarly named records in a table.
Example:
Code: Last Name First Name SSN Smith Alex 123-45-6789 Smith Jane 234-56-7891 Smith Mary 345-67-8910
If I select Smith Jane, the record for Smith Alex will display. I know that this has something to do with the bound column property, but I am unsure what to do to fix the issue.
What I have tried: I tried setting the primary key as the first field, but then I could only search by the primary key, which is unrealistic for this database. Users will be searching based on last name. How do I make the combo box select whichever record I select from the drop down list?
So I have built a form and added a field using the expression builder which is returning #Name? There is a relationship between the table on which the form is based and the record I am adding to a control.
I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.
I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.
I'm selecting only records :
where the DeliveryCountry field is within the EU - works where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work where the Demo/Sale ID is 2 - works
I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.
The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).
One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?
FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") AND (([Order Details].ProductID) Not Like "*Upgrade") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") AND (([Order Details].ProductID) Not Like "*Repair") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") AND (([Order Details].ProductID) Not Like "*Rpr") AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Upgrade") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Repair") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Rpr") AND ((Orders.[Demo/SaleID])=2))
I have a text box that is displaying a value from a table where the display control for the field in the table is a two row combo box. The text box on my form (I'm droping the text box on the form and manually changing the control source) is displaying the second column of the combobox where as I want the first column. If I drag the combobox from the field list onto the form it does show the expected first column (now if i change this to a text box in properties it shows the second column as well). Is there a way to change the text box to view the first column?
I have added this IIF expression in the criteria in a query:
IIf([Forms]![Open_Form]![CLAMT_BET] Is Null;[Tbl_MainWeekly]![Credit limit]; Between [Forms]![Open_Form]![CLAMT_BET] And [Forms]![Open_Form]![CLAMT_and])
When the field CLAMT_BET is empty I get all the data which is what I want. However, when it is not empty, instead of having the "between....and" I get all the records which are at 0
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
Can y'all take a look at this expression and tell me what is wrong? I'm new at this and have found many variations on proper syntax when trying to use dlookup with a combo box. Access 2003 returns the error (#Error) Thanks.
I am attempting to create an expression that will change the font to red if it is an overdue date. It will be on a form with the records showing.
My datebase is for entering, changing, and searching for information dealing with orientation dates, contacts, and associations. My data sheet holds the company name, employee name, date of orientation, due date (orientations are completed annually), contact employee, and status. I would like the date, when opening the form, to show red if it is past due. how to create an expresion to return the status as "Current" or "Overdue", as I am still unsure which method I want to use.
here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.
here's the two methods i tried:
Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test
Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code: Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.screenupdating = False excelApp.Visible = False Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
all tables are 1 to many relationships with referential integrity. every contact can have many Authorizations every authorization can have many payment numbers many payment numbers can have many payments
all payments numbers created are based on the latest Authorization number. a single payment number will have numerous payments listed.
i have tried forms with qry, forms with tbl, forms with forms, sub forms, sub sub forms. i can get so far but then everything stops linking together. ive tried VB ( barr it i am better with Excel!! ) to open forms, link forms.
story. 1. my main form will show the current contact & all details.
2. i click a button and want to make a payment to them.
3. a form should open and automatically create a new payment number with a subform ready to receive payment details in a continuous form layout. i can then either press an apply button on the main form to save or a cancel button to completely cancel the payment number.
after this, i wanted to add a button on this form to add a new authorization number and refresh but i need to get over this hurdle first.
Please Please Please can someone guide me? the rest of my project is based like this so if i can understand how this can be done, i can move forward to complete.
Any help would be graciously received so i can at least move in a direction rather than circles!!!!!
I have a text box on my table form with the expression =([LINES]-[MISSED LINES])/[LINES] which gives me the results I want but I also want this total to go to the "On time %" column in my table.
I know how to do this in VB.NET ... I have a form with a combo box. I would like to create an "on change" event where it updates the text boxes on that same form depending on what's been selected in the combo box.
I don't know how to change the combo box's selected index so it defaults to the first item so there's no blanks/errors in my form.
So when the combobox contents is changed, I'd like a routine that updates all the text boxes kind of like this:
sub update() TextBox = "SELECT * FROM Table_Name WHERE ID =" & Combobox.text end sub
I am trying to pull a number from a text box. I listed the expression for the box below. [Current Record] is a number and Count(*) is a number. I couldn't figure out what field gave me the total records so I did a count instead. If I want to pull the value from just the count portion, what expression would do that?
I have a table called "Cities" which has only two fields; City & Province.I have connected it in a relationship with another table as "Include ALL records from 'Cities' and only those records from 'tblInstallations' where the joined fields are equal."I have put an "AfterUpdate" event which works and everything.
It is working fine and updates the province correctly.The problem is that is putting another city from same province in the "City" field even though I picked up different name.
I have a table with a text field (named Description), which may or may not contain an "ID Number", which is of the form [CHSV]#[A-Z][A-Z][A-Z]## - NB this is not necessarily at the beginning or the end, and will vary between records. For e.g.
So far I have considering LIKE, which is no good because it doesnt tell you where in the string the regular expression is, and INSTR doesnt work with regular expressions AFAIK.
Added bonus difficulty - I use the database as an external source for an Excel pivot table. I created a function (which I named JOBID) in VBA to do the above job easily enough i.e. I added a field "JOBID([Description])", but when I try to use the data in said pivot table, it says "Undefined function JOBID in expression". I have tried copying the function to the Excel workbook, but that doesn't work.
Im using Office 2003 if that makes any difference. Thanks in advance.
=IIf([Details]="COST OF PRODUCT",[PerUnit]*[Quantity],0)
it keeps coming back with 0 even when the text says COST OF PRODUCT
I have also tried: =IIf([Details]="COST OF PRODUCT*",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT*",[PerUnit]*[Quantity],0)
I'm using the expression builder to create it in a total box in the report footer, I have a box that gives me the total of the report using a sum and I need to show the cost of product separately to this as I need to use it in another calculation.
I'm currently in the process of moving from Excel to Access and am setting up a user table with 5 Fields (Full Name, First, MI, Last, Alias). I would like to only transfer the Full name and have the other field be calculated fields, so they automatically fill in. I was able to get the "Last" field to work, but the others are being tricky. Especially the "Alias" field which would need to take the first letter of the first name, MI letter and the entire last name, like (JPDoe).
The current format of my "Full Name" field looks like, (Doe, John P.) without the brackets of course. I'm new to Access, so I was trying to use the Expression Builder to do all of it, unless there is an easier way?
I have a form with a TAB control and four pages. Each control of each page has his TAB index set correctly (starting from 0 to n), some of controls are not enabled to be tabbed. The form's cycle is set on "Current page".
I have two kind of problems.
First: I am on the last control of the page, pressing TAB key the focus gets invisible. Press TAB key again and the focus moves on the first page's control. For each page happens this behaviour.
Second: clicking randomly on the TAB pages the focus is not always on the control with TAB index 0, but usually on the last.
I have read that clicking on a new page the focus should be automatically on the first control, but this does not happen in my experience.
