424,647 Members | 1,444 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

# Combining Columns

 P: n/a I'm thinking this is simple and I'm just not understanding how to do it but I have to ask because I'm stumped. I have a query (which is used to create a report) and three of the columns in this query are Date, Charge by Hour, and Charge by Day. In my report all three of these columns show. I'd like to have only two columns in my report; Date, Charge (charge would consist of Charge by Hour and Charge by Day). How can I make that happen? I've tried several things but can't seem to get it done. Thanks, Don.......... Jan 14 '06 #1
6 Replies

 P: n/a Don wrote: I'm thinking this is simple and I'm just not understanding how to do it but I have to ask because I'm stumped. I have a query (which is used to create a report) and three of the columns in this query are Date, Charge by Hour, and Charge by Day. In my report all three of these columns show. I'd like to have only two columns in my report; Date, Charge (charge would consist of Charge by Hour and Charge by Day). How can I make that happen? I've tried several things but can't seem to get it done. Thanks, Don.......... To combine (concatenate) two fields in a query you use... Charge: [Charge by Hour] & [Charge by Day] Of course that would run the two right together so I assume you want at least a space between them. That would be... Charge: [Charge by Hour] & " " & [Charge by Day] -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Jan 14 '06 #2

 P: n/a "Don" wrote in news:rq8yf.77\$Iw3.0@trndny06: I'm thinking this is simple and I'm just not understanding how to do it but I have to ask because I'm stumped. I have a query (which is used to create a report) and three of the columns in this query are Date, Charge by Hour, and Charge by Day. In my report all three of these columns show. I'd like to have only two columns in my report; Date, Charge (charge would consist of Charge by Hour and Charge by Day). How can I make that happen? I've tried several things but can't seem to get it done. Thanks, Don.......... Your question is unclear. Do you want charge by hour to appear when some condition is met, and charge by day if not that condition? if that's the case you would use the iff() function Make the texbox =iif(something = true, [Charge by Hour], [Charge by Day]) -- Bob Quintal PA is y I've altered my email address. Jan 14 '06 #3

 P: n/a Rick thanks that exactly what I was trying to do. Another question if you don't mind. I think you call this an "expression". This field, or expression, if I want it to show as currency how does that happen. In the format box it doesn't give me the option to set it up as currency. So what I've got now are hours which may show as something like, 8.499996 and days which may show as 17. I'd like them to show as \$8.50 and \$17.00 but I'm not sure how to make that happen. Thanks again for the first reply it does exactly what I've asked for. Don............... "Rick Brandt" wrote in message news:Kt**************@newssvr29.news.prodigy.net.. . Don wrote: I'm thinking this is simple and I'm just not understanding how to do it but I have to ask because I'm stumped. I have a query (which is used to create a report) and three of the columns in this query are Date, Charge by Hour, and Charge by Day. In my report all three of these columns show. I'd like to have only two columns in my report; Date, Charge (charge would consist of Charge by Hour and Charge by Day). How can I make that happen? I've tried several things but can't seem to get it done. Thanks, Don.......... To combine (concatenate) two fields in a query you use... Charge: [Charge by Hour] & [Charge by Day] Of course that would run the two right together so I assume you want at least a space between them. That would be... Charge: [Charge by Hour] & " " & [Charge by Day] -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Jan 14 '06 #4

 P: n/a Don wrote: Rick thanks that exactly what I was trying to do. Another question if you don't mind. I think you call this an "expression". This field, or expression, if I want it to show as currency how does that happen. In the format box it doesn't give me the option to set it up as currency. So what I've got now are hours which may show as something like, 8.499996 and days which may show as 17. I'd like them to show as \$8.50 and \$17.00 but I'm not sure how to make that happen. Charge: Format([Charge by Hour], "Currency") & " " & Format([Charge by Day], "Currency") -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Jan 14 '06 #5

 P: n/a No I don't think that's what I want. I'm sorry I haven't be clearer. Here's the situation as best as I can explain it. Actually in my first question I wasn't completely correct in my description of what I have. Let me try again. I'm setting up a database that will keep track of charges. There is a date field (which would be Date of Service). Then there is another field which would be used if the service was for less than a complete day (Charge by Hour). And then the last field would be all charges that were complete days (Charge by Day). I have an expression that multiplies 1.888888 times the number of hours, let's say 4 hours (this calculates to 8.49996). I have another expression that multiplies 17 times the number of days (in every case this would be 1, so it always calculates to 17). So when my report opens up it shows the number of "Hours" in once column and the number of "Days" in a second column. I was mistaken by saying I wanted Charge by Hour and Charge by Day in the same column, what I really wanted was the expression "Hours" and the expression "Days" in the same column. I took Bill's example and just substituted Charge by Hour for Hours and Charge by Day for Days. It works exactly as I asked. The only problem is the "Hours, 8.4996" and the "Days 17" don't show as currency with two decimals. That's about as detailed as I can get. I've probably confused the issue even more, huh? I thank you for taking the time to try and understand my problem/question. I wish I understood all of this much better so I could do it myself or at least explain it better. Unfortunately I'm, as well as everyone else, is stuck with what I am. Thanks again, Don.............. "Bob Quintal" wrote in message news:Xn**********************@207.35.177.135... "Don" wrote in news:rq8yf.77\$Iw3.0@trndny06: I'm thinking this is simple and I'm just not understanding how to do it but I have to ask because I'm stumped. I have a query (which is used to create a report) and three of the columns in this query are Date, Charge by Hour, and Charge by Day. In my report all three of these columns show. I'd like to have only two columns in my report; Date, Charge (charge would consist of Charge by Hour and Charge by Day). How can I make that happen? I've tried several things but can't seem to get it done. Thanks, Don.......... Your question is unclear. Do you want charge by hour to appear when some condition is met, and charge by day if not that condition? if that's the case you would use the iff() function Make the texbox =iif(something = true, [Charge by Hour], [Charge by Day]) -- Bob Quintal PA is y I've altered my email address. Jan 14 '06 #6

 P: n/a Don wrote: No I don't think that's what I want. I'm sorry I haven't be clearer. Here's the situation as best as I can explain it. You can still use the Format() function within a larger expression to apply formatting to that portion of the output. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Jan 14 '06 #7

### This discussion thread is closed

Replies have been disabled for this discussion.