Convert Number Field to Date using query
I have search the forum and found several people asking this question,
but my situation is a little different. I have a database that I need
to convert a number field to a date, but the number fields have the
date as "08/07/2007". I have not been able to find a way to convert
this number field to a date.
Any suggestion/comments would be greatly appreciated!
You sure it's a numeric field? Numeric fields wouldn't have slashes in them.
If it's text, you can use the CDate function.
Doug Steele, Microsoft Access MVP
(no e-mails, please...XML formatting question.
For example, I load a XML file with DOM. XML file like this:
<root a="a" b="b"/>
Then I want to save and format this file like this:
But I find that I can not insert CR and Tab char between attributes. Help
Which XML parser are you using? Most of the one's I've seen have a separate
XML formatter (SAX parser?) that allows you to set up the format for how
files are written. In any event, except for visual it doesn't matter how
the file is written.
"fi...pivot tables formatting
I have a sales pivot with the months in columns and
products by category in the rows. I simply want to
have "normal" lines, which will make for easy reading like
any other report at print time. Also I would like the
product headers left aligned down in their column
Whatever formatting I apply in whatever way I can select
those items, my formatting is undone on a refresh, though
the auto-format option is off in the table setup. It does
keep seetings like the font and bold, etc, but lines and
alignment instructions are rudely overwritten.
Anybody with an answer to that one? I w...Email to queue 12-22-05
We had 2 queues in 1.2 which worked great for receiving external support
requests. For some reason, these no longer work since the upgrade to 3.0.
Is there a different procedure for queues in 3.0? I can't seem to find the
answer in the documentation anywhere, nor in a search through the
We are having the same problem. Has anyone found a solution to this
The setup process for Email to queue in v3.0 has changed from v1.2.
The current v3.0 Implementation Guide for setting this up needs to be updated.
So here are our notes for now until it get...Custom formatting for a cell
I need some help…
In Excel 2003, I am entering in a column of cells; different numbers that I
want formatted a specific way.
I enter this number: 31002770004304330
I want to format them like this: 3100-27-7000-430-4330
After I entered the first series of numbers without the dashes, I went to
format cells, selected custom, and enter in 0000-00-0000-000-0000 and applied
What happen is Excel changes by last 4 numbers in the string from 4330 to
4300 when I enter the data. Why?
I am using the numeric key pad enter a couple hundred of these and don’t
want to ...file format not valid #5
I have xl2000 - please - my boss opened one of my xl files in word - it has
tons of data - now i can't open it in xl anymore - HELP
Your boss most likely also saved it when in Word.
It would have defaulted to being saved in Word format.
Unless you have a recent backup the only solution would be to re-create the
layout in a new Workbook then try to salvage some of the data from the
saved-in-Word file by opening in Word and grabbing what you can to copy and
paste, if possible.
Gord Dibben Excel MVP
On Fri, 4 Feb 2005 10:25:03 -0800, "jeniflower"
<email@example.com...Need help on formatting row height
I have 500 rows of data. All row heights are currently at 15.
For a given column width, I need to wrap the text for about 100 rows to fit
within the column width and make the text visible. Because of this, I need to
increase the row height for these 100 rows to accommodate the type size.
If I use the Auto Fit Row Height feature, it will increase the height of 100
rows to accommodate the type size. It will also, however, decrease the row
height of the remaining 400 rows to fit the type size. I do not want to
decrease the row height of these 400 rows but keep it at 15.
Is ...receiving excel sheet in rich text format
Using outlook 97, when I receive a page sent from a user as an excel
workbook, it is received as an html file. I then need to use ie explorer to
open and read it. Others using outlook express set up for rich text, see
the sheet directly. Can I fix this inconvenience?
...time format from hh:mm:ss to h:mm
I have been trying forever and no luck at all!!!
I have changed the the whole column under format cell/Number/Category/time
and selected 13:30 (english USA) When I then try to do a mail merge in word
2003 the merged field still comes up hh:mm:ss which I don't want???
Any help greatly appreaciated.
Unfortunately, mail merge doesn't look at the cell's format, only its number
Either create a text field, using a formula like:
or specify the format in your Word document.
"Diana" <Diana@discussio...Copying cell formatting and data validation in protected worksheet
I have a worksheet that has several auto-calculated columns that I would like to protect to prevent users from entering values. On the other hand, users should be able to copy the last row to create additional rows for entries. When I protect the sheet and copy the row, all the values calculated from formulas paste as fixed values and the data validation rules (selection dropdowns) disappear. I've tried not marking the cells with the formulas as "locked" in the cell format dialog, but it seems to make no difference. Using events to prevent users from selecting the cells that they...Error Message 05-14-10
The connection to the server has failed. Subject 'Test', Account: 'Windows
Live Email', Server: 'smtp.live.com', Protocol: SMTP, Port: 25, Secure(SSL):
Yes, Socket Error: 10060, Error Number: 0x800CCC0E
Here is the error message that popped up when I tried to send a message (it
was just a test) and I'm not sure what I'm doing wrong. Can anyone help
Change port 25 to port 587. You can do so here:
Go to Tools, Accounts, select that account, Properties, Advanced.
Gary VanderMolen, Microsoft MVP (Mail)
Microsoft MVP progra...Import text file in UTF-8 format to Excel 2000
When I open a text file(in UTF-8 format)under excel 2000,
the chinese character cannot be displayed correctly.
But this text file can be opened directly in word 2000.
Can anyone help me? Thanks!
...Browser choice 04-12-10
I have posted this question before, but cannot now find my post on the site.
I stupidly downloaded the 'browser choice' update, and now can't get rid of
it. I have three browsers installed already, & don't want another one.
Every time I reboot the computer this annoying message pops up (even more
annoying because although it is from Microsoft it says the certificate is not
valid). I want to *permanently* get rid of this message, so I never see it
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&...Conditional Formatting of Percentages
I have a spreadsheet with 12 rows (one for each month), and 3 columns:
Incidents Closed, Closed on Time, and Percentage Closed on Time. The data in
the % Closed on Time is formula (=IF(E15=0,"",E16/E15).
I'm trying to use Conditional Formatting Icon Sets, Traffic Signals as
follows: >90% = Green, 80-89% =Yellow, and Below 80% = Red. I can't get the
Icon Sets to work correctly, getting some percentages with the incorrect
color, as a new month'e data is acced to the sheet. I have the Rules set up
as indicated above, and using the percent Type in the Rul...Conditional Formatting A Cell Based on Another Cell's Value
I have a report where I want to use conditional formatting on the two
far right-hand columns.
Applying conditional formatting to right-hand column 1 (RHC1) which
consists of percentages is easy. If the value in any cell in this
column is less than .85 the cell is shaded green, if greater than or
equal to .85 and less than or equal to 1 the cell is cell is shaded
yellow, if it's greater than 1 it is shaded red.
Right-hand column 2 (RHC2) consists of whole numbers. I want the
shading of the cells in RHC2 to match the shading of the cells next to
them in RHC1. I have tried to create formu...Conditional Formatting #102
How do i conditional format a large amount of cells? I need each cell t
change colour when a certain value is inputted into that cell
smith.james0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1623
View this thread: http://www.excelforum.com/showthread.php?threadid=38234
If the format will be the same for all the cells, i.e., all red when >100,
then simply select all the cells in question, and the cell with the focus
(colored white in selection) should be the cell of re...Versions of the *.xls format
at which excelversion is the *.xls format the same?
I personally estimate that 97, 2000, xp and 2003 use the same format.
Is this correct or not ?
Thanks in advance!!!
"J. B." <firstname.lastname@example.org> wrote in message
> at which excelversion is the *.xls format the same?
> I personally estimate that 97, 2000, xp and 2003 use the same format.
> Is this correct or not ?
Excel 97 and later all use the same file format (called BIFF8).
Microsoft MVP - Excel
Pe...Combining text and date in one column
I have a very old spreadsheet originally created in Macintosh Works that has
entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. If
I format the column to text, then my dates turn into numbers. If I format it
to dates, then my numbers turn into incorrect dates. I can format and fix
the original information, but how can I format the column for future entries?
Thank you for the info. I understand how Excel can show the data in a date
field if it's not a date and how it shows data in a text field if it is a
date. The problem is that the original information i...date calculations #5
I am trying to calculate the average and median of a value in column I when
the date in column H has a month value of January. This is my formula:
Any idea why I get the wrong result?
Blank cells would return TRUE for MONTH = 1 as well, you can use
"kraway" <email@example.com> wrote in message
> I am trying to calculate the average and median of a value...pivot table
Hi - have just switched to XP
In a pivot in old excel I use to to click in a subtotal
field eg "Europe Total" and excel would automatically
highlight all subtotals at that level. It just meant I
could colour the row and it would apply to all of the
subtotals - how can I do that it XP ?
To enable selection --
From the Pivot toolbar, choose PivotTable>Select
Click on Enable Selection
To format the subtotals --
Move the pointer to the left of a subtotal heading in the
When the black arrow appears (like the one that appears
when the pointer is over a r...Does Access store data in fixed lenght or variable length format
This is just for my own education and curiosity. Does anyone know if Access
stores it data records in a fixed length or variable length records / rows?
I realize that the answer is some what immaterial as it works the way it
does, but as I said I would like to know for my own knowledge.
On Sat, 9 Jan 2010 20:27:01 -0800, Dennis <Dennis@discussions.microsoft.com>
>This is just for my own education and curiosity. Does anyone know if Access
>stores it data records in a fixed length or variable length records / r...Date Question 05-31-07
I have created a database to store photographs and one of the entries is the
date the photo was taken. Unfortunately, many times the date is not
complete. In some cases the day is missing, in some cases the day and month
are missing and in rare cases, the entire date is missing.
I have initially set up the date as three text fields to accommodate the
possibility of the missing values. Are there any other solutions to the
problem? I'd like to use a single date field if possible.
Unless you will always have complete dates, or unless you want to use a
"default" da...Need Help Formatting a 6-digit number
My excel worksheet prints a barcode sheet from data that is pulled from
a database. The format for my "Client ID" number must always be 6
digits, but the format pulled from the database is sometimes
inconsistent, and I need to find a way to correct this in my Excel
If the number pulled from the database is only 5 digits, I need for
Excel to add a leading 0. Likewise, if it is 7 digits, I need for
Excel to drop the first digit and retain only the right-most 6 digits.
Database Desired Excel Format:
55555 -> 055555
0555555 -> 555555...Date Difference 05-06-10
I have a text field formatted to date, the date is chosen using the date
picker, I would like to put another text box on the form to display the words
"overdue" if the the field displaying the date is greater than 14 days else
display "Waiting". Thanks in advance.
Try this --
IIF(DateDiff("d", Date(),CVDate([YourTextDate])) >14, "Overdue",
Build a little, test a little.
> Hi All,
> I have a text field formatted to date, the date is chosen using...Smart Tags troubleshooting
I am looking to setup and get some understanding of smart tags in a
demo environment on my laptop; however, I am having some confusion.
I have the PDF documentation available, though I can't seem to get the
Basically, I have GP 10 / SQL 2005 installed with Fabrikam, Ltd
running without a hitch on my laptop. I turned on smart tags in
Outlook, Excel, Word and then began the install of the office smart
tag server. I configured the smart tag server to allow all domain
users (as I am logged on at my work network) to have full access
(enabled) to all resources for Fabrikam. I then...