Too many functions in a query?

Hi, everybody!

I'm trying to use Functions like MAX, IIF and DateADD in my SQL statment. is it possible?

Set MyDb = OpenDatabase("MyFile.mdb")
MySqlString = "SELECT Calibrations.RealDate, Max(iif([Unit]=1;DateAdd(""d"";[Value];[Date]);iif([Unit]=2;DateAdd(""m"";[Value];[Date]);iif([Unit]=3;DateAdd(""m"";[Value]*12;[Date]);0)))) AS Próx, and so on..

Set mystet = MyDb.OpenRecordset(MySqlString)
I'm receiving an error message:
3075 Syntax error in query expression.

Have any of you used a query which such functions before? Is there anything you can see that's wrong just by taking a look at my SQL?

Thanks in advance for any help,
Roselene

I'm not sure what a 3075 error is but you could find out by using an error handler as follows ;at the beginning of your sub type

On error goto ErrorHandler

'and at the end type

exit sub
ErrorHandler:
msgbox Err.Description
end sub

From a quick glance at your SQL, depending on what type you are using I would suggest using a CASE statement instead of the iif.

Quote:

---

I'm trying to use Functions like MAX, IIF and DateADD in my SQL statment. is it possible?

---

Yes, using functions in SQL works beacuse the following works for me.

Set ssDefaults = gdbRWTS.CreateSnapshot("Select * From Defaults where Description like '" & IIf(y > x, "o", "f") & "*'")

Quote:

---

Is there anything you can see that's wrong just by taking a look at my SQL?

---

Yes, it's MUCH too complicated. Even if it's syntactically correct (which I assume it isn't), it's a maintenance nightmare. I would do most anything in the program to avoid something that complex even if it meant returning a larger set of records than necessary and examining each record to determine if it's one you really want.

Can you describe exactly what your SQL is trying to do?

------------------
Marty
What did the fish say when it hit the concrete wall?
> > > > > "Dam!"

[This message has been edited by MartinLiss (edited 02-17-2000).]

Hi, folks.

To Bigley and Martin Liss:
--------------------------

Thanks very much for taking the time to help me, I really appreciate that.

To Bigley
----------
I don't know how to use a CASE statmente in a SQL. Could you please send me an example? Thanks.

To Martin Liss
--------------
You're right. It looks like a nightmare :-)
I have already taken a calc from the SQL Statment and put them in code in order to simplify it, but there's still very complicated things left, no?

I could get it to work, and now it looks like this:
MySqlString = "SELECT Max(iif([Unit]=1,DateAdd(""d"",[Value],[RealDate]),iif([Unit]=2,DateAdd(""m"",[Value],[RealDate]),iif([Unit]=3,DateAdd(""m"",[Value]*12,[RealDate]),0)))) AS ExpectedDate, last(Equipment.TAG) FROM CalibrationPeriodicity INNER JOIN (Equipment INNER JOIN Calibration ON Equipment.COD = Calibration.EquipId) ON CalibrationPeriodicity.Counter = Equipment.Periodicity Where (Equipment.Tag) = '" & DtCmbTAG.Text & "'" Wow! :-O
Set Myset = MyDb.OpenRecordset(MySqlString)

What I'm trying to do is:
The user enter data about a calibration of piece of Equipment X. (which is in DTCmbTAG combobox)
I have to look for X in the CalibrationPeriodicity Table and add this periodicity in order to determine the next calibration expectedDate. So, if unit =1, that means day, I have do add the number of days which are in the field value to the date the user entered today (RealDate). Unit 2 = months and unit 3 = year.
I also have to antecipate the ExpectedDate to Friday, in case the result of the SQL returns a date which is saturday or sunday, but, at least, I could took this calc from the SQL and put it in the code. :-)

I could get this date by using the SQL above, but,
I'm having problems ....

To everybody
------------------
I'm trying then to add a new record to the ADO Control which feeds my DataGrid, like this:

AdodcDataGrid.Recordset.Fields(0) = NewNumber ‘table primarykey=the last table autonumber counter plus 1
AdodcDataGrid.Recordset.Fields(1) = DtCmbTAG.Text
AdodcDataGrid.Recordset.Fields(2) = ExpectedDate
AdodcDataGrid.Recordset.Update

But it isn't working. Vb is displaying a message box:
-2147217887 Errors

Any ideas or suggestions?

Thanks in advance for all of your help, folks :-)
Roselene

Hi Roselene,

here's something I found in MSDN about it:

BUG: Connection Failure with ADO 2.1 and the Jet 3.51 Provider
ID: Q229741



--------------------------------------------------------------------------------
The information in this article applies to:

ActiveX Data Objects (ADO), version 2.1
Microsoft OLE DB Provider for Jet, version 3.51

--------------------------------------------------------------------------------


SYMPTOMS
If you try to set read, write, or read/write access permissions using the ADO mode property within your connection string, you get the error:

Run-time error: '-2147217887' "Errors Occurred"
However, if you use the Microsoft Data Link or the Data Environment designer to set these permissions, you get the following error:
Test connection failed because of an error in
initializing provider. 0x80040e21

RESOLUTION
Remove the "Share Deny None" mode option from your ADO connection string.

If you are using the Microsoft Data Link or the Data Environment designer, you can deselect the "Share Deny None" option from the Advanced tab.

-or-

Use the Jet 4.0 OLEDB Provider instead of the Jet 3.51 OLEDB Provider.

STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior
Right-click on your Desktop, choose New/Microsoft Data Link.


Fill in the appropriate information to connect to your Access Database via the Microsoft Jet 3.51 OLEDB Provider.


Go to the Advanced tab. Select read, write, or read/write permission.


Test your connection. You should get the error.

REFERENCES
For more information, please refer to the ADO documentation.


© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ammar Abuthuraya, Microsoft Corporation


I didn't test it, but maybe it helps.
Roger