Hi Everyone, I'm trying to run an append query with a criteria on a date/time field based on a start and end date-time that I compose in VBA on a form. The query has for criteria on the date/time field: >=[Forms]![oneoclock]![Startdt] And <=[Forms]![oneoclock]![Enddt] In the form VBA, as an example: Private Sub Form_Open(Cancel As Integer) Dim Startdt As String Dim Enddt As String Dim dummy As String Dim stSocName As String dummy = "31/10/2007" Startdt = dummy & " 09:00:00" Enddt = dummy & " 09:15:59" stDocName = "pull_test" D...

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel My first post here - please be patient. I'm helping a friend who has just converted from her PC to a shiny new iMac and I've struck a problem that I don't know how to resolve. On her PC she saved many (hundreds) of photos in .doc format (don't ask)! She thought that was how to do it. She's adamant that she needs to keep all these images. <br><br>I've never faced the problem before and don't seem to be able to find any way of converting the .doc formatted pictures to e....

Hello, I'm exporting some information from a system called CMS. The problem I'm having is that it exports in seconds and decimals. For instance 3:19 (3 minutes 19 seconds) is exporting as 226.605636597. I'm trying to convert this back to 3:19. Any suggestions? Thanks in advance! Chuck -- bagoxc ------------------------------------------------------------------------ bagoxc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30084 View this thread: http://www.excelforum.com/showthread.php?threadid=497654 I don't see how 3 minutes 19 seconds ...

I found this neat coding in the custom number format box: _ * #,##0.00_) $_ ;_ * (#,##0.00) $_ ;_ * "-"??_) $_ ;_ @_ It produces this when there is no dollar amount: - $ I like this format, but of course, need to write the currency in the more standard English format since this particular sheet is in English and not French so that I should get this instead: $ - Dunceville me ... I tried everything I could think of but I can't get it to come out right. Every attempt gave me an error code with the kind suggestion to use the "built-in number formats" :oD :oP ...

I want to have a date without using date function in another function, i.e., MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? -- Thanks, Don On Sat, 16 Jan 2010 19:40:01 -0800, DRA <DRA@discussions.microsoft.com> wrote: >I want to have a date without using date function in another function, i.e., >MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 > >I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? The DATE function ...

Using this code Sub Insertformula () For Check = 4 To 40000 Step 2 If Cells(Check, "b") <> "" Then Cells(Check, "b") = "=IF(OR(I5="",J5=""),"",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1)" Next Check End Sub My objective is to put this formula =IF(OR(I5="",J5=""),"",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1) Into every even cell in Column B with data below it. However if you tried the macro you would see it does not work. If I could get any help thank you. Please tell ...

Hi, Would it be possible to assign a shortcut key to a VBA procedure like can be done in Word and Excel? I use Outlook 2003 (MultiLangual version). -- Mvg, Frans Follow the instructions at = http://www.slipstick.com/outlook/toolbar.htm#macro to add a macro to the = toolbar, then give it an Alt+ accelerator key by putting an ampersand & = in front of the accelerator character.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators,...

Cell A1 of my worksheet contains the following formula: ='[WTA Work History Overall.xlsx]Retirement Summary'!Retirement_Payment Cell A2 contains a date. Is there any way that the formula in cell A1 can be constructed so that once the date exceeds the date in cell A2 that the formula stops updating the value in cell A1 and just shows the value that was in place prior to exceeding the date in A2? A formula can only return a value so with something like the following: =IF(DATEVALUE("29/11/2009")>A1,"A1 expired",A1 If the date in A1 was before to...

Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

I have a class that keeps a balance of "money" they earn in school. Each week we add the total then at the end of the month they subtract thing s they bought. So I would need a formula that adds the total for four rows and then subtracts in the fifth row to give me a final total. I am new to excel and am hoping I will be able to do it without too much trouble. I am usually a pretty quick learner. Are you wanting only to make this calculation occur on demand at the end of the month? If your happy for it to just calculate as you go along then just use "=A1+...

Can anyone please help with this formula. If Sheet1 Column A = nothing(blank) And Column B = \\\\\\CC\\\\\\ Then Sheet 2 B7 = SumTotal. As a regular formula would be nice or VB code Hi Richard you want nothing at all in the whole of column A in sheet 1? do you want every cell in column B of sheet 1 to have \\\\\\CC\\\\\\ or should this (can this) only appear in one cell? and what range are you summing on sheet 2 in cell B7? Cheers JulieD "Richard" <anonymous@discussions.microsoft.com> wrote in message news:82ce01c477e1$77f0e740$a301280a@phx.gbl... > Can anyone please ...

I probably didn't title this too well. I have a spreadsheet with 500 rows of data, sorted by the date in column A. I want to extract certain data from rows that all start with the same specified date. Previously I used an IF formula but that necessitates having the formula in 500 rows which won't work for my purposes. I'm going round incircles trying to think how best to achieve this. If you apply a filter to the date column for the selected date you can then just highlight the visible rows and copy/paste or cut/paste them elsewhere (eg another sheet) to extract ...

I am in a number draw based on the lottery. Each member has 10 numbers and there are just over 400 members. I would like to try and set up a spreadsheet wherby I can enter a number in cell A1 and all cells which contain that number change to an X. Can it be done ?. Thanking you in anticipation. This is not exactly what you asked for, but: Say you have a long list of numbers in column C and your X-out value in cell A1. Then in cell B1 put: =IF(C1=$A$1,"X",C1) and copy down This will repeat the list in column C and "X-out" the value appearing in cell A1 -- Gary'...

I import data from a database as a csv file and then save it as an Excel 2003 workbook. The first column in the spreadsheet contains a long list of four character entries. I remove the first character of each entry by inserting a column next to Column A and using the following formula: =right(A1, LEN(A1)-1) Usually, I can copy or drag the formula to the following cells in the inserted column and the formula results are displayed. However, today, when I copy the formula to the other cells, the results for Column B1 are copied to all the cells in Column B. When I look at the form...

Dear : i have the following code : CStdioFile file("C:\\report.txt",CFile::modeCreate | CFile::modeWrite); file.WriteString("My infor"); SetFilePointer(file.m_hFile,NULL,NULL,FILE_END);// the Compiler issued error said : E:\Private\Files\Dlg.cpp(321) : error C2664: 'SetFilePointer' : cannot convert parameter 1 from 'unsigned int' to 'void *' file.close(); Why? this error generated in Visual Stdion 6 , the same code implemented in Visual Stdio 7 (Visual C++.NET ) run without errors .!!!!! what cause the problem? Regards Raed Sawalha wrote: >...

Hello everyone...again Having trouble with the last formula I need for this worksheet. This one is a bit tricky. If the user enters in a whole number in cellA, say 1 (which equals 5 minutes), cellB has to display based on cellA, the value in hours(in decimal form). so if a user enters in 6 in cellA, cellB would display .5 hours. This is possible, right? Any help or suggestions would be apprecaited Mike Hi try the following in B1 =TIME(0,A1*5,0) and format the cell B1 as 'Time' or enter the formula =(A1*5)/(24*60) and also format as 'Time' -- Regards Frank Kabel Frankfu...

ello and thanks in advance to anyone who can point me in the right direction. I am running Excel 2003 on a Windows 2000 machine. I have 2 sheets in one workbook. sheet 1 is data input sheet 2 is summary I want to put this into summary ......A...........................B..........................C 1...='data input'B1........='data input'B2......='data input'B3 2....='data input'E1.......='data input'E2.......='data input'E3 I have about 100 rows each of about 10 columns that I wish to do this with. I know I can do it the long way by hand b...

I have one cell containing a street, city, state and zip that I want converted to 4 individual cells without manually entering each cell. Thanks, Becky Try Data/Text to Columns. Use "," as a delimiter, then a space character. If you have states with two-word names spelled out, do a search and replace first to replace them with one word names, then reverse it after you do the Text to Columns. In article <66979866-7EDB-4A72-994B-FC32C38ED62D@microsoft.com>, bbrowning711 <bbrowning711@discussions.microsoft.com> wrote: > I have one cell containing a street, cit...

Dear All, Please i need the formula for colum D Status, it can display Pickcal or In or Out or Picked. Colum A Colum B Colum C Colum D Confirm Date scanin scanout Status Pickcal 17-03-10 11:38 In 17-03-10 11:38 17-03-10 11:49 Out 17-03-10 11:25 17-03-10 7:29 17-03-10 8:24 Picked 17-03-10 11:20 Picked Thans so much. Tr...

In Sheet1, I have a list of 20 products, each with a unique referenc number in column A. In Sheet2, I have a list of every potentia reference number, also in column A. I need a formula somewhere i Sheet1 that matches each reference number to its counterpart in Sheet and copies the data from that row (Sheet1) to the correct row i Sheet2. The biggest problem I'm having with this, is to create a formula in on cell (or sheet) and have the result appear in another cell/sheet. Would appreciate any help... :o ----------------------------------------------- ~~ Message posted from http://www.E...

Hi, I am trying to create a fairly simple formula where when the use enters a 3 digit country code such as CAN, excel automatically enters the part of the world that code is from. So basically I have 7 categories (South America, North America, Asia Pacific, South Pacific, Europe, Middle East and Africa with each having the relevent 3 digit codes assigned to them for example North America = USA, YYZ, RDU - the most has 25 codes) so when USA is entered, the following cell would enter South America. So two things I guess I need to do :- 1) Assign country codes to country regions 2) Use a for...

Hi, I have a 2 lists of telephone numbers - one that is in the form 4165551234, and the other that has the format 416 555 1234. I need to convert these to the same format, and cannot (unfortunately) do it through Excel as I have more rows of data in my database than are allowed in Excel. I'm using Access 97 if that makes a difference. Any suggestions would be appreciated. Thanks, Chris Hi Chris, You can do this with an update query. Set a criterion on the phone number field of Like "##########" (that's ten hash signs) and update it to Format([XXX], "000 000 ...

Does any one know if there is a limited number of arguments one can nest in an IF formula? I have tried entering 14 conditions and it will not accept after the 8th one. It tells me I have no false value. Or is there something wrong with my formula? =IF(H45=43.9, "Needles",IF(H45=21.95,"Needles",IF(H45=62.5,"HP",IF(H45=65,"HP",IF(H45=67.5,"HP",IF(H45=70,"HP",IF(H45=130,"HP",IF(H45=135,"HP",if(h45=140,"HP",if(h34=995,"CD",if(h34=1295,"CD",if(h34=1695,"CD",if(H34=1990,"CD...

No, I have a drop down menue that quaries the internet for stock dat based upon the stock selected in that drow down menue. Since the dro down menue has a control cell linked to it I would like to utilize tha number which is in D5 on the sheet as a of determining where to pu data, i.e., the closing price of the stock selected. What I am looking for is a way to reference a cell address with th value of another cell. Any ideas? Thanks, Michae -- MJSlatter ----------------------------------------------------------------------- MJSlattery's Profile: http://www.excelforum.com/member.php?a...

Hi, I know that the "Create Email" tool in order to collect data through email but I want to do it automatically. My database form includes VBA code in order to send automate emails. I'd like to make this email "special" in order to receive the replay into access and use the answer email. I'd be grateful if someone could help me with the VBA code. Thanks! This capability is included in Access 2007, if you are using the .accdb file format, although you still need to initiate the process (ie. it is not fully automatic). What version of Access are you using? ...