Access:To chk the parameters of query and then run it

[atksamy]

Hi,

I am not sure how clear i can be in my question.
I have a table full of queries which i am running agaianst another table full of records.

consider i have a query in table like select * from table where

f < (a+b)/d

here suppose value of d is 0 then an error will occur i want to modify it such that if d = 0 then control must move on to next method.
is there any way other than error trapping. i mean by modifying the query

thanks

[rsmccli]

Kinda fuzzy on some details there. If 'd' is a field from a query, then you could just filter those that =0 with a WHERE clause.

'I'm running a bunch of queries' or whatnot is hard to decipher. Are you running these queries from code or how is this all working? It is probably possible to do some sort of If...Then...Else or Select Case statement in the code to skip invalid records if using a WHERE clause is not viable.

[atksamy]

well d is a field from a table.

the code has the following statement

select * from table where


the remainig part of the statement are in a table which is executed in the form of a loop.
one of the condition in the table is f<(a+b)/d

which is returning erro because d might return 0 in some records
i cant change code as ther are other conditions which might get affected and then code will not be generic, as d may not be a part of all tables

i want to make change in this condition f<(a+b)/d

[rsmccli]

@atksamy
Dunno. I guess I suggest that you base your loop query to select from a query that has already filtered out any records where d = 0.

So more like:

SELECT * FROM (SELECT * FROM table WHERE d <> 0) AS Temp WHERE



or something of that nature.

Good luck to you.

[atksamy]

Hi,

This question is a better explanation for the thread " To chk the parameters of the query and run it"

I am using the following code to run a table of queries against a few tables of records.
I have a table query criteria which has a few conditions and i am trying to get each query to run against the tables.

The code works fine till it comes to a condition like f<((a+b)/d)
as here certain records may return 0 fro d and hence error occurs. Can anyone show me a way to get through this

Option Explicit
 
Sub plausibilitaet_check()
 
Dim rs As DAO.Recordset
Dim rs2 As ADODB.Recordset
Dim db As database
Dim strsql As String
Dim tdf As TableDef
 
Set db = opendatabase("C:\Codebook.mdb")
Set rs = db.OpenRecordset("Query criteria")
 
Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection
 
For Each tdf In CurrentDb.TableDefs
 
   If Left(tdf.Name, 4) <> "MSys" Then
        rs.MoveFirst
        strsql = "SELECT * From [" & tdf.Name & "] WHERE "
 
        Do While Not rs.EOF
            On Error Resume Next
 
            rs2.Open strsql & " " & rs![query]
 
            If Err.Number = 0 Then
                On Error GoTo 0
                Do While Not rs2.EOF
                If Not rs2.EOF Then                            
    msgbox "error"                            
 
                End If
 
            rs2.MoveNext
            Loop
            End If
            Err.Clear
            rs2.Close
            rs.MoveNext
 
        Loop
    End If
Next
 
End Sub

[ChipR]

I'm wondering if you could change that query to d<>0 AND f<((a+b)/d)
but I couldn't find anything about whether SQL would try to evaluate both of these if the first failed, so it may or may not work.

[atksamy]

i already tried that, i guess its evaluating both as it still throws up an error

[ChipR]

Ok, what if your condition is d<>0, f<((a+b)/d
Then you search your condition for a comma, and if there is one you construct a
SELECT * FROM (SELECT * FROM tdf.Name WHERE d<>0) WHERE f<((a+d)/d)
otherwise you just construct the string without the subquery.

[missinglinq]

atksamy, your duplicate posting of this question/problem has been merged with the current one! Please refrain from this prohibited behavior in the future.

From FAQs

Do Not Double Post Your Questions

Double posting is where you start a thread on a topic and then for some reason start another thread on exactly the same topic in the same forum. Please do not do this because

1. It makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.

2. It swamps the forum with your problem resulting in less attention for the other threads.

If you feel for some reason that you post has been overlooked (for instance it hasn't had any replies) please do not repost the question. Post a message to the thread you started, this will bump it back to the top of the thread list for the forum.

In point of fact, rsmccli gave you the same answer two days ago as Chip gave you today!

Thank you for your attention in this matter.


Linq ;0)>

Moderator

[atksamy]

Sorry about that I ll take care of that next time

[atksamy]

I have found a solution, just in case if anyone else is interested

f<IIf(d=0, 0, ((a+b)/d))