I have a phone number field defined in a table as text and use the input maks !(999") "000-0000 for entering and editing data. Is there a way to use a mask when using this field in a list box? It would be nice to be able to show (555) 123-4567 instead of 5551234567.
Thanks,
Ted
I am using a Multi Select List Box to display a list of names from a table, in order to select e-mail recipients. The names are in two fields: [Main]![First Name] and [Main]![Surname].
I have done this by using the following code for the rowsource:
SELECT DISTINCTROW Main![e-mail], Main![First Name], Main!Surname FROM Main;
This gives a rather undesired effect, in that shorter first names have a large gap before the surname. In addition, there is a dull separating line between the two columns, thus:
Jane | Smith
Bartholomew | Simpson
My questions:
Is it possible to arrange the names so they appear in one column, with a space between them? (i.e. they would appear as written)
If not, can I get rid of the separating line?
Many thanks in advance if you can help!
I have a table of data (codes & amounts) which I want to display on a form via a list box (purely for information purposes; the list box will be locked / disabled). Basically, the list box will mimic a pivot table as it would appear in Excel, albeit without any of the filtering functionality.Codes can appear multiple times in the source table, each with a different value assigned to it.
The list box should have 3 columns :the unique code strings
the number of instances of that code string (i.e. Count)
the total value assigned to that code string (i.e. Sum)
In descending order based on the number of instances of each code
I have the following query set up to pull the data :
Quote:
SELECT tblData.Code AS Expr1, Count(*) AS CodeCount, Sum(tmp.Amount) AS CodeSum
FROM (SELECT Code, Amount FROM tblData) AS tmp
GROUP BY tblData.Code
ORDER BY Count(*) DESC;
I want the 3rd column of the listbox (the summed value) to appear as a formatted $ amount rather than a raw floating point. So "$10,000" rather than "10000.00". And, if at all possible, right aligned.But I have no way of formatting the columns of the list box (that I am aware of) either through the listbox properties directly or by VBA indirectly.Is there another way I can do this, either by applying the formatting to the query itself or any other trick to somehow apply the formatting as I need it?
My table fields are as follows:
1. Issued? (This is a lookup field with the following lists "Yes"; "No"; "NA")
2. IssueDate
I created a subform bound to the table.I wish to accomplish the following on the form:
1. If "No" is selected on the [Issued?] field, I want the [IssueDate] to gray out.
2. If "Yes" is selected, I want [IssueDate] to be required.
I set Conditional Formatting as follows:
Expression is ([Issued?]="No"), condition is disabled.
When I tested it, [IssueDate] grayed out when "No" was selected. However, when I select "Yes", it still remained grayed out.
I want to add a conditional formatting to a combo box to highlight if the value is not on the list.There is a process to read in data from a 3rd party excel sheet that has truck arrival dates and times. My form displays this data and allows the internal users to change it. My form has the "Trucks" field as a limit to list combo box - so basically any data can be read into this field but internal we can only change it to trucks on the list (from the trucks table). I want to highlight where the trucks value is not in our trucks list.
View 2 Replies View RelatedI hope someone can help me with this issue. Currently I have numeric data that I need to convert. For example if a quantity is 42.5 in my data I need to format it to look like 00000042500 similarly if the quantity was -42.5 I need to format it to look like 00000042500-. Any assistance anyone can provided would be greatly appreciated.
View 5 Replies View RelatedAccess 97 SR1
I am importing a file that has two numeric fields in it. I was asked to import as fixed length text fields with leading zeroes and no decimal point.
I am supposed to export the data in Csv format.
Source field from IBM iSeries DB: quantity is 15,3. Quantity source value is 9, requested text format of five characters 00009, the Csv result is 9. The field in Access is defined as Text 5 characters.
Source field from IBM iSeries DB: price is 17,4. Price source value is 1020.0000, requested text format of nine characters 000001020, the Csv result is 1200.00. The field in Access is defined as Text 9 characters.
Thanks,
Dave
I have to create a new table for some data I have from work. The data was imported within quotations though, so it won't let me copy and paste it untill the quotes are removed. I have over 3000 data values in each column, so I can't just go through and delete every quotation mark. Is there any way for me to select a column and remove the quotations around every value?
I have the XP version, by the way.
Thanks !
I hope that someone out here can help me with this. I have recently had a format change from how I received updated data for my database. I used to update my tables from TXT files. There were several update queries/macros written to import the data from 5 or 6 different TXT files into one table. Well; my source has changed :eek: The downside: I can only get my data from an XLS or CSV file now; but the upside is all the data is only in 1 file.
The macros/queries were written years ago and I've been having migranes trying to figure out how this was all done. So, i've sort of started from scratch. I can easily import the data into a table; I have even gotten our front end of the database working so it's just a click of the mouse again.
The problem I have is that the data that is in this table is linked everywhere throughout this spiderweb of a database! I was unable to import my data into the existing table because that table's field names are different than what I have in the XLS file. I get all sorts of errors when I do this. So I am currently importing into a new table. (I.E. MASS and MASS Imports)
Is there an easy way to update data from table 'MASS Imports' to 'MASS' ?? what I was thinking was to use an update query with both tables and link each line.... Or am I way off base? Is there an easier way to do this?
Thank You!!
I have a centralised database that imports data from several other databases at the click of a button to produce a "global" query of data.
This is presented as a datasheet form which I am then able to filter. This is a subform to my main form.
I have a button on my Main form with a macro to ExportWithFormatting to excel. I was under the impression that exportwithformatting exported the filtered data.
How to export my filtered query data to Excel and not the whole query data?
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
View 11 Replies View RelatedExport Query Data with Formatting and Layout Using A Button On Form Before Or After DoCmd.TransferSpreadsheet
View 4 Replies View RelatedI inherited an Access 2000 (2002?) database that uses a textbox behind another to change the background color based on two different fields.
The Format property is set to: "[Green];"[Yellow], the Data ControlSource is set to:
=IIf([chkCheckedIn]=True,1,IIf([PassExpired]="NO",1,-1))
I understand the IIF(), but what's happening with the crazy characters? (It works the text changes from yellow to green). I tried replacing it with conditional formatting on the actual data control. That only changes color when I click on the control. The above shows the color all the time.
I am exporting data from the database to an excel sheet. Then I want to apply conditional formatting to the data so values between a certain range should be a certain colour.
But nothing happens when applying the conditional format, or when just trying to fill a cell in excel. Only when repeatedly clicking fill cell in excel does the colour change. Also all the cells are in the number format.
In my Access forms, i display data by month wise; and i want to give the conditional formatting with two condition;
If date of today is greater than target date for that month and if got value more than 0 - Red color
If date of today is lesser than target date for that month and if got value more than 0 - green color
Target date for every month in available in a separate table.
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
Format([Table].[Open Date],"yyyy-mm-dd")
The query then returns no data.
I've got two tables, Properties and Valuations. Every six months the properties are revalued. I want to have a datasheet list of all the properties with a blank field for the new valuation so that the user can just go down the list and enter the new amount without having to select every property individually. Can I do this?
View 5 Replies View RelatedI have a list box control on a form which is being populated by a query. It is showing bookings that are placed on three shifts (AM, PM, Night shifts)...the list box is working fine...
- what i'd like is that the output in the listbox is seperated by shift (maybe having a blank line when shift changes, or different text color for different shift, or different background colour for different shift? )
i'm not quite sure if this can be done...or maybe there's another way out ? any ideas?
thanks!
I have a form called CreatePL which is made up solely on List Boxes that the user must choose from. After all the choices are made, I have a button to save the information to a table called PL.
I noticed that after clicking on this button, that record stays active so when you try to create the next record, it is actually changing the previous record. As I was testing this a bit further, I also noticed that the one of the names in one of the list boxes got erased (I'm not sure how yet).
Is there a way to lock the information but still be able to make selections and save it to a table? When I choose the properties so that you can't edit, I loose the ability to make choices also. All of the list boxes are set to "limittolist".
Ok access noob here and a first post as well...I have to filter a table that has children and teachers.I made a form with a combo box of teacher names then filteredon the teacher name.in the subform I have to create a matrix of the children thatare assigned to that teacher.it would be something like thisteacher date child name start endthe teacher and child name are in tblmainthe date start and end values have to be in tbldata1so the beginning they want to choose a teacher and a dateso I shifted and put the teacher in a combo box and tied it to nothingbut the subformso combo box teacher and field datelinks to subform are teacher;datecombo box(teacher name)==> Ms. Eve text box(date)==> 01/01/06but they want a filtered list on teacher so the subform would have to beteacher date child name start endMs. Eve 01/01/06 mary lamb Ms. Eve 01/01/06 billy bullMs. Eve 01/01/06 bob jonesthen they could put in start and end for each childI don't see how I can get the child names filtered thru a query (thisI can do) BUT then to be able to edit the fields and put this datainto a new table.IS there a way to do this?a big help for any ideas or pointers as I am lost on this
View 3 Replies View RelatedHi
how do i click on my data in a list box (4 fields unbound)) on my (main form) so that it goes straight to my subform fields (same 4 fields from the list box), this will save me on data entry and i can just enter my quantity and it will calculate a total for me.
i don't want a combo box in my field on the subform.
any solution??
I am trying to create a database to create a purchase list of parts for a final assembly. There are several subassemblies that make the final unit. There are also subassemblies within subassemblies.
I want to be able to create a list of parts for purchase for the completed unit.
I have a table of part numbers that assigns part numbers to parts as ewell as subassemblies. in this table there is field that designates what type of part it is, System, assembly or base part. I am trying to query the "system" to get a quantity of all base parts contained in it. The feils in the table are part number, part name and what type of part it is.
I am having trouble with the queries or what should be in the table.
Hi-
Can anyone help me? I am trying to add new records/data entry on a Form from a list of drop-down options.
I have a database of vendors with names addresses etc. I will be selecting a specific vendor from the pull-down menu and then add data entry comments. Please help!
I have a listbox call lst with two columns, what I want to do is to populate this two column with "firstdata" and "seconddata" respectively.
What I do is:
Me.lst.RowSource = """firstdata""; ""seconddata"""
But I see nothing appear, what is the corect way to achieve in vba?
I am using Access XP.
I have a table for experiments - tblexp.
Each experiment can have one or more projects within it. So I have a drop down list of the various projects in a separate table - tblprojref with fields project no (projno) and project title (protitle).
When I am setting up a new experiment I am entering the information into a form. Along with other information that populates the underlying table, I would like to have a multiselect list box which allows me to pick the projects linked to the experiment. These would then either populate the same tblexp or another. I can only achieve this if I am only selecting one project from the list.
How can I get the several items selected into different fields and linked to the one experiment? I am new to this and although have found various threads relating to this they include a lot of code which I don't understand. Is there a step by step easy way to achieve this?
I have a database with a form. Form has a list Box. I write a code for sorting data in list Box and it is worked but sorting in one of the column in list box does not work.I think the reason of it that this column is a combo box with multiple values.
The file of database is in attachment.