By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

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

atksamy
P: 91
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
Nov 26 '08 #1
Share this Question
Share on Google+
10 Replies


rsmccli
P: 52
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.
Nov 26 '08 #2

atksamy
P: 91
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
Nov 26 '08 #3

rsmccli
P: 52
@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.
Nov 26 '08 #4

atksamy
P: 91
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

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub plausibilitaet_check()
  4.  
  5. Dim rs As DAO.Recordset
  6. Dim rs2 As ADODB.Recordset
  7. Dim db As database
  8. Dim strsql As String
  9. Dim tdf As TableDef
  10.  
  11. Set db = opendatabase("C:\Codebook.mdb")
  12. Set rs = db.OpenRecordset("Query criteria")
  13.  
  14. Set rs2 = CreateObject("ADODB.Recordset")
  15. rs2.ActiveConnection = CurrentProject.Connection
  16.  
  17. For Each tdf In CurrentDb.TableDefs
  18.  
  19.    If Left(tdf.Name, 4) <> "MSys" Then
  20.         rs.MoveFirst
  21.         strsql = "SELECT * From [" & tdf.Name & "] WHERE "
  22.  
  23.         Do While Not rs.EOF
  24.             On Error Resume Next
  25.  
  26.             rs2.Open strsql & " " & rs![query]
  27.  
  28.             If Err.Number = 0 Then
  29.                 On Error GoTo 0
  30.                 Do While Not rs2.EOF
  31.                 If Not rs2.EOF Then                            
  32.     msgbox "error"                            
  33.  
  34.                 End If
  35.  
  36.             rs2.MoveNext
  37.             Loop
  38.             End If
  39.             Err.Clear
  40.             rs2.Close
  41.             rs.MoveNext
  42.  
  43.         Loop
  44.     End If
  45. Next
  46.  
  47. End Sub
Nov 28 '08 #5

Expert 100+
P: 1,287
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.
Nov 28 '08 #6

atksamy
P: 91
i already tried that, i guess its evaluating both as it still throws up an error
Nov 28 '08 #7

Expert 100+
P: 1,287
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.
Nov 28 '08 #8

missinglinq
Expert 2.5K+
P: 3,532
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

Nov 28 '08 #9

atksamy
P: 91
Sorry about that I ll take care of that next time
Nov 28 '08 #10

atksamy
P: 91
I have found a solution, just in case if anyone else is interested

f<IIf(d=0, 0, ((a+b)/d))
Dec 1 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.