creating a concatenated field - microsoft.public.access.queries

I would like to combine 3 fields with 3 different data types: string, date and integer. I would like to combine them into a string. What do I need to do to convert the datea nd integer values into strings in order to save the 3 together as a string field. I would like to do this with in the query. Thanks!

If you are using Access you should be able to use the "&" concatention operator. Field: Combined: StringField & DateField & integerField. Are any of the fields ever Null? If so, what do you want to display for the null values Do you want specific formats for the date and integer fields? For example, numbers should be five characters long with leading zeroes; dates should be in the format "mm-dd-yyyy". Do you want separators between the values in the sections? -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "querykitty" <querykitty@discussions.microsoft.com> wrote in message news:ADE4FAC8-56F3-4F12-ADC7-24FA0253BD00@microsoft.com... >I would like to combine 3 fields with 3 different data types: string, date > and integer. I would like to combine them into a string. What do I need > to > do to convert the datea nd integer values into strings in order to save > the 3 > together as a string field. I would like to do this with in the query. > > Thanks!

Replace(StringField," ","") & Format(DateField, "yyyymmdd") & IntegerField Use the replace function to remove all spaces from the string field Use the format function to return the date as a string in the specified format -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "querykitty" <querykitty@discussions.microsoft.com> wrote in message news:3C7D5478-D26C-437A-AA96-EA4782B5EE5F@microsoft.com... >I want the date to just read as numbers such as 20070405 and all spaces to >be > taken out of the text field so I have one long string. It is going to be > used as a reference key so each will be unique. > > Thanks for your assistance. > > "John Spencer" wrote: > >> If you are using Access you should be able to use the "&" concatention >> operator. >> >> Field: Combined: StringField & DateField & integerField. >> >> Are any of the fields ever Null? If so, what do you want to display for >> the >> null values >> >> Do you want specific formats for the date and integer fields? For >> example, >> numbers should be five characters long with leading zeroes; dates should >> be >> in the format "mm-dd-yyyy". >> >> Do you want separators between the values in the sections? >> >> >> >> -- >> John Spencer >> Access MVP 2002-2005, 2007 >> Center for Health Program Development and Management >> University of Maryland Baltimore County >> .. >> >> "querykitty" <querykitty@discussions.microsoft.com> wrote in message >> news:ADE4FAC8-56F3-4F12-ADC7-24FA0253BD00@microsoft.com... >> >I would like to combine 3 fields with 3 different data types: string, >> >date >> > and integer. I would like to combine them into a string. What do I >> > need >> > to >> > do to convert the datea nd integer values into strings in order to save >> > the 3 >> > together as a string field. I would like to do this with in the query. >> > >> > Thanks! >> >> >>

Similiar Articles:

creating a concatenated field - microsoft.public.access.queries ...
If you are using Access you should be able to use the "&" concatention operator. Field: Combined: StringField & DateField & integerField. Are any of the fields ever ...

Formatting Concatenated Fields - microsoft.public.access.queries ...
Sequence Number To A Query; Format A Number As A Text In A ... CREATE TABLE ... creating a concatenated field ... Store Employee ID in table, Display User Name in form ...

Create function concatenate - microsoft.public.sqlserver ...
I want to know How I can create a function that will concatenate ... My stored procedure always returns a varchar(4000) field containing the values concatenated.

join 2 fields into one so user can search on new field - microsoft ...
creating a concatenated field - microsoft.public.access.queries ... I would like to combine them into a string ... all spaces to >be > taken out of the text field so I ...

concatenating a field from multiple subform records - microsoft ...
I'm trying to build a concatenated string of email addresses ... Email field in subform: txtEmail Checkbox field ... records? > > Thanks in advance, > > Paul create a ...

Creating ID with numbers and text - microsoft.public.access ...
Sequence Number To A Query; Format A Number As A Text In A ... CREATE TABLE ... creating a concatenated field ... Store Employee ID in table, Display User Name in form ...

How do you create a formula subtracting 1 field into a 2nd field ...
How do you create a formula subtracting 1 field into a 2nd field ... I am trying to subtract ... How to Create an Excel Formula | eHow.com How to Create an Excel Formula.

Concatenate fields without duplicating data - microsoft.public ...
I have two tables that I'm creating a query with. Table 1 has Agent field and Table 2 has product field. I'm linking the tables by a primary key...

Display first 3 characters in a field - microsoft.public.access ...
I created a table for the marketing codes information and linked to a field, etc ... creating join with only the first 4 characters of a field ... MS ACCESS :: Query All ...

complex query to pull unique values - microsoft.public.access ...
You can group by up to TEN fields; it is neither necessary nor particularly helpful to create a redundant concatenated field. >I can pull unique Supervisor/location ...

Creating a Concatenated Bibliography Field For 3 or Fewer ...
Stay tuned for new security updates and database security notifications.

MS Access: Concatenate two fields in a query in Access 2007
In the example below, we've decided to create a field in our query that is ... The concatenated field that we called Name and the Address field from the Employees table.

How to Concatenate Cells in Microsoft Access
step is to create the new, concatenated column. Scroll to the end of the list of query fields, to the first available, empty box. Click in the first row (the Field row ...

Access/VBA Tutorials - Concatenated fields yield garbage in recordset
The idea is to create a table with a similar structure, but a Memo field where you need the concatenated field. The table has no records. Access looks as the first table ...

7/27/2012 3:08:37 PM