Access2010 & sqle 3.5

I'm trying to learn while doing, please humor me.

I have a SQLE 3.5 database. Presently I go through a process of exporting the tables into .xlsx format, link to the tables in Access 2010 and generate the results. Access 2010 does not have an OBDC driver for SQLE 3.5 built in. I have not yet succeeded in getting the MS Server & Visual Studio things to work. Consequently, I'm using another after market product to export the data to xlxs format.

In so much as Access utilizes SQL in the background, I thought I would be clever and cut & past the Access generated SQL query and use that to create the results. The Access generated SQL is reflected below.

SELECT PROPERTY.Code, PROPERTY.Name, BUILDING.Name, UNIT.Name, PROPERTY.LastSynchronizedOn,
PROPERTY.SerialNumberType, METER.CountFactor, TRANSMITTER.SerialNumber,
IIf([SERIALNUMBERTYPE]="2",([SERIALNUMBER]+2684354560),IIf([SERIALNUMBERTYPE]="3",([SERIALNU
MBER]+2688876544),"OOOPS")) AS TXIDVAL, CStr([TXIDVAL]) AS TXID, CHECKINSTATE.Received
FROM PROPERTY INNER JOIN ((((BUILDING INNER JOIN UNIT ON BUILDING.ID = UNIT.BuildingID) INNER
JOIN METER ON UNIT.ID = METER.UnitID) INNER JOIN TRANSMITTER ON METER.ID =
TRANSMITTER.MeterID) INNER JOIN CHECKINSTATE ON TRANSMITTER.ID = CHECKINSTATE.TransmitterID)
ON PROPERTY.ID = BUILDING.PropertyID
ORDER BY PROPERTY.Name, BUILDING.Name, UNIT.Name;

When I execute the query, this is the error message I receive.

[SQL Server Compact ADO.NET Data Provider]
HResult: -2147217900, NativeError: 25501
ErrorMessage: There was an error parsing the query. [ Token line number = 3,Token line offset = 23,Token in error = = ]

My research seems to indicate there is some sort of translation error from what ever Access is using as SQL & the 3.5 version of the data base. Trying to find a document which outlines 3.5 compact SQL syntax provides a variety of links which do not exist any longer.

Again, I ask for your understanding and patience as I just started down this SQL path a few days ago.:banghead:

your error message is telling you where the error is - line 3, 23 characters in and an = sign.

My guess is it is the iif which is cause the problem. iif is a vba function and although available in transact sql from version 2012 it is unlikely to be in sqle. so you need to change it to the sqle equivalent.

I'm not familiar with sqle so cannot advise, but in sql you can also use the case function. here is a link https://www.w3schools.com/sql/func_mysql_case.asp.

CJ, appreciate the feedback.
As you indicated, I need to find the sqle equivalent.... which has been somewhat challenging.
The applications generating the data I'm trying to get at were developed years ago.

Is [SERIALNUMBERTYPE] a number field type or a text field type?
Then you're comparing it with a text type [SERIALNUMBERTYPE]="2"

JHB said:

Is [SERIALNUMBERTYPE] a number field type or a text field type?
Then you're comparing it with a text type [SERIALNUMBERTYPE]="2"

Click to expand...

Good point
If it's a number then it should be [SERIALNUMBERTYPE]=2.

In which case, it will then error at [SERIALNUMBERTYPE]="3"
Change both and it may be solved

The sums in the IIf expression certainly suggests it is a number

Gentlemen,

Appreciate your help. SerialNumberType is a string value & not a numeric value (it's text not a number).

The query works in Access 2010 & produces the desired results. I was trying to find a 'quick & easy' way to generate the query in SQL CE 3.5. I was hoping I could cut and paste the Access generated SQL syntax into SQL CE 3.5 & run the query there. NOT! Trying to find information on commands for SQL CE 3.5 has been an exercise in frustration. Any link I have found, seems to no longer exist. The product is no longer supported & it appears any links Microsoft had on line, have been removed. Microsoft, in all its infinite wisdom, has also removed the ODBC driver from Access.

As CJ pointed out, the issue appears to be associated with SQL CE 3.5 not supporting the IIF statement (there are probably others too).

What would really help at this point in time would be a viable link to the SQL CE 3.5 commands. Due to my 'newness' to SQL, I have created a convoluted way of extracting the data I need in Access.

Again, thanks for your input.

lemondrop9344,

If you're still around look up the documentation for the Case statement.

hth,
Wayne

Also, I am not sure with SQLE, MS-SQL does like using double quotes for strings, instead use single quotes. The double quotes can be used for aliasing column names (i.e., "First Name") to overcome normal naming rules.