Queries returning different formats

Hello, 
I have cut and paste the same expression from a query to another, however 
when i do this it changes the format of the information returned. 

In the inital query it returns (Hours:Minutes) perfectly, expression is 
"LOS (Hours:Mins)": 
(EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime). 
When I paste it, it returns a decimal value (which I presume is the way 
access formats time), and adds '[]' to the expression i.e. 
"LOS (Hours:Mins)": 
([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([EDMain]![EDPresentationDate]+[EDMain]![EDPresentationTime]). 
I have tried removing the '[]' however they automatically return. Any ideas 
how I get the expression to work in the same way that it does in the first 
query?

Thanks, 
0
Utf
3/14/2010 11:03:01 PM
access 16762 articles. 3 followers. Follow

4 Replies
476 Views

Similar Articles

[PageSpeed] 57

I cant work out how to remove my post - but I have sorted the issue - sorry 
for wasting everyone time.

"youkr" wrote:

> Hello, 
> I have cut and paste the same expression from a query to another, however 
> when i do this it changes the format of the information returned. 
> 
> In the inital query it returns (Hours:Minutes) perfectly, expression is 
> "LOS (Hours:Mins)": 
> (EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime). 
> When I paste it, it returns a decimal value (which I presume is the way 
> access formats time), and adds '[]' to the expression i.e. 
> "LOS (Hours:Mins)": 
> ([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([EDMain]![EDPresentationDate]+[EDMain]![EDPresentationTime]). 
> I have tried removing the '[]' however they automatically return. Any ideas 
> how I get the expression to work in the same way that it does in the first 
> query?
> 
> Thanks, 
0
Utf
3/14/2010 11:11:02 PM
youkr,

You can't remove your post...  posting that you found a solution let's us 
know to move along...  posting the solution is even better as it is bound to 
help someone else!

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"youkr" <youkr@discussions.miscrosft.com> wrote in message 
news:CC5C346D-A30F-4D77-A7C4-7AA930A9C763@microsoft.com...
I cant work out how to remove my post - but I have sorted the issue - sorry
for wasting everyone time.

"youkr" wrote:

> Hello,
> I have cut and paste the same expression from a query to another, however
> when i do this it changes the format of the information returned.
>
> In the inital query it returns (Hours:Minutes) perfectly, expression is
> "LOS (Hours:Mins)":
> (EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime).
> When I paste it, it returns a decimal value (which I presume is the way
> access formats time), and adds '[]' to the expression i.e.
> "LOS (Hours:Mins)":
> ([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([EDMain]![EDPresentationDate]+[EDMain]![EDPresentationTime]).
> I have tried removing the '[]' however they automatically return. Any 
> ideas
> how I get the expression to work in the same way that it does in the first
> query?
>
> Thanks, 

0
Gina
3/15/2010 12:15:52 AM
Of course...
I changed  the format of the return to be 'short date' - this made it the 
same as my other query. 

is there a way to format it h:n:s? When I tried to format it this way it 
just returned 'H:n:s' in every record!

"Gina Whipp" wrote:

> youkr,
> 
> You can't remove your post...  posting that you found a solution let's us 
> know to move along...  posting the solution is even better as it is bound to 
> help someone else!
> 
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
> 
> "I feel I have been denied critical, need to know, information!" - Tremors 
> II
> 
> http://www.regina-whipp.com/index_files/TipList.htm
> 
> "youkr" <youkr@discussions.miscrosft.com> wrote in message 
> news:CC5C346D-A30F-4D77-A7C4-7AA930A9C763@microsoft.com...
> I cant work out how to remove my post - but I have sorted the issue - sorry
> for wasting everyone time.
> 
> "youkr" wrote:
> 
> > Hello,
> > I have cut and paste the same expression from a query to another, however
> > when i do this it changes the format of the information returned.
> >
> > In the inital query it returns (Hours:Minutes) perfectly, expression is
> > "LOS (Hours:Mins)":
> > (EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime).
> > When I paste it, it returns a decimal value (which I presume is the way
> > access formats time), and adds '[]' to the expression i.e.
> > "LOS (Hours:Mins)":
> > ([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([EDMain]![EDPresentationDate]+[EDMain]![EDPresentationTime]).
> > I have tried removing the '[]' however they automatically return. Any 
> > ideas
> > how I get the expression to work in the same way that it does in the first
> > query?
> >
> > Thanks, 
> 
> .
> 
0
Utf
3/16/2010 10:59:01 PM
IYoukr,

I think what you are looking for is HH:MM:SS

How exactly are you trying to use this?

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"youkr" <youkr@discussions.miscrosft.com> wrote in message 
news:EF0A6DE6-3271-47F3-8854-D1F62464ADC5@microsoft.com...
Of course...
I changed  the format of the return to be 'short date' - this made it the
same as my other query.

is there a way to format it h:n:s? When I tried to format it this way it
just returned 'H:n:s' in every record!

"Gina Whipp" wrote:

> youkr,
>
> You can't remove your post...  posting that you found a solution let's us
> know to move along...  posting the solution is even better as it is bound 
> to
> help someone else!
>
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> http://www.regina-whipp.com/index_files/TipList.htm
>
> "youkr" <youkr@discussions.miscrosft.com> wrote in message
> news:CC5C346D-A30F-4D77-A7C4-7AA930A9C763@microsoft.com...
> I cant work out how to remove my post - but I have sorted the issue - 
> sorry
> for wasting everyone time.
>
> "youkr" wrote:
>
> > Hello,
> > I have cut and paste the same expression from a query to another, 
> > however
> > when i do this it changes the format of the information returned.
> >
> > In the inital query it returns (Hours:Minutes) perfectly, expression is
> > "LOS (Hours:Mins)":
> > (EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime).
> > When I paste it, it returns a decimal value (which I presume is the way
> > access formats time), and adds '[]' to the expression i.e.
> > "LOS (Hours:Mins)":
> > ([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([EDMain]![EDPresentationDate]+[EDMain]![EDPresentationTime]).
> > I have tried removing the '[]' however they automatically return. Any
> > ideas
> > how I get the expression to work in the same way that it does in the 
> > first
> > query?
> >
> > Thanks,
>
> .
> 
0
Gina
3/16/2010 11:11:56 PM
Reply:

Similar Artilces:

Exchange rich-text format
What are the results from the client side if I change my Exchange 2003 server to "Alwayes Use" Exchange Rich-Text format from "Determined by individual user settings"? On Thu, 22 Jun 2006 08:31:01 -0700, CK <CK@discussions.microsoft.com> wrote: >What are the results from the client side if I change my Exchange 2003 server >to "Alwayes Use" Exchange Rich-Text format from "Determined by individual >user settings"? Depends on what your users are sending messages as. Most will be using HTML or RTF anyway unless you have some policy in p...

newsletter formatting
I have designed a newsletter in Publisher that I e-mail as a message. The format is saves as HTML. The original recipient can view the newsletter fine, however, when the newsletter is forwarded the formatting gets rearranged. How can I keep the original newsletter the same for orignal recipients as when it gets forwarded? Thanks, Fredrik Fredrik A <Fredrik A@discussions.microsoft.com> was very recently heard to utter: > How can I keep the original newsletter the same for orignal > recipients as when it gets forwarded? Convert to PDF using an application such as PrimoPDF (www...

Copy and Paste not saving format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I just bought Microsoft Office for Mac early this month and am still getting used to it. Can someone help me with this: I have several documents typed onto a notebook layout with lots of bullet points, color changes, cross outs and the like. I wanted to copy and past all of that into a new document but when I did ALL of the formatting changes were lost. There were no bullet points, so the text flushed left, yada, yada, yada. I looked around the toolbar to try and find a setting that would allow me to do the cut an...

Query Problems
I have a table of ecards that is populated from a website and includes receiver, sender, and Team leader. The problem I am having is names can be entered into the table like this "Tom Thumb", but Team leader is a dropdown menu that is constant and lists name like this: "Tom S. Thumb". When I make a query to find all the members of Tom S. Thumb's team, I get everyone except Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I can't change that. How do I get Tom Thumb to be included when I enter "Tom S. Thumb" as Team Leader? ...

Query to Mail Merge issue
I am running Access2003 on WinXP SP2. I send out dues letters for members of a retiree group. When I try to do a mail merge to my Dues Query all goes fine except the telephone number in the query which is (555)555-1234 comes out 5555551234. If I copy the query into an excel spreadsheet and use that as the datasource for the mail merge it works fine. Is there something I need to do to format the query so it merges with the right format. Thanks Billa In the query format the field to include the parentheses and dash. In query design view, instead of the field name put an expression alo...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

If statement with formatted text
Hi, Is there any way to have an if formula such as: If (A1="Active", "KAE",KPE") where the two letters after the K are formatted as subscript? The best I've done is to paste a picture over the cell. The picture's formula refers to named formula that selects one of two cells, the one with correct string. One cell contains KAE and the other KPE with the subscript. However, it means that I'm using a picture and it would be much nicer if I could just do it in an If formula. I hope that makes some kind of sense, and thanks in advance for your help. D...

Formatting cells from internet
I have copied and pasted a chart from a website. (The website will not let me import) The cells are showing numbered values, but when i go to put the formula in it is giving me a zero like it cannot read the numbers in the cell. That is on 3 seperate columns. On the very last column it is reading the numbers I cannot make it recognize that those columns are numbers to be used in formulas Thanks for your help! Use Dave McRitchie's Trimall macro to clean up the data. Then copy an empty cell, select all your data and do Edit / PasteSpecial / Add and it will coerce all data back to nu...

Conditial Formatting Not Available
After trying to use conditional formatting (which didn't work btw) now the Conditional Formatting button in the ribbon isn't available or disabled. I tried Compact and Repair but it didn't work. Any ideas? James Duh. I was selecting a label instead of the field. "JamesJ" <jjy@adelphia_darwin.net> wrote in message news:uJhfSCSKIHA.1324@TK2MSFTNGP06.phx.gbl... > After trying to use conditional formatting (which didn't work btw) now > the Conditional Formatting button in the ribbon isn't available or > disabled. > I tried Compact and Repair b...

Cannot view itme in Public Folder between 2 different exchange 2003
I have 2 Exchange 2003 on WIN2k server. Exchange 2003 (A) has 300 mailboxes and store the public folder. Exchange 2003 (B) has 100 mailboxes. User at Exchange 2003 (B) found that they can view the public folder but no items in it. User at Exchange 2003 (A) do not have this problem. Is the public folder must replicate to other server? Is any suggestion to view the item if no replication on public folder ?? Thanks Dennis JoJo These 2 servers in the same routing group. Exchange 2003 (A) is the Master Exchange 2003 (B) is the Member "omer maydan" <omermaydan@yahoo.com> wr...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Saving html message as Draft changes text formatting...
WIN XP HE, OL 2002 Hi, I have recently noticed that whenever I write an email (using Word as editor) in html format, and instead of sending it, save it (to the drafts folder), the text itself changes format from my default to another one. It seems to change in the paragraph style which then changes the text format. The only change I recently made was to edit my signatures in html, rtf and plain text format. When I write a new email, it opens up with the signature already in it and perhaps there are format/style conflicts..? Tx for shedding some light into this. S As an added information, t...

Amazing Problem in AllocPhysMem wince 6.0
Hi I am writing a Port Driver for x86 platform in WinCE 6.0. I want to allocate virtual and equivalent physical memory in driver and mapped it to USER mode to use application. For that I used AllocPhysMem in driver and passed that address through IOCTL calls but i cant use that virtual and physical address in application side. Because AllocPhysMem returns Error Code as 0x57 (meaning Parameter incorrect). But the same code is working in WinCe 5.0. My code snippet is, VirAddress = (LPVOID)AllocPhysMem(32, PAGE_READWRITE|PAGE_NOCACHE, 0, ...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Restrictions in format
How do I restrict some columns of having all text in UPPER CASE, some columns to have text only First In Capital Letter and some columns only in lower case?? Thanks JPG You can restrict using Data Validation but I would find that very annoying. Example............in DV>Allow>Custom =EXACT(A1,UPPER(A1)) will not allow anything but Uppercase. I would use event code to automatically change Case when anything is entered. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo ErrHandler Application.EnableEvents = False With Target ...

Windows and Mac have two distinctly different units for column width.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to format columns for some data I am entering in a spreadsheet and when I enter &quot;15.00&quot;, which is the required width for these columns given by my professor, I end up with a column 15 inches wide. What I would prefer is for the options to be like the default options in Windows version of Excel. In Windows version of Excel, when you hover over the lines between the columns it gives you two numbers (e.g. 8.43 (64 pixels)). These are the default numbers for column width in Windows Excel....

Spontaneous 'Compile Error' In Queries
I've got a half-dozen queries that contain this expression: IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName]) Haven't touched any of them, but suddenly they were all throwing "Compile error. in query expression 'IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName])'." tblIssuer.IssuerID is still there in a link. My kneejerk was to somehow force recompiles of all the queries, so I did a Compact/Repair. After the compact/repair, all was well: the errors went away. C...

Conditional Format #14
That's what I was messing up! It was the ordering, not the formula! Thank you. Now if I have my =AND(E2="Y") for one and no formatting and my second =C2<TODAY() Red bold format, I should be able to mak rule 3 =C2<TODAY()-2 format Yellow bold and have anything 2 days clos to due yellow, YES -- Jimmytec ----------------------------------------------------------------------- Jimmytech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1447 View this thread: http://www.excelforum.com/showthread.php?threadid=26087 RE: =AND(E2="Y") You o...

Two different cells
I have a file with more the 1000 names and addresses. My proble is that the firat and last names are in 2 differents cells. I i would like to put it in teh same cell. Is there anyway I csn do that? Please help too many names to re-type Carlos Assuming that Column A contains the first name and Column B the last name... =A1&" "&B1 OR =B1&", "&A1 ....depending on your preference. Hope this helps! In article <520FA0CA-1940-42E5-B84D-22BA15D59638@microsoft.com>, "Hombreck" <Hombreck@discussions.microsoft.com> wrote: > I have a...

Conditional Formatting
Hi I need some help with conditional formatting. I'm trying to highlight those cells which have a plus sign in them. Can someone help with the formula to enter into the conditional formatting dialogue? Grateful for any assistance. Best Wishes Hi Colin if the Plus sign + is part of a text string Select the column range first then =FIND("+",A2:$A$160) change the range to your needs. If the + sign is alone in the cell, you don't need a formula, just select > Cell value is > then select Equal to> and type the plus sign in the third box. H...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...