Using query results within Conditional Statement...sequenced conditional queries

R

rafael.farias.jr

I am fairly new to SQL and am trying to create a database that will
have a querying interface sitting on top of it.

I need the query to perform the first full statement (SELECT, FROM,
WHERE) and if the result is null, then I want the query to proceed to
the next full statement, etc... until I have a result. Once I have a
result I would like to output it.

My limitation is in understanding how to store the results of each
sequenced query within the conditional statement. I have never dealt
with conditional statements in SQL, so I hope they behave similarly to
other languages.
 
T

tina

i'm not quite sure if you're wanting to *build* SELECT statements on the
fly, and execute them, or simply execute existing queries in sequence,
stopping at the first one that returns one or more records and outputting
that recordset. if the latter, the simplest way would be to build each query
and save it in the database as a query object, such as qryOne, qryTwo,
qryThree, etc. then run an If statement in VBA to test each query's return
in sequence, as

Dim str As String

If DCount(1, "qryOne") >= 1 Then
str = "qryOne"
ElseIf DCount(1, "qryTwo") >= 1 Then
str = "qryTwo"
ElseIf DCount(1, "qryThree") >= 1 Then
str = "qryThree"
Else
MsgBox "All queries return zero records."
End Sub
End If

DoCmd.OutputTo acOutputQuery, str, acFormatTXT

of course, you can output the query's recordset to Excel instead, or
whichever available format you want.

hth
 
R

rafael.farias.jr

Thanks Tina. My background is not in CS or databases, so I'm reaching
a bit in trying to do this myself. I was unclear in how to approach
this problem, but I have taken your suggestion of saving the individual
queries and then controlling it by running VBA.

Two more questions:
How can I create arguments in this VBA that will be used as bind
variables in my SQL?
Can I display the answer in a pop-up or in a different way without
creating a file?

The intent is to have the arguments input by a user on a .asp web page
and output the results on the same webpage.
 
T

tina

well, as far as using variable from the VBA, you can't do that in a saved
query object. you'd have to write each SQL string in VBA, concatenating the
variables into the proper places in each string. then you'd have to open
each SQL string as a recordset, and do a recordset count to find the first
SQL statement that returns a recordset of one or more records. it's all
certainly do-able, just takes more coding.

as for displaying the answer in a pop-up, it depends on what that answer
is - a calculated value, or a series of records, or ?? you've really given
no details to work with here.

as to how to do all this from a webpage interface, i've no clue, so you'll
need others' input for help there.

hth
 
Top