Altering Text Display Using Iif Function
Oct 17, 2007
Let me start by saying I am very, very new to Access, but have been assigned a report to create.
I have the following textbox in my report:
=[Location]+(Chr(13) & Chr(10)) & [callnumber]+(Chr(13) & Chr(10)) & [Online Availability]+(Chr(13) & Chr(10)) & [PF Subject]
I'm using this format so that my 4 fields appear in a list and don't leave blank lines if the one of the fields happens to be empty.
The problem is that I need to alter or substitute some text.
First, in the location field, I can have several different entries, but if the entry is 'Circ', I don't want that text to display and I don't want a blank line either.
Secondly, in the online availability field, if there is any value in the field, I want whatever text that is to change to something like 'Online - Yes'. If there is no value in the field, nothing should display.
I think the use of the Iif function should work, but I can't seem to write it correctly. Help!!
View Replies
ADVERTISEMENT
Mar 12, 2008
I tried to revive an old post in the "General" section with this question, but it doesn't look like many people are looking at it. I figured it wouldn't upset the posting gods too much if I posted it here, since the the question is concerning queries.
I'm starting out with an excel sheet that looks like this:
Company Name..........Employee
Jones Electric............Kyle Brown
Jones Electric............Chris Ford
Jones Electric............Matt Daniels
Is there a way for access to make it look like this?
Company Name..........Employee..........Employee 2..........Employee 3
Jones Electric............Kyle Brown........Chris Ford............Matt Daniels
Thanks a lot, guys. I'll be sure to link the old post to this one.
View 6 Replies
View Related
Nov 9, 2005
Hello, I use a database at work that I made. Unfortunatly, I did not add a field for numbering the items that a customer orders. When entering an order It is important to my customers to keep it in a certain order. how do I change it so it adds a numbering to all the past orders as well? I've been trying to think of a way for months and cannot do this on my own. Thank you to anyone who takes the time to help me out. If you have any questions I'll try to answer them the best I can.
View 6 Replies
View Related
Jan 6, 2014
My database has Main form and a Sub form. On main form i place one unbound text box named investigations. In subform of which datasheet there is a column named TestCode. I want unbound text box (Investigations) to display row values which selected in a column (TestCode) of subform.for example:Investigation field should display "CBC,HB,ALP".
View 5 Replies
View Related
Apr 15, 2014
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
View 9 Replies
View Related
Dec 13, 2005
I have a line chart built in Access. Is there anyway I can change the line colors like I can in Excel? The default fuschia, bright yellow, and bright blue are horrendous.
View 3 Replies
View Related
Dec 7, 2007
Hi
I'm trying to produce a database that will convert Ebay download files to a format more suitable for import into Sage Line 50. I have a table "Customers" that has all the customer information in it.
One of the fields is "Country", this lists the full name of the country the customer lives in. However, Sage requires countries to be listed as a two letter abbreviated identifier, GB, US etc.. I have another table "CountryCodes", with two fields, the countries full name and its abbreviated identifier. I need to be able to compare every "Country" field in the "Customers" table with with the "Country" field in the in the "CountryCode" table and change it's value accordingly. I also need the "Country" field to change to fixed value (ZZ) if the country is not found in the "CountryCodes" table.
Any help would be greatfully recieved.
View 1 Replies
View Related
Jan 9, 2006
Ok, so I have a form that I am using to apply certain parameters to a report. The form allows the user to specify one or more fields to sort by and then launches a Report with those statements in the Report's where clause using DoCmd.OpenReport. However, when certain fields are searched, I need to add information to the SELECT and FROM parts of my Report's query. I can't figure out how to do this with Access, if it is even possible. Otherwise, I will need to find a way around this.
Thanks for your help,
Alex
View 8 Replies
View Related
Jun 18, 2014
When I mouse over TEXTBOX1 I want it to display the phrase Hello World in TEXTBOX2.
When the Mouse moves away from TEXTBOX1 I want TEXTBOX2 to go back to normal.. (Empty)
How can I get the below VB to work? Or something similar.. I'm assuming a mouse move event or something
Code:
Private Sub TEXTBOX1_GotFocus()
Display Hello World in TEXTBOX2
End Sub
Code:
Private Sub TEXTBOX1_LostFocus()
Clear TEXTBOX2
End Sub
View 1 Replies
View Related
Nov 30, 2004
It's written a Function which takes an Integer as Arg, runs a Select Statement and returns a String.
Such that the Control Source of the Forms reads....
=ConvertCompanyNumbers([12]) = where [12] is the Name of the field. Not My choice to have numbers as Fields.. but there it is.
Anyway.. I'm getting an #Name? error... also tried [Ctl12] but again same error.
I know the function works, as I can use something like =ConvertCompanyNumbers(12) and it displays the appropriate result.
What is up with this?
View 2 Replies
View Related
Aug 19, 2013
I have loads of projects in a table, they all have a status (eg Red, Green, Amber).
I have a query for each so if you run qry_RagRed, it will show in a sheet all Red projects and obviously have a total in there.
So I have a form which has buttons on it for navigation, what I want is to display various things on there so for example, a field that simply has the number of red projects, or green etc.
All I want it to do is tell me a number based upon number of items in a query.
View 5 Replies
View Related
Jul 1, 2013
I have an access report that there is One column among 9 of them, refuse to give answer.. Its suppose to Sum all the total of each line in the column. See Attachment. This report is group by ID and sort by Shift. The formula is: Sum([Rates]), and the Rate is list in Details Report where the Sum ([Rates]) is located in Id Footer section.
FYI, when I ran this report, it does not know what the Rate is. However, the rate is the formula which listed accordingly in the report details.
View 3 Replies
View Related
May 22, 2015
I have a database containing a mixture of upper and lower case input. I can make upper case appear on the screen by using > in the format box in the property section for each field. This works great but the same old uncorrected stuff remains on the database. How can I get the database to store the corrected upper case data after I have entered it in lower case?
View 11 Replies
View Related
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
View 4 Replies
View Related
Mar 1, 2014
I am new to MS Access. So I am making a database for an automobile warehouse. When a new car arrives at the warehouse, it is allocated a specific parking slot (using a combo box available on the 'New Arriving Car' form).
However, once a specific slot has been allotted, the end-user should not be able to allot the same parking slot to other arriving cars, until the original car has left the warehouse so that the parking slot becomes available once again. Currently, the parking slot combo box shows all parking slots whereas it is supposed to display only unoccupied parking slots. How can I enforce this? (Note that there is a separate form called 'Exiting Car', which is filled when a car is leaving the warehouse).
Fyi, each car is referred to by a unique 17 digit code - so this is the primary key. Also, there are 120 parking slots available in all, with the slot identifiers ranging from A1, A2...A12 to J1, J2..J12 (10 x 12 = 120).
View 5 Replies
View Related
Dec 6, 2006
I am trying to run a IF, THEN expression.
What I have created is :
Private Sub Form_Open(Cancel As Integer)
If Me!Type = "Workboat" Then Me!DWT.Visible = False
End
This works fine but where the TYPE field is enetred as another category other than workboat, the DWT field is still missing. Am I missing the Else part?
Please help!
View 6 Replies
View Related
Oct 19, 2005
Hi,
I'm new to Access and was wondering if there is a function that can split/divide the contents of one field into seperate fields like you can do in Excel via the 'text to columns' function. I have people's names (surname christianname in one field in a table and want to have a query that splits them up by the space between the names and put one in one filed and the other in a seperate field).
Hoping someone can help me..
Thanks.
Greg
View 1 Replies
View Related
Jan 23, 2006
Is there any way to set the ControlTip Text for a Text Box to be the value of the Text Box using a function?
If so what event would you use?
View 3 Replies
View Related
Jan 20, 2005
txtNotes is a text box on my form.
I use this code to put an AutoText into the box:
Code:txtNotes = "New Appointment: " & Chr(13) & "Sales Rep.: " & Chr(13) & "Date and Time: " & Chr(13) & "Contact: "
I would expect the text appear in the text box like this:
New Appointment:
Sales Rep.:
Date and Time:
Contact:
...but it appears in ONE single line with a square between the 'labels' instead of a CRLF, like this:
New Appointment: {square}Sales Rep.: {square}Date and Time: {square}Contact:
I tried it also with Chr(10) but came to the same result.
Any idea how to overcome this?
Show me the door please...:-)
View 2 Replies
View Related
Apr 12, 2007
I've been searching for several hours now and can't find the answer to this problem...
I'm using a form to capture criteria for a query. One of the fields is a text field and here is the parameter I'm using:
Nz([forms]![frmMain]![Company],[CNm])
I want to return all records if the text box named Company is null. I'm getting only the non-null records. How can I get it to display all the records in the field, CNm?
Thanks!
View 2 Replies
View Related
Sep 6, 2006
Hi Guys, I am willing to design a form where I could browse the files and a button should read from text file and insert values into my Table. Please help me in this regard.
I want to have a function with parameters like text file name and destination table. Any clue will be highly appreciated
Cheers
Abid Hussain
View 4 Replies
View Related
Oct 7, 2013
I am trying to rearrange text contained in a mid function. my code is:
Private Sub MaterialA_Reportable_Quantity_AfterUpdate()
Dim USDOTlen As Integer
If Me.MaterialA_Reportable_Quantity = False Then
USDOTlen = Len(Me.MaterialA_US_DOT_Description)
Me.MaterialA_US_DOT_Description = Mid(Me.MaterialA_US_DOT_Description, 7, USDOTlen - 6)
[code]....
How do I rearrange the mid function to do this?
View 2 Replies
View Related
Apr 17, 2006
Hello Experts,
i have this small problem with my qry results not being displayed in the text box.
tbl Issues= status field and many other fields
tbl Status= Active or Closed
here is my qry
SELECT Count(Issues.Status) AS CountOfStatus
FROM Issues
GROUP BY Issues.Status
HAVING (((Issues.Status)="Active"));
I have tried typing this code on the control source of the text box
=DCount("PartNumber","Issues","Status=Active")
but it doesnt work.
All i want to do is display my results in a text box on a form.
Any help
Thanks alot
View 2 Replies
View Related
Sep 27, 2006
Hi,
I have ADP project + SQL Server Express.
I need display in Form textbox User Name (with then I log into SQL Database).
Thank You in advance for answers.
View 2 Replies
View Related
Dec 14, 2005
Hi
I'm having problems trying to get an If statement working on my form.
What I have is a form with two combo boxes linked to a Calendar when you click on the arrow. I can get the dates I select from the calendar into the combo boxes which works perfectly.
What I wanted to do then was create a textbox to list the number of days between each date.
I have created an If statement for me to do this, which works fine.
The problem I am having is that it doesn't seem to update these changes on the form after the date has been changed.
I have placed the code in the AfterUpdate event on both of the combo boxes and also in the Form Current Event.
Anyone any ideas?
If you want to see the If statement let me know, but I don't think it's that.
Thanks
View 2 Replies
View Related
Dec 7, 2006
Here's a strange one.....
We have only 4 computers in our office. Our DB backend is SQL server and due to constant corruption of the database from multiple users, each user/computer runs its own separate copy of the mdb file.
I have set up a small form in a separate database to allow me to quickly compact the DB and copy it to the 4 locations on the network instead of doing it manually. This process works fine...BUT....I have a text box on the form set up to display the copy progress and the contents of the text box do not properly display during the file copying. Even though the code to change the value of the textbox comes before the file copy command, the textbox will not display the until after the copying is finished. However, when I step debug the process it displays the information correctly before the file copy command executes.
Here is a sample of my code...
Dim strPath1 As String
Dim strpath2 As String
strPath1 = "C:FortuneSystemTemp.mdb"
strpath2 = "\ScottFortune"
If Dir("C:FortuneSystemFortune_System.ldb") <> vbNullString Then
MsgBox "Cannot proceed! Fortune database is open.", vbCritical
Exit Sub
End If
txtProgress = "Compacting Database"
'compact master DB to a temporary DB file
DBEngine.CompactDatabase "C:FortuneSystemFortune_System.mdb", strPath1
txtProgress = ""
If chkTed Then
If Dir(strpath2 & "Ted.ldb") <> vbNullString Then
MsgBox "Ted cannot be copied. Program is running.", vbCritical
Else
Screen.MousePointer = 11
txtProgress = "Ted Copying"
Kill strpath2 & "Ted.mdb"
FileCopy strPath1, strpath2 & "Ted.mdb"
txtProgress = ""
Screen.MousePointer = 0
End If
End If
....etc...
Can anyone explain why this is happening and possibly a way around it?
Thanks
View 2 Replies
View Related
