Migrating Access Queries to SQL Server

[Arch]

The following code is from Access and I am migrating all
the queries to SQL server. Please help me convert this
String Concatenation along with a column name of a table
to SQL server. Once I transferred the query into the SQL
Query Analyzer, I tried replacing a dot for ! mark and +
sign for &. But it still comes up with an error. It looks
for a column name called Project Awarded and says 'invalid
column name'. It does not read the string as text.

CREATE VIEW [Projects Awarded] AS
SELECT Mun_Code_Name.County, "Project Awarded " & [Award]!
[AwardDate] & ". " AS Remarks2, Award.CloseOutStatus
FROM .......etc.

Any help regarding this will be appreciated very much.
Thanks
Arch

 

[Duane Hookom]

I would never create a view, table, stored procedure,... with spaces in the
name.

CREATE VIEW v_ProjectsAwarded
AS
SELECT Mun_Code_Name.County, 'Project Awarded ' & Convert(varchar(11),
Award.AwardDate) + '. ' AS Remarks2, Award.CloseOutStatus
FROM .......etc

You may want check Books On Line regarding date formats in Convert().

 

[Arch]

Thank You very much for the reply. I tried it but now I
have the following error:
Invalid operator for data type. Operator equals boolean
AND, type equals varchar.
Appreciate your help.
Arch

-----Original Message-----
I would never create a view, table, stored procedure,... with spaces in the
name.

CREATE VIEW v_ProjectsAwarded
AS
SELECT Mun_Code_Name.County, 'Project Awarded ' & Convert (varchar(11),
Award.AwardDate) + '. ' AS Remarks2, Award.CloseOutStatus
FROM .......etc

You may want check Books On Line regarding date formats in Convert().

--
Duane Hookom
MS Access MVP
--

The following code is from Access and I am migrating all
the queries to SQL server. Please help me convert this
String Concatenation along with a column name of a table
to SQL server. Once I transferred the query into the SQL
Query Analyzer, I tried replacing a dot for ! mark and +
sign for &. But it still comes up with an error. It looks
for a column name called Project Awarded and says 'invalid
column name'. It does not read the string as text.

CREATE VIEW [Projects Awarded] AS
SELECT Mun_Code_Name.County, "Project Awarded " & [Award]!
[AwardDate] & ". " AS Remarks2, Award.CloseOutStatus
FROM .......etc.

Any help regarding this will be appreciated very much.
Thanks
Arch

.

 

[Duane Hookom]

My bad, try:
CREATE VIEW v_ProjectsAwarded
AS
SELECT Mun_Code_Name.County, 'Project Awarded ' + Convert(varchar(11),
Award.AwardDate) + '. ' AS Remarks2, Award.CloseOutStatus
FROM .......etc

This worked for me and assumes AwardDate is a date field.

--
Duane Hookom
MS Access MVP
--

Thank You very much for the reply. I tried it but now I
have the following error:
Invalid operator for data type. Operator equals boolean
AND, type equals varchar.
Appreciate your help.
Arch

-----Original Message-----
I would never create a view, table, stored procedure,... with spaces in the
name.

CREATE VIEW v_ProjectsAwarded
AS
SELECT Mun_Code_Name.County, 'Project Awarded ' & Convert (varchar(11),
Award.AwardDate) + '. ' AS Remarks2, Award.CloseOutStatus
FROM .......etc

You may want check Books On Line regarding date formats in Convert().

--
Duane Hookom
MS Access MVP
--

The following code is from Access and I am migrating all
the queries to SQL server. Please help me convert this
String Concatenation along with a column name of a table
to SQL server. Once I transferred the query into the SQL
Query Analyzer, I tried replacing a dot for ! mark and +
sign for &. But it still comes up with an error. It looks
for a column name called Project Awarded and says 'invalid
column name'. It does not read the string as text.

CREATE VIEW [Projects Awarded] AS
SELECT Mun_Code_Name.County, "Project Awarded " & [Award]!
[AwardDate] & ". " AS Remarks2, Award.CloseOutStatus
FROM .......etc.

Any help regarding this will be appreciated very much.
Thanks
Arch

.

 

[Guest]

Thank You so very much for your help. It worked. Many
querys were based on this and now I can continue with my
work. Thank You.
Arch

-----Original Message-----
My bad, try:
CREATE VIEW v_ProjectsAwarded
AS
SELECT Mun_Code_Name.County, 'Project Awarded ' + Convert (varchar(11),
Award.AwardDate) + '. ' AS Remarks2, Award.CloseOutStatus
FROM .......etc

This worked for me and assumes AwardDate is a date field.

--
Duane Hookom
MS Access MVP
--

Thank You very much for the reply. I tried it but now I
have the following error:
Invalid operator for data type. Operator equals boolean
AND, type equals varchar.
Appreciate your help.
Arch

-----Original Message-----
I would never create a view, table, stored

procedure,...
with spaces in the

name.

CREATE VIEW v_ProjectsAwarded
AS
SELECT Mun_Code_Name.County, 'Project Awarded ' &

Convert
(varchar(11),

Award.AwardDate) + '. ' AS Remarks2, Award.CloseOutStatus
FROM .......etc

You may want check Books On Line regarding date formats in Convert().

--
Duane Hookom
MS Access MVP
--

The following code is from Access and I am migrating all
the queries to SQL server. Please help me convert this
String Concatenation along with a column name of a table
to SQL server. Once I transferred the query into the SQL
Query Analyzer, I tried replacing a dot for ! mark and +
sign for &. But it still comes up with an error. It looks
for a column name called Project Awarded and says 'invalid
column name'. It does not read the string as text.

CREATE VIEW [Projects Awarded] AS
SELECT Mun_Code_Name.County, "Project Awarded " & [Award]!
[AwardDate] & ". " AS Remarks2, Award.CloseOutStatus
FROM .......etc.

Any help regarding this will be appreciated very much.
Thanks
Arch



.

.