lookup syntax question

[smile]

Hi, I have created some lookups and for one access made syntax like this

SELECT [tbl_bpo].[ID], [tbl_bpo].[Banko pajamu orderis Nr:] FROM tbl_bpo ORDER BY [ID] DESC;

For another

SELECT tbl_kpo.ID, tbl_kpo.[Kasos pajamu orderis Nr:], tbl_kpo.[ID] FROM tbl_kpo ORDER BY tbl_kpo.[ID] DESC;

Both work, should I reformat it to:

SELECT [tbl_kpo].[ID], [tbl_kpo].[Kasos pajamu orderis Nr:] FROM tbl_kpo ORDER BY [ID] DESC;

The trick is: My fist lookup was created to sort data by ID and then I decided to fix the second lookup by editing in design view. IT was made to sort by same field [Kasos pajamu orderis Nr:]. Made everything like the first one but syntax is different.

 

[smile]

Thanks for reply. It seems access generates code like

line 1

SELECT [tbl_bpo].[ID], [tbl_bpo].[Banko pajamu orderis Nr:] FROM tbl_bpo ORDER BY [ID] DESC;

if you just create lookup, then if you like me created first to sort by same field you use in lookup itself and another to sort by ID. The lookup sorting by id in design view is different than that sorting by field itself.

The one sorting by ID has another [ID], tbl_bpo is set as table and sort set to descending.

If you try to replicate this you will get syntax like this:

SELECT tbl_kpo.ID, tbl_kpo.[Kasos pajamu orderis Nr:], tbl_kpo.[ID] FROM tbl_kpo ORDER BY tbl_kpo.[ID] DESC;

So I edited my lookup to:

SELECT [tbl_kpo].[ID], [tbl_kpo].[Kasos pajamu orderis Nr:] FROM tbl_kpo ORDER BY [ID] DESC;

 

[smile]

BTW not sure why sometimes access gets this:

SELECT [tbl_kpo].[ID], [tbl_kpo].[Kasos pajamu orderis Nr:] FROM tbl_kpo ORDER BY [ID] DESC;

sometimes:

SELECT [tbl_kpo].ID, [tbl_kpo].[Kasos pajamu orderis Nr:] FROM tbl_kpo ORDER BY [ID] DESC;

I think I should include the brackets?


I read somewhere:
Including table name helps if you try to rename tables access 2007 tracks your other database resources this way and if you are lucky your datbase will not brake. This is not recommended to do I mean rename anything if you use it in db.

 

[smile]

for example just now access generated this nonsense when you use visual editor you get this:

SELECT tbl_opkodas.ID, tbl_opkodas.opkodas, tbl_opkodas.aprasymas FROM tbl_opkodas ORDER BY tbl_opkodas.[opkodas];

I had to change it to

SELECT [tbl_opkodas].ID, [tbl_opkodas].[opkodas], [tbl_opkodas].[aprasymas] FROM tbl_opkodas ORDER BY [opkodas];

 

[smile]

I also mentioned that your "ID" field could be named better:

ID means nothing if seen on its own, however if you name it like this:
opkodasID

you now know that the ID relates to the table opkodas.

Thanks, can I rename them without impact on my database like integrity and that all queries and reports will still work?

 

[smile]

I still did not rename my fields, waiting for a reply?

 

[georgedwilkinson]

It is unclear what your problem is. So what if QBE puts brackets around your object names? How is that keeping your system from working?

The best way to fix that is to name your objects appropriately. If you don't want to do that, you'll have to live with the brackets.

 

[smile]

It is unclear what your problem is. So what if QBE puts brackets around your object names? How is that keeping your system from working?

The best way to fix that is to name your objects appropriately. If you don't want to do that, you'll have to live with the brackets.

The problem is that access names them differently than it did before, naming is done in unfriendly fashion - it's harder to read them then proper naming done before.

I suspect Service pack 1 for office 2007 .

BTW you did not answer is it safe to rename my ID fields?

 

[georgedwilkinson]

Safe? Probably (within limits). Necessary? No.

You need to rename your fields with the spaces and special characters: therein lies the only problem I see.

 

[Brianwarnock]

If the Db I downloaded is anything to go by renaming will be a hard and tedious task frull of potential errors, but Tony's comment is cerainly one for the future, and if time allowed I would take a copy of the DB and do it now.

Brian

 

[Brianwarnock]

I don't know whether the tool pointed to here would help with renaming as I haven't followed the link, but it might be worth a read.

Brian

 

[smile]

So I can use all A...Z characters and _ to form my database field and object names right?