Hello, i have a excel spreadsheet that has a column with a persons surname (comma) and firstname i.e Steve, Jones.
I export this spreasheet in Access, but would like to splitt the surname and first into separate columns in the table.. whats the easiest way to do this?? Thanks
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 RelatedI am trying to create custom "icons" using values retrieved from an access db. Right now, I'm pulling records that match the user's name(Fname) or "default", then assigning the returned values variables and plugging those variables in as the image, URL, and title of the "icon". The problem is that this setup will only retrieve the first record from the db that matches "Fname" or "default" and I want to create seperate "icons" for each record that matches "Fname". I can pull all matching records using the maketable(), but then I don't know how to format these as I have here to make additional "icons". My source code is included below:
Code: <% if Fname <> "" thenopencnset rs = Server.CreateObject("ADODB.RecordSet") SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'" Set rs = cn.Execute(sql, , adCmdText)ID = rs.fields(0)URL = rs.fields(2)Icon = rs.fields(3)Title = rs.fields(4)rs.closecloseCNend if%><a href="<%=URL%>" target="new"><img border="0" title="<%=Title%>" src="menupics/<%=Icon%>.gif"></a><br><font color="#FFFFFF" face="Arial" size="2"><%=Title%></font>
Any help anyone could provide on this would be greatly appreciated. Thanks in advance. -Chris Gordon
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 !
In a table, I would like a particular field to look thusly:
xx-xx-x-x-x
My question is how do I format it so that the dashes (-) are automatically inserted when the data is keyed into the input screen. The dashes would also have to be displayed on the table.
1. Can we have the cells within access table change color when their input changes as in excel? For Ex:
If the input for a cell inside Column "Status" says "Pending" then the cell color is orange.If it says "Completed" then color is green?
Is this possible on Access?
2. Also is it possible to change the color of Column headings?
I tried changing the color but found that the entire table changed colors with alternate rows.
Hi all
the problem i am facing is that the table exported by ACCESS to EXCEL is without any kind of formatting, bold text italics etc. , is there any way i can make access export the excel file with bold column headings and the cells having solid borders(basically any formating i wish to give).
Each time i export the table i have to open excel and format the spreadsheet giving headings highlighting stuff and all and it is very troublesome so i did make a macro in EXCEL for it but i still need to open the file in Excel and run the macro. is there some way to run that macro during the export process so that the user just gets the file in the format he/she wishes.
thanks for you help in advanc
I have a table in access and I need to format it so that when column "Status" = "Closed" column "Closed" = "yes". so it will automatically mark column "Closed" "yes" if "status" says "closed". Instead of having to change both columns all the time.I know how to do that in MS Excel, but is it possible in MS Access 2013?
View 3 Replies View RelatedI have been using Access for several years but always in a basic way. I have started work on an existing Members database where the Members Addresses have been pasted in from Notepad or been imported from csv or manual entry.The ones that have been manually entered are on one line. The ones that are imported from csv or pasted from Notepad are on multiple lines like you would naturally type on an envelope..In Notepad they look like this:
'Oakdale' 123 Expression Road
Name of Town
Name of County
Postcode
Sometimes the addresses have 4 lines and sometime they also have the name of a village which makes it 5 or 6 lines. When imported into Access they also view (datasheet view) in the same way with the return at the end of each line. However, I cannot enter a new address in this manner directly into Access.How can I enter the address text into a text filed and be able to format it so that it looks like the above?
I have searched many times for an answer to this but cannot find a way of explaining it that search engines understand. I first came across this 12 years ago when I worked on a massive Members database for a radio station all the addresses where formatted in the same way and had been imported through csv. When I do this the csv file marks up the text as "'Oakdale' 123 Expression Road□Name of Town□Name of County□Postcode"
I have tried exporting to csv ad then importing it into GMAIL and this works fine I have also printed off labels and envelopes and all the addresses print fine too; I just don't know how to input it in that format other than pasting all addresses through a basic text file.
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast )
SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast
FROM dbo_examInfo
I have a form which basically has - employee, work done, hours and a calculated field (w/hour) of work done/hours
I can manually set the conditional formatting however as time changes targets will go up and it would be easier to update the targets in another 'admin form' (i can manage that part)
Its just referencing the other table to say the likes of :
if work per hour is greater than tbltargets.convtargetmid then vborange, if work per hour is greater than tbltargets.convtargethigh then vbgreen else vbred.
While using append Q I lose formatting information in destination table.
How do I transfer the formatted information with out losing it when using append Q?
I am new to access so VBA code is not an option for me.
See image below.
I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.
These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.
Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.
Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.
Could anyone suggest a reason why writing an SQL command "create table" in visual basic would successfully create a table *but* change the way the data is stored in the fields?
For example, if I created a table with one field:
strSQL = "CREATE TABLE whatever ([MyField] char(20));"
DoCmd.RunSQL strSQL
And enter the data: "Test"
Why then would the whole 20 spaces be used, the first four with the letters I entered, and the rest with 16 blank spaces I never did? If I highlight them and delete them, then rexamine the record, they are still there.
Any suggestions appreciated! :D
Really simple one for all you folks out there, but I've just been struggling for 20minutes and thats it I've decided to to seek proffesional advice. Though its been said I need it in other areas of my life too (so my ex says but..), any way I digress.
I put a tab control on a form all I want to do is format the title of each individual Tab name, i.e. font size etc, type. Had a look at the properties but nothing there seems to register.
Is it possible?
Hey do any of you guys know if its possible to change the colour of the tabs or set them as transparent in a form plz??? I know you can set the background as transparent, but i dnt know if you can do that to the actual tabs.
Thanks guys
Zaf
Ok, don't know what I should title this problem as, but maybe you guys can help. The data stored on my table is formatted as Currency, however, when I Queue up the data, I also multiply it by a factor (exchange rate). Which turns the data into just a regular number. When I display this data on my form, I reformat it into Currencies again. However, when I click on the field, it shows the number in its raw format. How can I fix this so that when the user click on the field, they won't see the raw number but just regular Currency format? Much thanks in advance.
View 9 Replies View RelatedHi,
I am creating a database of private equity funds. I want to have a currency field for an input entitled fund size. However, I need to be able to display whether the size of the fund is in dollars or euros. How can customize a number/currency field to allow me to input the correct currency symbol?
Thanks
Hi
I have a database with multiple tables all linked together in various ways. I would like a master reset button that will delete every record in every table as well as resetting the auto number(s) back down to zero again. Can this be done and if so, how?
Thanks in advance.
Dave
I'm trying to format a header in a report and need some help.
The following results in "Error" if placed as the Control Source :
=IIf(IsNull([Captain]),"","Captain:" & [Captain])
Is there another way to do this?
Essentially, I want to display the word "Captain: " followed by the name if there is an entry. If there is no captain, I want to display nothing.
Thanks for the help!
In my report the data shows patients and which unit they are in so in the detail section it shows something like this depending on how many records there are:
CVICU
CVICU
CVICU
MSICU
MSICU
NICU
STICU
STICU
STICU
I'm now modifying that report to show totals and I want to list the names of the units going across the page not down the page which shows all the records. I moved the field into a "Unit Footer" and now the report shows only one entry for each unit like this:
CVICU
MSICU
NICU
STICU
Within the Unit footer section I don't want the names of the units going down but across like this:
CVICU MSICU NICU STICU
I know there must be an easy answer but I've looked and work on it for so long I can't see what it would be. Any help would be GREATLY appreciated.
Thanks so much in advance.
Hi,
Please see attachment. I have created a mini database which will hopefully help me in explaining the process.
Table1: Field1 - 8 rows/8 records
Form1: Field1 text box
So in this box I used conditional formatting to say- whenever the date is greater than 04/05/2006, turn red.
So now I scroll to record2, need to have new conditional formatting rules to apply to this record.
My understanding is that I can use the remaining 2 conditions for records 2 and 3.
But what about records 4,5,6 & 7.
For example in record 4 I need the date to be greater than 02/15/2006, and record 8 to be greater than 05/09/2006-and as you can see when you scroll down to record 8 it has already turned red because it is governed by the rules in record1!
Hopefully this makes sense!
Is there a way to get around this? Is conditional formatting the only way to achieve this?
If it's not what would I do?
Thanks in advance for your help!
Jcwishart
I am having trouble formatting a mailing label zipcode using the xxxxx-xxxx format.
I am using the =Trim([City]&","&[State]&" "&Format([ZipCode],"@@@@@-@@@@")) for Access 2002
My program is Access 1997-is there a different formula and why isn't the above working?
The zipcodes that have an extension come out right but the ones that don't have an extension come out like 6-8894
Any help would be appreciated!
Hey everyone, I need some help with formatting a string for use with the DoCmd.RunSQL() method. I can't figure out how the hell VB deals with escape characters for the purpse of variables inside strings. I have this line right now.
strSQL = "INSERT INTO OS (OS) VALUES(" + OS.Value + ")"
This is supposed to insert one row into the "OS" Table, in the "OS" field. In my form there is a textbox called "OS" and I'm trying to insert that value into the DB table.
Two things.
1) Yes, I know, I have a lot of things named "OS"
2) Yes, I know I don't need to use a string here, but I'm just presenting this is an abbreviated example, in my full program I do in fact need a string.
hi all, first time poster, with an awkward question.
i'll save you my life story but suffice to say i'm just 3 weeks into a 6mnt work placement from college and i could do with a bit of help.
the company i'm working with has to generate an xml document (preferably with access) corresponding to this schema (http://www.ros.ie/schemas/eusavings/v1/schema.xsd)
the problem so far as i can make out is that when i import this xsd into ms access - i'm presented with a rediculous number of tables and in turn these are not related, i.e. there is nothing to say that the e-mail address table is associated with the header table. (having them in one table makes more sense to me, but i'm not an expert, all i know is i have to comply with this xsd.)
when the data is exported to xml format, it's meant to look like this:
<?xml version="1.0" encoding="UTF-8"?>
<EUSavings formversion="1" periodstart="01/07/2005" periodend="31/12/2005" language="E">
<HeaderDetails>
<PayingAgent>
<TaxNumber taxtype="4">1234567T</TaxNumber>
<PayingAgentName>
<NameDetails>Joe Bloggs</NameDetails>
</PayingAgentName>
<PayingAgentAddress>
<AddressLineDetails Type="Line1">Test Road</AddressLineDetails>
<AddressLineDetails Type="Line2">Test City</AddressLineDetails>
</PayingAgentAddress>
<PayingAgentCountryCode>
<CountryCode>IE</CountryCode>
</PayingAgentCountryCode>
</PayingAgent>
<FileSequenceNumber>1</FileSequenceNumber>
<PaymentYear>2006</PaymentYear>
<ContactDetails>
<ContactName>
<NameDetails>Paul O'Neill</NameDetails>
</ContactName>
<TeleNumber>
<TeleNumberDetails>0875252252</TeleNumberDetails>
</TeleNumber>
<EmailAddress>
<EmailAddressDetails>test@test.com</EmailAddressDetails>
</EmailAddress>
</ContactDetails>
</HeaderDetails>
<AccountDetails>
<DocumentType>1</DocumentType>
<AccountHolderDetails>
<FormType>A</FormType>
<KeyName>
<NameDetails>Jones</NameDetails>
</KeyName>
<OtherNames>
<NameDetails>Tom Paul</NameDetails>
</OtherNames>
<Address>
<AddressLineDetails>New Road</AddressLineDetails>
<AddressLineDetails>New City</AddressLineDetails>
</Address>
<AddressCountryCode>
<CountryCode>GB</CountryCode>
</AddressCountryCode>
<BeneficialOwnerResidenceCountryCode>
<CountryCode>FR</CountryCode>
</BeneficialOwnerResidenceCountryCode>
<BeneficialOwnerBirthDetails>
<DateOfBirth>01/01/1945</DateOfBirth>
<BirthCity>Paris</BirthCity>
<BirthCountryCode>
<CountryCode>FR</CountryCode>
</BirthCountryCode>
</BeneficialOwnerBirthDetails>
<PaymentType>0001</PaymentType>
<CurrencyCode>EUR</CurrencyCode>
<AmountPaid>8889</AmountPaid>
<AccountIdentifier>152525525</AccountIdentifier>
</AccountHolderDetails>
<ReferenceNumber>455200211</ReferenceNumber>
</AccountDetails>
</EUSavings>
everyone in this company is completely new to xml and seeing as i have a little experience with it from college i've got quite a bit of pressure on me to try and make this work.
so, my question is: how would i go about exporting data from access into a xml document which conforms to the xtd, and looks like the xml code above?
is access capable of doing this?
they want this solved programatically, but nobody here knows how to do it.
can anyone offer guidelines on how to tackle this? any help would be really appreciated, i'm in over my head
Hi Guys,
I've a question and I hope you can help me out.......
I'm trying to use Conditional Formatting Options on a Text box called "Date". I'd like that this box become Red if its value is between value1 from Text box "Start Date" and value2 from Text box "End Date" or Green otherwise (in this way the color change dynamically every time I change the values of Text Boxes "Start Date " and "End Date"). How can I do that? What I need to type in the Conditional Formatting Windows?
Thanks a lot for you help!!!!
Ciao Ciao