Convert to text
in [Queries]
|
Prev: How do I copy a SQL query from one database to another in Acce
Next: Concatenate same field records into one - FOLLOW UP
From: Gina Whipp on 3 Jan 2010 19:47 Prohock, REread the instructions. The CStr(), making the field Text has nothing to do with the error you are getting. You are missing a parameter and your SELECT statement is lacking. -- 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 "Prohock" <Prohock(a)discussions.microsoft.com> wrote in message news:E1300502-8C33-441E-8128-690740E0FC50(a)microsoft.com... > Hi Jeff > > I tried but I still get errors when I run > > SELECT ConcatRelated("[DateLength]","[QryPrevious]") AS PreSuspList; > > I says Error 3061: Too Few Parameters. Expected 2 > > > "Jeff Boyce" wrote: > >> If the output of your conversion needs to be of type "text", you can use >> the >> CStr() function in your query to coerce the result into string/text. >> >> -- >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> Disclaimer: This author may have received products and services mentioned >> in >> this post. Mention and/or description of a product or service herein does >> not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "Prohock" <Prohock(a)discussions.microsoft.com> wrote in message >> news:86B0267B-C398-4D2F-8D5D-82DD519525D9(a)microsoft.com... >> >I have a query that has one field that combines two other fields. >> > >> > Date Length Combined >> > 12/2/2009 3 Day 12/2/2009 - 3 day >> > >> > I need the combined field to in plain text format. >> > >> > I tried the following hoping that it would work...no luck! >> > >> > Text(DateLength:[Date] &" - "& [Length]) >> > >> > Please help >> >> >> . >>
From: Gina Whipp on 3 Jan 2010 20:00 Oops, forgot, if you need some help please let us know what you are Grouping on. -- 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 "Gina Whipp" <NotInterested(a)InViruses.com> wrote in message news:%23PN6BeNjKHA.2780(a)TK2MSFTNGP05.phx.gbl... > Prohock, > > REread the instructions. The CStr(), making the field Text has nothing to > do with the error you are getting. You are missing a parameter and your > SELECT statement is lacking. > > -- > 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 > > "Prohock" <Prohock(a)discussions.microsoft.com> wrote in message > news:E1300502-8C33-441E-8128-690740E0FC50(a)microsoft.com... >> Hi Jeff >> >> I tried but I still get errors when I run >> >> SELECT ConcatRelated("[DateLength]","[QryPrevious]") AS PreSuspList; >> >> I says Error 3061: Too Few Parameters. Expected 2 >> >> >> "Jeff Boyce" wrote: >> >>> If the output of your conversion needs to be of type "text", you can use >>> the >>> CStr() function in your query to coerce the result into string/text. >>> >>> -- >>> >>> Regards >>> >>> Jeff Boyce >>> Microsoft Access MVP >>> >>> Disclaimer: This author may have received products and services >>> mentioned in >>> this post. Mention and/or description of a product or service herein >>> does >>> not constitute endorsement thereof. >>> >>> Any code or pseudocode included in this post is offered "as is", with no >>> guarantee as to suitability. >>> >>> You can thank the FTC of the USA for making this disclaimer >>> possible/necessary. >>> >>> "Prohock" <Prohock(a)discussions.microsoft.com> wrote in message >>> news:86B0267B-C398-4D2F-8D5D-82DD519525D9(a)microsoft.com... >>> >I have a query that has one field that combines two other fields. >>> > >>> > Date Length Combined >>> > 12/2/2009 3 Day 12/2/2009 - 3 day >>> > >>> > I need the combined field to in plain text format. >>> > >>> > I tried the following hoping that it would work...no luck! >>> > >>> > Text(DateLength:[Date] &" - "& [Length]) >>> > >>> > Please help >>> >>> >>> . >>> > >
From: Duane Hookom on 3 Jan 2010 21:52 Why did you start a new thread when you had the attention of the most able and willing Allen Browne in a previous thread? He asked you a question about a criteria in the query and you ignored it. Please go back to re-read Allen's question and then answer it. If you can't figure this out, post the SQL view of your query. -- Duane Hookom Microsoft Access MVP "Prohock" wrote: > Hi John > > I have change all my field names to follow proper Access naming conventions. > > The problem is the value that it creates does not work with the > ConcatRelated function that Allen Browne has created. > > Specifically > > In my query "QryPrevious" , I have a combined field consisting of two fields > called "DateLength". > > [Startdate] &" - "& [SuspLength] > > Startdate is a date field from a table. > > The results of QryPrevious are > > 12/2/2009 - 3 day > 12/4/2009 - 2 day > 12/8/2009 - 3 day > > I would to combine them into a single field, called [Alldates]. Like the > following > > 12/2/2009 - 3 day, 12/4/2009 - 2 day, 12/8/2009 - 3 day > > What can I do with the [Startdate] &" - "& [SuspLength] so it becomes TEXT > so that the ConcatRelated can resolve it as a single value? > > If I use simple values like > > A > B > C > > the function works fine and will make > > ABC. > > I hope that this makes sense, and I am sorry to all for not explaining this > very well. Thanks for taking an interest in this problem. > > Prohock > > "John W. Vinson" wrote: > > > On Sun, 3 Jan 2010 10:12:01 -0800, Prohock <Prohock(a)discussions.microsoft.com> > > wrote: > > > > >I have a query that has one field that combines two other fields. > > > > > >Date Length Combined > > >12/2/2009 3 Day 12/2/2009 - 3 day > > > > > >I need the combined field to in plain text format. > > > > > >I tried the following hoping that it would work...no luck! > > > > > >Text(DateLength:[Date] &" - "& [Length]) > > > > > >Please help > > > > I'm perplexed that you're having a problem at all. I agree that Date and > > Length are bad choices of fieldnames, but I believe you're creating a problem > > where there isn't one. > > > > The & concatenation operator will return a Text value *whatever* the datatype > > of the component fields! > > > > The expression > > > > [Date] & "-" & [Length] > > > > will return a Text value, consisting of the date value in the field named Date > > expressed in your computer's Regional Settings date format, followed by a > > hyphen, followed by the value of Length (assuming that Length is a Text field > > containing "3 Day"). > > > > Is this not what you're getting? > > > > What prompts the question? Are you getting an error using the Concatenate > > function? If so what error? > > -- > > > > John W. Vinson [MVP] > > . > >
From: Prohock on 4 Jan 2010 02:18
Hi Duane I missed the question... I was not in the right frame of mind to understand its relevance at the time. Believe me, it was not ignored on purpose. I will follow up with the other post. "Duane Hookom" wrote: > Why did you start a new thread when you had the attention of the most able > and willing Allen Browne in a previous thread? He asked you a question about > a criteria in the query and you ignored it. > > Please go back to re-read Allen's question and then answer it. > > If you can't figure this out, post the SQL view of your query. > > -- > Duane Hookom > Microsoft Access MVP > > > "Prohock" wrote: > > > Hi John > > > > I have change all my field names to follow proper Access naming conventions. > > > > The problem is the value that it creates does not work with the > > ConcatRelated function that Allen Browne has created. > > > > Specifically > > > > In my query "QryPrevious" , I have a combined field consisting of two fields > > called "DateLength". > > > > [Startdate] &" - "& [SuspLength] > > > > Startdate is a date field from a table. > > > > The results of QryPrevious are > > > > 12/2/2009 - 3 day > > 12/4/2009 - 2 day > > 12/8/2009 - 3 day > > > > I would to combine them into a single field, called [Alldates]. Like the > > following > > > > 12/2/2009 - 3 day, 12/4/2009 - 2 day, 12/8/2009 - 3 day > > > > What can I do with the [Startdate] &" - "& [SuspLength] so it becomes TEXT > > so that the ConcatRelated can resolve it as a single value? > > > > If I use simple values like > > > > A > > B > > C > > > > the function works fine and will make > > > > ABC. > > > > I hope that this makes sense, and I am sorry to all for not explaining this > > very well. Thanks for taking an interest in this problem. > > > > Prohock > > > > "John W. Vinson" wrote: > > > > > On Sun, 3 Jan 2010 10:12:01 -0800, Prohock <Prohock(a)discussions.microsoft.com> > > > wrote: > > > > > > >I have a query that has one field that combines two other fields. > > > > > > > >Date Length Combined > > > >12/2/2009 3 Day 12/2/2009 - 3 day > > > > > > > >I need the combined field to in plain text format. > > > > > > > >I tried the following hoping that it would work...no luck! > > > > > > > >Text(DateLength:[Date] &" - "& [Length]) > > > > > > > >Please help > > > > > > I'm perplexed that you're having a problem at all. I agree that Date and > > > Length are bad choices of fieldnames, but I believe you're creating a problem > > > where there isn't one. > > > > > > The & concatenation operator will return a Text value *whatever* the datatype > > > of the component fields! > > > > > > The expression > > > > > > [Date] & "-" & [Length] > > > > > > will return a Text value, consisting of the date value in the field named Date > > > expressed in your computer's Regional Settings date format, followed by a > > > hyphen, followed by the value of Length (assuming that Length is a Text field > > > containing "3 Day"). > > > > > > Is this not what you're getting? > > > > > > What prompts the question? Are you getting an error using the Concatenate > > > function? If so what error? > > > -- > > > > > > John W. Vinson [MVP] > > > . > > > |