Problem with sql string, wrongly use of "'","!"... (1 Viewer)

[Mgomp]

Hello all.

I am really stuck in a case which I think is easy for "fresh" eyes to se what I am doing wrong.

I have a simple (!) opening of a table with the folowing statment:
rsCable.Open "SELECT * FROM [Cable] WHERE [Cable!BundleID] = " & midBundle

The error message (in norweigian) can be translated to something like:
Wrong use of ".", "!" or "0" in expression [Cable!BundleID] = Crane.0

The correct value in midBundle should be Crane.01

I have tried almost all kinds of use of ", ', () and I only get different kinds of error messages. I have used the above statement her, because this is the same type of statment used in Access Bible 2010, page 509...

I do also put the , , adOpenKeyset, adLockOptimistic after the midBundle in the statement.

So, what am I doing wrong?

Regards.

 

[rudeboymcc]

sql doesn't understand bangs the way access does. To reference the field ID on "table" it's table.ID, not table!ID.

Try changing "WHERE [Cable!BundleID] " to "Where Cable.BundleID".

(square brackets aren't needed, they are normally used if there is a gap in the field name or table name, such as Cable.[Bundle ID])

 

[Mgomp]

Thanks for the reply

I did the change and removed the square brackets, but I still get the same error messages...

 

[gemma-the-husky]

try a recordset

dim db as database
dim rst as recordset

set db=currentdb
set rst= db.openrecordset ("SELECT * FROM [Cable] WHERE [Cable!BundleID] = " & midBundle)

not sure whether it is ot or bang offhand, tbh.

 

[rudeboymcc]

just noticed that you are searching for a Bundle ID equal to "Crane.0". Is this right? That means your bundle ID is a string. so you should try:

rsCable.Open "SELECT * FROM [Cable] WHERE [Cable!BundleID] = '" & midBundle & "'"

Notice the apostrophes (one before " & midBundle and the one in the double quotes at the end). When printed out this will look like:

WHERE [Cable!BundleID] = 'Crane.0'

 

[Galaxiom]

[Cable!BundleID] = Crane.0 will never work for the three reasons given in the error.

Firstly the square brackets are object delimiters and the misuse of the bang message is because it is an illegal character in what Access/VBA sees as an object name.

Crane.0 is also seen as an object called Crane with a property, control or field called "0". An object name cannot contain a dot and must start with an alpha character.

[Cable]![BundleID]

The bang will work fine in this context.

However it is usually best to use the dot almost everywhere. Access transparently and automatically substitutes a bang for a dot if necessary in most situations but not vice versa. Moreover IntelliSense in VBA only works with dots.

 

[Mgomp]

Hello All!

I am very sory, I may have misled You all!

First, using "sql-string" = '" & midbundle "'" works perfectly. I was mixing error messages due to another error i have found and fixed:

I am using two differen sets og tables;
Dim rsTable1 As ADODB.recordset
Dim rsTable2 As ADODB.recordset ... and so on.

In the program I have a Do Until loop, using the rsTable1. Inside of this loop, I use rsTable2.open "sql-string" = '" & midbundle "'",, and so on.
I find that I have to set up the rsTable2 inside of the first Do Until Loop, not in the beginning of the procedure. i dont know why, but after moving all the defs for rsTable2, everything works fine.

Again, sorry for my misled - but thanks for all good hints!

Regards.