Expected: End of Statement

[boliches64]

Hi All
I am getting the "Expected: End of Statement" message from my code:

strSQL = strSQL & "DLookUp("[ID]", "qryListCopiesToDelete", "[ID]" >1)))"

"[ID]" gets highlighted as an error! If I remove the quotation marks "qryListCopiesToDelete" becomes the problem etc. When I remove all quotes from the above script the warnings go but the script fails to work! How do I get the DLookup to read the query?

All queries were built in Query Design and work as plain queries, but when I place the code into vb - NOTHING!

Help please, I have spent ages on the web but to no avail.

[Stewart Ross]

Hi. Two problems with your statement: the first being that to include double quotes within a string you need to repeat the double quote character (it is after all a delimiter in VBA, and the VBA interpreter is seeing the end of your string as occurring just before your reference to the ID in your DLookup). The second error, which I also saw in a similar post you made earlier on this topic, is that your comparison statement is incorrect - the > 1 part is outside of the comparison.

Sorting these errors out, your StrSQL setup for the DLookup becomes:

strSQL = strSQL & " DLookUp(""[ID]"", ""qryListCopiesToDelete"", ""[ID]>1"")))"

which becomes

DLookUp("[ID]", "qryListCopiesToDelete", "[ID] >1")))

when run.

I am not convinced that this will do what you expect, however, as it is returning one ID from the set of all matching rows where the ID is > 1. I doubt very much that the one returned will in all cases be the one you need, but that is for you to sort out.

-Stewart

[NeoPa]

In any case, quotes within SQL should be single-quotes rather than double-quotes (technically speaking - see Quotes (') and Double-Quotes (") - Where and When to use them). This would avoid the problem altogether (although not fix your logic - See Stewart's post).

[boliches64]

Thanks Stewart (and NeoPa), the error message has gone away!! BUT as you predicted the script does not do want I want it to.

If you could help please!

I have a query which selects all duplicates from a table namely "qryListFirsts", (works fine).
I then created (in Query Design) another query which looks at the "qryListFirsts" and original Table to show all duplicate records - "qryListCopiesToDelete" (works fine).
My final query "qryDeleteDuplicates" whose SQL is:

DELETE tblMoneyDueBP.*, tblMoneyDueBP.ID
FROM tblMoneyDueBP
WHERE (((tblMoneyDueBP.ID)=DLookUp("ID","qryListCopiesToDelete","ID >1")));

is the script I am attempting to use in Visual Basic.
All work as they should when I run "qryDeleteDuplicates" via the Queries pane (albeit 1 record at a time gets deleted!!)

Therefore my problem remains as to why the above code fails to activate the relevant queries and achieve my goal when run within the VB area.

I hope this makes sense and that you may be able to assist.

Kind regards

[NeoPa]

Thanks Stewart (and NeoPa), the error message has gone away!! BUT as you predicted the script does not do want I want it to.

If you could help please!

If you want help with this you will need to specify what you need. What is the latest version that you're trying? How is it failing?

I have a query which selects all duplicates from a table namely "qryListFirsts", (works fine).
I then created (in Query Design) another query which looks at the "qryListFirsts" and original Table to show all duplicate records - "qryListCopiesToDelete" (works fine).
My final query "qryDeleteDuplicates" whose SQL is:

DELETE tblMoneyDueBP.*, tblMoneyDueBP.ID
FROM tblMoneyDueBP
WHERE (((tblMoneyDueBP.ID)=DLookUp("ID","qryListCopiesToDelete","ID >1")));

is the script I am attempting to use in Visual Basic.
All work as they should when I run "qryDeleteDuplicates" via the Queries pane (albeit 1 record at a time gets deleted!!)

Therefore my problem remains as to why the above code fails to activate the relevant queries and achieve my goal when run within the VB area.

It does one at a time (only) because DLookup() is a Domain Aggregate function that returns only a single item.

Try instead :

DELETE tblMoneyDueBP.*
 
FROM tblMoneyDueBP INNER JOIN qryListCopiesToDelete
  ON tblMoneyDueBP.ID=qryListCopiesToDelete.ID
 
WHERE qryListCopiesToDelete.ID>1

[boliches64]

Thanks again for your time, much appreciated. I can see why your idea is much better, but still I cannot get it to work. Do I need to somehow run the queries in VB to get this script to function? Its as if the "qryListCopiesToDelete" is not being actioned. I dont know if this makes any sense, but I am starting to go mad!!

The only reason I need this in VB is to be able to point to varying tables depending on user ie tblInvoiceAB (for user AB) tblInvoiceBP (user BP) etc. As mentioned previously all worked well in Query Design.

[NeoPa]

Two points here :

1. Sometimes Action queries will only work when the underlying query is what is known as updatable. Your [qryListCopiesToDelete] query appears to be updatable, as you can delete items from it successfully. That is not to say that any action query based upon it must necessarily be so also (See Reasons for a Query to be Non-Updatable).
   
   When I find myself in that position, I often organise things in such a way as to update the table that I want to delete from. Usually I use a field in the table and set it to an otherwise not found value. Typically the string "DeleteMe", but anything can work, even numeric fields can be used if done properly. A very simple DELETE query can then follow on, deleting all records with the specially created value ("DeleteMe").
2. Having separate tables for each entity who has invoices would not be a recommended approach. Check out Normalisation and Table structures for the full explanation of that. It may also help you resolve the other problem incidentally. A better approach generally, would be to have a single invoice table (tblInvoice for example) which contained a field that identified who the invoice was for. There are many benefits to this, some of which you will find in the linked article, and some of which you will become aware of as your experience leads you to different areas of the subject.

[boliches64]

Thanks, cant wait to try it!! Your 1st idea would (thinking about it) work perfectly as it gives the table a value to search against, and therefore a unique record to find and delete.

Re your 2nd thought: I will look at this with interest, as going forward, it makes perfect sense to tie more areas together in diferent tables. However in my particular scenario I believe I should have individual tables to keep data secure from other users. (or would your suggestion be able to achieve this v. important criteria?)

[NeoPa]

Well, you would generally choose to show only the data for a certain selected customer at any given time.

I doubt you'd want the customers to have direct access to the table data.

[Bugran]

Hello,

I'm not totally certain to your purpose of using SQL to accept a DLOOKUP. However, maybe the following will help you by bypassing the SQL statment and directly attributing a value to a variable from the DLOOKUP.

MYVARIABLE = DLookup("[ID]", "qryListCopiesToDelete", "[ID] >1")

Hope that helps you.
Bugran

[boliches64]

Thanks Neopa solution 1 fixed the problem. I entered another field (Delete) into the table that updates with a "Yes", this gives me a referennce to delete all records against. ie If Delete field = Yes then delete. So simple its genius.

Brilliant again!! Thanks.

Re your last post I think I have misled you! My database is designed for multi users, for each user to have access only to his \ her records within the database. I thought the simplest route was to have individual tables for each user, rather than one huge table for all. My thought process being (rightly or wrongly) that the smaller the tables content the faster queries etc will run, whilst protecting the data from other users prying eyes.

[NeoPa]

No worries. Glad I could help.

On the matter of multiple tables versus a single table with selection, it is actually possible to be in such a position as you describe, though generally shouldn't be an issue (does depend on certain settings and choices you may have made).

The downsides of your approach include extra complication of development etc.

At the end of the day it can still work that way if you prefer to do that, but I think it unlikely to be necessary. I don't know all the details of your database of course so that comes with no guarantees.