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: Jeff Boyce on 3 Jan 2010 15:10
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: John W. Vinson on 3 Jan 2010 16:39
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 3 Jan 2010 17:35
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 3 Jan 2010 17:43
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: Jeff Boyce on 3 Jan 2010 18:42
I don't see how what you did has anything to do with the CStr() function...

How and where are you "running" that SQL statement?

What does the ConcatRelated() function do? It appears to require two
parameters ... is that just coincidental?

--

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: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
>>
>>
>> .
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How do I copy a SQL query from one database to another in Acce
Next: Concatenate same field records into one - FOLLOW UP