Hex search problem

[skyshare]

I have an access database where one of the fields is named 'mod' and it contains string HEX codes such as AE093C, 00C394, 405FD0 etc.

I am trying to use a data control to do a 'findfirst' on this field but I keep getting the error 3077 'syntax error (missing operator) in expression'

i.e data1.recordset.findfirst("mod='405FD0'")

Other fields with non hex strings work fine using this method. I have also tried using recordsets and sql to search with the same result.

I presume that this is something to do with how the HEX codes are processed. Does anyone have an idea how I might bypass this problem please?

thanks

[BitBlt]

Shouldn't be any issue...it's just a string. Can you post the actual code thats having issues? What kind of data control are you using? What version of MSAccess? More details, please.

[AndreRet]

Firstly, why use a data control. See here why it is considered bad practise. :)

To solve your problem though, your FindFirst syntax should be like this -

data1.recordset.findfirst "mod = '405FD0'"

You will find ALL functions on the datacontrol here .

Happy coding...

[skyshare]

Thanks for the reply but that code is exactly the same as I am having the problem with.
It is just difficult to understand why the exact same code works on my other text fields but not that one and I just wondered if the fact that it was a hex code was causing the problem and maybe needing converting omehow to do a successful search.

[AndreRet]

Nope, a missing operator error refers to an incorrect select string. It is missing an ', ", = etc. which is why I took the () out of the equation. I do not use DAO (data access objects) because of this problems. Switch to ADO. You will then have a select query like -

rs.Open "SELECT * FROM YourTableNameHere WHERE mod = '" & txtMod.Text & "'",cn, AdOpenStatic, AdLockOptimistic

, or similar.

It has nothing to do with the value returned from your database, whether it is a hex value or not. Once you tell your app that it is a hex value, AFTER it showed up in your textbox, it will treat it as such.

I will have to try and recreate your scenario to test the the error, time is however not on my side at this moment, will post a solution a bit later on.;)

[ChrisPadgham]

hex is only a way of representing a number for human consumption, they are held in binary after all.

What is the data type that is holding the number, is it a text string or an integer. That will determine what your findfirst looks like. Text fields have quotes around them, integers do not.

[skyshare]

The field in the Access db is a string

I have tried using sql but the same error comes up...ie:

Dim db As Database, rc As Recordset
Set db = OpenDatabase("c:\databasename.mdb")
Set rc = db.OpenRecordset("select * from tablename where mod = '00B3C9'")

gives:
run time error 3075
syntax error (missing operator) in query expression 'mod = '00B3C9".

Interestingly the error text is different to the original code, probably connected?

[ChrisPadgham]

mod may be a reserved word try putting square brackets around it.

Set rc = db.OpenRecordset("select * from tablename where [mod] = '00B3C9'")

[skyshare]

That worked, obviously it is a reserved word.

Brilliant, many thanks for your help.