Error 7874 in a SQL string

[Bakta]

Hello here is the code that bothers me :

dim strSQL as string
LReftst = tst_idx (InRef) ' this is a global public function which returns a long
if LReftst <> 0 then
     strSQL = " SELECT tbl_ctc_evt.[Idx Ctc], tbl_ctc_evt.[Idx evt] FROM tbl_ctc_evt WHERE tbl_ctc_evt.[Idx Ctc] = '" & LReftst & "' AND tbl_ctc_evt.[Idx evt]= '" & LNumevt & "';"
     DomCmd.RunSQL strSQL
end if

' where LNumevt is a global variable tested beforehand. Both LRefttst and LNumevt are global variable and both declared in a GenVal module

When I run this code I get an error messagebox N° 7874 stating impossible to find object 'SELECT....' where both global variable are in value.

Should I use info from Pat's Gen info I get the same kind of error.

My purpose is to see if a query with both global variables used as filter matches any record in the table.

Im starting to think Im not doing this properly but can't think of a better way at the moment given my lack of SQL skill.

Any pointers appreciated.

 

[Mile-O]

If you are going to use those awful underscores in your field/table names then, in Access, you need to enclose them in square brackets.

strSQL = "SELECT [tbl_ctc_evt].[Idx Ctc], [tbl_ctc_evt].[Idx evt] FROM [tbl_ctc_evt] WHERE [tbl_ctc_evt].[Idx Ctc] = """ & LReftst & """ AND [tbl_ctc_evt].[Idx evt]= """ & LNumevt & """;"

 

[Bakta]

Thank you very much for the prompt reply.

Point duly taken, I'll never ever ever again use those underscore (working on an old base, naming conventions or lack of are not mine to start with, but nonetheless).

Alas, the duck is still alive; still got an 7874 error after puting brackets were they're due.

 

[Mile-O]

OKay, now that you've done that. The real prooblem.

The RunSQL method is only for Action queries and can't be used with a Select query.

What, exactly, are you trying to do? And where's the rest of the code? You've used variables that, as far as I can see, have not been dimensioned.

 

[Bakta]

tbl_ctc_evt is made of
Idx_ctcevt : Long
Idx ctc : Long
Idx evt : Long
and other fields

Both Lnumevt and LReftst are declared, in a Gen Val module, as public var both Long.

General purpose :
I'm trying to check the existence of any record in table tbl_ctc_evt where both [Idx ctc] matches LReftst and [Idx evt] matches Lnumevt.

Any help appreciated.

 

[Mile-O]

Would a simple DCount not suffice?

 

[Bakta]

I feel ... so ...dumb

Brains....Need more brains