Queries 2007 vs 2003

[scubasteve.biz]

Can someone help me understand what has changed from 2003 to 2007? I have
converted a database and any query with a JOIN will not open in Design View.
I went back to 2003 and got the SQL Statement and input it into 2007 in a new
query. I get the same error. Yet there is no problem running the query in
2007. The SQL is below: (The Error when trying to look in DESIGN VIEW is: "
is not a valid name. Make sure it does not contain invalid characters or
punctuation and that it is not too long.)

INSERT INTO tbl_manufacturers (mfg_name_cwr, mfg_name_web, date_added)
SELECT tbl_import_product_download_cwr.mfg, StrConv([mfg],3) AS mfg_web,
Date() AS Today
FROM tbl_import_product_download_cwr LEFT JOIN tbl_manufacturers ON
tbl_import_product_download_cwr.mfg=tbl_manufacturers.mfg_name_cwr
WHERE (((tbl_manufacturers.mfg_name_cwr) Is Null))
GROUP BY tbl_import_product_download_cwr.mfg;

 

[Jeanette Cunningham]

Hi scubasteve,

There is something unusual with the date_added field.
-->In the insert into statement the field is called date_added.
-->In the Select statement you are using Date() As Today.

If date_added is a date time field, I would expect the Select statement to
have
Date() As date_added instead of Date() As Today.

A2007 is less tolerant of incorrect names that earlier versions.
I seem to remember that I use Date and not Date() when doing an insert into.

You could try using Date instead of Date().

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Dale Fye]

Steve,

Why do you have the GROUP BY clause in the SELECT statement? You are not
doing any aggregation, so the first place I would start is to remove that
part of the query.

Have you checked your references to make sure you don't have any missing
references? Since you are calling both the StrConv() and Date() functions,
that is another place to check.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Ron2006]

I haven't got access to 2007 just yet.....


But is it possible that "Today" is a key word.

And I know that Date() is.

If true then you are trying to "redefine"? one key word with another.

Might be worth checking.

Ron

 

[Dale Fye]

But to your original issue:

"and any query with a JOIN will not open in Design View"

I've not seen this problem. Do you mean that you actually converted a 2003
(.mdb) file to a 2007 (.accdb) file?

 

[Byron]

I have the same issue as below. My JOIN query will not open in design view,
but it runs fine. This was not an issue in 2003, only in 2007. The problem
seems to be with a linked table that resides in a folder with an apostrophe,
e.g. F:\Dean's Office\MyDatabase.accdb.

I've tried refreshing links, compacting, etc. Is there a solution, besides
relocating my database to a better named folder?
--
Thanks,
Byron

"and any query with a JOIN will not open in Design View"

I've not seen this problem. Do you mean that you actually converted a 2003
(.mdb) file to a 2007 (.accdb) file?

Dale

:

Can someone help me understand what has changed from 2003 to 2007? I have
converted a database and any query with a JOIN will not open in Design View.

 

[Douglas J. Steele]

Assuming that by "design view" you mean the graphical interface (with the
tables at the top and a grid at the bottom), that's correct. UNION queries
will not open in design view. They'll only open in SQL view. It's been this
way in all versions of Access.

 

[David W. Fenton]

UNION queries
will not open in design view. They'll only open in SQL view. It's
been this way in all versions of Access.

....along with DDL and passthrough queries.