concatenate 2 fields and add new field to table Update query

[jackie]

Hi,
I'm pretty sure this is an update query, but I'm not sure of the syntax. I
tried to do it in SQL, but I get syntax errors.
I have two fields in one able: 'material group' and 'niin'. I need a new
field in the table called NSN.
I have a form, and I want the user to be able to search on NSN, and I dont'
know any other way to do it except to join the two fields into one.
Thanks.

 

[Douglas J. Steele]

You don't want to duplicate data in the table.

Instead, simply create a Select query, and add a computed field in it to
concatenate the two fields. Assuming you're building the query through the
graphical builder, you'd add something like the following to an empty cell
on the FIeld: row:

NSN: [material group] & " " & [niin]

 

[jackie]

Thanks, Doug.
I already have the field in my query. I went into the SQL and tried to add
the field. I did:

AND ((Jan_08_Orders.NSN Like "*" & [Forms]![QBF_form]![NSN] & "*") OR
([Forms]![QBF_form]![NSN] IS NULL))

then I tried:
((Jan_08_Orders.NSN Like "*" & Forms!QBF_form!NSN & "*") Or
(Forms!QBF_form!NSN Is Null))

I also tried going into the query designer, adding the cocantenated field,
and entering a criteria is not null. Still no luck.

The problem is there's no field called NSN. So how do I create a search box

You don't want to duplicate data in the table.

Instead, simply create a Select query, and add a computed field in it to
concatenate the two fields. Assuming you're building the query through the
graphical builder, you'd add something like the following to an empty cell
on the FIeld: row:

NSN: [material group] & " " & [niin]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
I'm pretty sure this is an update query, but I'm not sure of the syntax. I
tried to do it in SQL, but I get syntax errors.
I have two fields in one able: 'material group' and 'niin'. I need a new
field in the table called NSN.
I have a form, and I want the user to be able to search on NSN, and I
dont'
know any other way to do it except to join the two fields into one.
Thanks.

 

[jackie]

Hi,
I updated the table to included a field called NSN which is made up of two
fields joined together.
The only problem is when I get new data for field1 and field2, I'll have to
run the update again.
Is there any other way? Thanks

Thanks, Doug.
I already have the field in my query. I went into the SQL and tried to add
the field. I did:

AND ((Jan_08_Orders.NSN Like "*" & [Forms]![QBF_form]![NSN] & "*") OR
([Forms]![QBF_form]![NSN] IS NULL))

then I tried:
((Jan_08_Orders.NSN Like "*" & Forms!QBF_form!NSN & "*") Or
(Forms!QBF_form!NSN Is Null))

I also tried going into the query designer, adding the cocantenated field,
and entering a criteria is not null. Still no luck.

The problem is there's no field called NSN. So how do I create a search box

You don't want to duplicate data in the table.

Instead, simply create a Select query, and add a computed field in it to
concatenate the two fields. Assuming you're building the query through the
graphical builder, you'd add something like the following to an empty cell
on the FIeld: row:

NSN: [material group] & " " & [niin]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
I'm pretty sure this is an update query, but I'm not sure of the syntax. I
tried to do it in SQL, but I get syntax errors.
I have two fields in one able: 'material group' and 'niin'. I need a new
field in the table called NSN.
I have a form, and I want the user to be able to search on NSN, and I
dont'
know any other way to do it except to join the two fields into one.
Thanks.

 

[Douglas J. Steele]

What's the entire SQL look like?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, Doug.
I already have the field in my query. I went into the SQL and tried to add
the field. I did:

AND ((Jan_08_Orders.NSN Like "*" & [Forms]![QBF_form]![NSN] & "*") OR
([Forms]![QBF_form]![NSN] IS NULL))

then I tried:
((Jan_08_Orders.NSN Like "*" & Forms!QBF_form!NSN & "*") Or
(Forms!QBF_form!NSN Is Null))

I also tried going into the query designer, adding the cocantenated field,
and entering a criteria is not null. Still no luck.

The problem is there's no field called NSN. So how do I create a search
box

You don't want to duplicate data in the table.

Instead, simply create a Select query, and add a computed field in it to
concatenate the two fields. Assuming you're building the query through
the
graphical builder, you'd add something like the following to an empty
cell
on the FIeld: row:

NSN: [material group] & " " & [niin]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
I'm pretty sure this is an update query, but I'm not sure of the
syntax. I
tried to do it in SQL, but I get syntax errors.
I have two fields in one able: 'material group' and 'niin'. I need a
new
field in the table called NSN.
I have a form, and I want the user to be able to search on NSN, and I
dont'
know any other way to do it except to join the two fields into one.
Thanks.

 

[John Spencer]

Jackie,
I think what you want might be

Field: NSN: [Material Group] & [NIN]
Criteria: LIKE "*" & [Forms]![QBF_form]![NSN] & "*"

In the SQL statement the WHERE clause would be

WHERE [Material Group] & [NIN] LIKE "*" & [Forms]![QBF_form]![NSN] & "*"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..