Convert to text

[Prohock]

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

 

[Jellifish]

FORMAT([Date],"d/m/yyyy")&" - "&[Length]

Date is a pretty bad name for a column, you should re-name it to something
more descriptive i.e. StartDate.

 

[Gina Whipp]

Prohock,

In addition to the answer you got...

I see you have fields named Date and Length. These are Reserved Words and
will cause you issues as they are issues for Access. Consider changing
them. For a complete list see...

http://allenbrowne.com/AppIssueBadWord.html

For naming conventions see...

http://www.granite.ab.ca/access/tablefieldnaming.htm

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

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

 

[theDBguy]

Hi,

Is this in an Access Query? I only ask because I think TEXT() is an Excel
function and Date and Length are "reserved" words in Access.

You might consider renaming your fields to something else and then try
something like:

Combined: Format([DateField], "mm/dd/yyyy") & " - " & [LengthField]

Hope that helps...

 

[Prohock]

Thanks for the suggestions.

The problem I am having is that I need the combined field to be a TEXT field
in order to run the ConcatRelated function that Allen Browne created.
Currently access can't resolved the combined fields. If they were seen as
TEXT then it could be resolved. Any ideas?

FORMAT([Date],"d/m/yyyy")&" - "&[Length]

Date is a pretty bad name for a column, you should re-name it to something
more descriptive i.e. StartDate.

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

.

 

[Jeff Boyce]

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.

 

[John W. Vinson]

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?

 

[Prohock]

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

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?

 

[Prohock]

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

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.

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

.

 

[Jeff Boyce]

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.

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

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.

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

.

 

[Gina Whipp]

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

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

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.

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

.

 

[Gina Whipp]

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

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

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

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.

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


.

 

[Duane Hookom]

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

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

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?

 

[Prohock]

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.

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

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

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?