My Db had an email_address field which was a hyperlink. For various reasons I had to change it to a text field. However, now every email shows as:
some_email@domain.com#http://some_email@domain.com#
Is there a simple way of cleaning this up so I am only left with the email address? In other words is it possible to create an update query to remove everything between # signs (including the # signs)?
----
I found some vba code on the forum which extracts strings but was wondering if there is an easier way just using an update query since I'm still not too efficient with vba
I have a report that contains hyperlinks to various folders on our share drive at work. I distribute this report once a week using the EmailDatabaseObject built-in function in the macro builder. I specify the output of the report to be PDF format. Everything works great, except when it is converted, the hyperlinks do not work.
You can click on them within the PDF, and a message pops up asking me to "allow" it to connect to the address. (Only the address now contains "%20" scattered throughout the address. Is the link corrupted?).
Using Access 2010, I have a table the contains a field, MembereMail, that is defined as a hyperlink. I imported data from an Excel spreadsheet which looked to be consistent in format and did not realise that anything was wrong until I wrote some VB code to generate personalised emails with personalised attachments. I then found that some of the hyperlinks worked and some didn't. Those that were essentially text worked, those that were genuine hyperlinks tried and failed to go to addresses prefixed "Mailto:".
I have finally managed to convert all email hyperlinks to text content (although still defined as hyperlinks) and my code works. However when I add a new record to the table through a form, the field still being defined as Hyperlink, it goes in as pure text. I therefore have the need to use the address in both text form and as hyperlink so that the user can click on the field and start an Outlook email as well as my code running correctly for mass mailings.
So a number of questions:
-Why does Access not store the email address as a clickable Hyperlink? -How can I satisfy my two requirements from one field? -Can Access 2010 check the input text to ensure that it is a correctly formatted email address?
I have a comment box that was just for text. I have been asked to adapt this to allow a web URL to be typed in which would then become a clickable hyperlink (like typing in a Word document). Is this possible?
I was wondering if anyone knows of a routine/standalone application that you can run to ascertain determine the usage (what forms/modules use them), of queries in an Access database - also, it would tell you whether certain queries listed, are not used at all and therefore can be removed if desired.....
I am keen to see if I can reduce the no. queries in my database as part of general housekeeping.
Hi, I have the following problem which I hope someone can help me with:
I am importing a CSV file, one of the field in which contains times in the following format: 1200 (ie this is 12:00, th text file does not contain the : separator)
I want these to be recognised as time values by access, which at the moment only treats them as text. Is there any way i can run a query on this table to convert the numbers into times? I can do it in excel using concatenate to add in the :, but access does not have this function.
I have a table with a field called 'dereg_date' which is formatted as text, an example is as follows:
2006-07-07 00:00:00.000
I want to be able to convert this field to a date. I have tried using a query with Cdate([dereg_date]), however it doesn't convert it and just returns #Error for all the records.
Has anyone got any ideas on this? I need this fixed fairly urgently as my superiors are getting quite impatient!
Is it possible to convert the text field that comes in this form:
:80421
to the following datetime format: MM/DD/YY 12:00:00AM
Please note that the text field contains a colon at the beginning. The number 8 represents a year (2008), the 04 represents a month (April) and the last two characters are days in the month (21). The converted field should look like:
I have a table with person's name, clubname, cluburl. In a report I can show the clubname,but when I export to HTML, can this clubname become a hyperlink to the clubURL ? (While showing the clubname..) Anybody ideas ?
I have creadted a form with a combo box that has 3 dates in it: February 1, 2005, September 1, 2004 and June 4, 2004.
Is there a way to display in another field on the form(A text field) when one of these dates is selected from the combo box, a text field returns "DATED ON THIS FIRST DAY OF FEBRUARY TWO THOUSAND FIVE"?
I reckon this is a vb issue and any help is appreciated.
I have a date field in a form which is not compulsory.
I then pull this date field into a query to create a report.
If the date is not entered then is it possible for "Date not entered" to be written into the report automatically. I have tried playing around with nulls but have had no luck.
I have a file called CAPTURE.BUF. I am trying to convert it to text. Here is what I ahve done, but I get the message box ever time.
Public Function copyFile() Dim SourceFile As String Dim DestinationFile As String SourceFile = "C:PCPICSWCAPTURE.BUF" DestinationFile = "C:INVESTMENT_REPORTSCAPTURE.TXT" On Error Resume Next FileCopy SourceFile, DestinationFile If Error > 0 Then MsgBox "Could not copy mls file." End If On Error GoTo 0
I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?
Using 2010 (accdb format) on windows 7.I'm trying to convert a memo field's format from Plain Text to Rich text. The problem is that every time I try to select the Rich Text format option the field immediately changes back to Plain Text.
Just as a test a created a new memo field in the same table and set this to Rich Text (worked fine) then copied the records for the Plain text memo field to the new memo field. Unfortunately this removed all line breaks so instead of my text looking like
I have a string that includes mm/dd/yyyy + person's name. All of this is concatenated and is recognized as a text value. Then I want to convert all of these characters to a number. Since each character has an ASCII code, I would like to find ASCII equivalent.
In Excel, this would involve using something like the CODE ( ) function, but that only gives the numerical equivalent for the first character in the string, but I want all characters at once.
When I try the VAL ( ) function, it returns the first character which is the number of the month.
I have a text file delimited and uploaded the information to a new Access Table. One field "MarketValue" has a number followed with a percentage (i.e. 100%, 54%, etc..), however it converts as a text column.
How can I convert the column to a TRUE percentage field representing the value originally imported.
In a table called Appointments and the field called Plastics we enter the initials of surgeons. However in a report i want the initials converted to full text - in the example below the initials are HJC
The expression below doesn't work - it just returns everything in the Plastics column.
Hi, Can anybody help me ? I've a case where ms access displays oracle's numeric field as text where msaccess is linked to oracle.The possible reason could be when we don't specify precision in declaring the filed in oracle table, default precision is 38 which ms access cannot hold. So, it(driver) converts this to text field.But this is with MSORCL32.dll(microsoft driver for oracle).Is there any other possible reason that will result this behavior?
The following are the configuration details: 1.Oracle 9i on Unix server. 2.MSAccess on Windows XP. MSAccess holds linked data of Oracle server.
Clng([text field]) to convert a text field into a number so that I can link in a query. Because it is an informix database, I cannot change the underlying table format.
When I run the query, the data is showing "Error".
I tried changing the other table to text and linking but it doesn't seem to link properly because the query doesn't work.
I have an option box 1=Yes, 2=Np 3=Unknown. the result is stored in a field in my table. When i run a query I pull up the numbers 1, 2 or 3 but to put in a report I actually want the written answers. How do i convert them back?
I am building a database using data imported from Excel workbooks. The dates in the workbooks are formatted as text in the YYYYMMDD format. Is there a way to convert this into date format during the import or after? I am pulling in a lot of different workbooks and trying to avoid having to reformat each individual workbook prior to importing them.
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2)))) DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2)))) DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4)))) Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
I have a text field I need to convert to numbers. There are both empty and non empty fields. I then need to show the numbers like this "1.234" and a "*" in the empty fields.
I have tried to convert the fields with Cint, but I get an #Error in the empty fields.
I have a problem converting text to a real date value so I can do some calculations. I have a query that brings in data from an external data source. It appears the data is stored in the external table in text format and looks like this:
20050902 15:40:41
I have tried CDate to convert the text to a date/time format, but no luck. Any ideas?
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
