Is 'EL' a reserved word? 1

[dRahme]

Hello, I am a little stuck.

I dim a number of abbreviations as strings. I update various fields in a table based on these dim's.

The routine works perfectly until I get to one that says 'where xyz = EL, which is a two character text code and then I get a data type mismatch.

There are seven simple docmd.run sql statements that are identical. The only difference is the two character code.

It works perfectly for every other code except 'EL'. EL is in the database, it is two character text. I checked the field length and 'EL' is only two characters.

If I go through and rename the EL abbreviation to ELE or something different, it works perfectly. Name it back to EL and it bombs.

So, I am kind of stuck. Is 'EL' a reserved word or something? I haven't been seen any documentation to that effect.

I told these folks this would be a six hour job and I just passed eight.

Is there a workaround? I cannot change the field name in the customer table and I need to update some values based on 'EL'

Thanks, dRahme.

 

[MajP]

Is this a field name? If it is put it in brackets. If you enclose a field name in brackets you can use spaces and reserved words as names of fields.
me![EL]

me![reservedword] works
but not
me.reservedWord

 

[dRahme]

No, it is not a field name - it is an item in a field. AR, EL, ME, CI are some of the characters in the field, which is set as text for two characters.

I can count the EL items in a query but running it as sql in a procedure it bombs. All other counts come through correctly with the exception of EL counts - where I get a data type mismatch.

Dont' understand it.

The bracket thing gives me an idea though.

Thanks, dRahme

 

[rjoubert]

If you're querying for the STRING EL, then you need to put single quotes around EL in your query.

WHERE xyz='EL'

 

[abaldwin]

How are you constructing the SQL statements. Please post the problematic SQL statment here, not just the where clause. Also you say you are building the variable EL and filling it from vba code.

Please post the code.

Thanks

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[MajP]

This would definately bomb then

xyz = EL
I think you mean
xyz = "EL" to assign a variable the string "EL"

or in some sql statement like said above "...where field = 'EL'

 

[abaldwin]

EL is a variable declared in VBA code. In his question he states that he DIMS them.

If so then the where clause should be constructed as

.....xyz = '" & EL & "'.......

That is why I asked for his code that constructs the SQL. He is not trying to set a field.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[rjoubert]

Actually, he says that EL is a two character text code. I believe he said he stores the various two letter text codes in variables.

 

[rjoubert]

Actually, I think dRahme should post his code and SQL so he can help to clear up our confusion.

 

[dRahme]

Hi, I decided to experiment and ran an update query on the field where there was an EL to EL.

Now the sql runs as it should, steps right through and populates the table.

So, it must be something about the way the field was originally popoulated.

Have to do some more testing and see if I can replicate the
problem.

The bracket suggestion got me thinking about the field data itself.

Thanks for the suggestion.

dRahme