Create If/Esle from SELECT query in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a form close button that when clicked will run a check. This will
run a SQL Select query, that I would like the result to determine how an
If/Else will be ran. For example, if their is a result in the query, I would
like it run 'A', else, if there is no result, then 'B' will run (resulting in
nothing happening). How would I set this up in VBA? I am not sure how to
use the result from the SQL to determine which option will be used. I am
thinking it would be something like:
DoCmd.RunSQL "SELECT..."
If result = yes then
'A'
elseif result = no then
'B'
endif
How would I set this up? Do I need to have the result post to a variable? I
need syntax help, thanks
-gary
 
RunSQL is limited to Action queries, not SELECT queries.

Use DLookup() to see if your query returns any results.
Here's how:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

If you really are using an action query, and you want to know if any records
were affected, use Execute instead of RunSQL. Here' how:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


news:[email protected]...
 
Hi Gary

It depends what type of result(s) the SQL returns.

Look up the domain aggregate functions in the help - things like dsum, dmax,
dmin, dcount, etc

If you just want to test if the SQL returns zero rows or not then you can
use code similar to...

if dcount("*", "QueryName") = 0 then
'do something
else
'do something else
end if


Replace QueryName with the name of your query (this will be the SQL you want
to run).

Hope this points you in the right direction - if you need to post back it
would be useful to see the SQL you were trying to use.

Andy Hull
 
Gary said:
I have a form close button that when clicked will run a check. This will
run a SQL Select query, that I would like the result to determine how an
If/Else will be ran. For example, if their is a result in the query, I would
like it run 'A', else, if there is no result, then 'B' will run (resulting in
nothing happening). How would I set this up in VBA? I am not sure how to
use the result from the SQL to determine which option will be used. I am
thinking it would be something like:
DoCmd.RunSQL "SELECT..."
If result = yes then
'A'
elseif result = no then
'B'
endif
Click to expand...


RunSQL is useless here. You would have to open a recordset
before checking a set of records.

If you had posted some details about what you want to check,
I might have been able to make a useful suggestion. As it
is, all I can say is to try using DCount if you just want to
see if a specific record already exists or use DLookup if
you wnat to check the value of a field in a specific record.
 
Thank you Allen!
Your refenerce materials are excellent! I appreciate you taking the time
for a response
-gary
 
Thank you Andy!
Your example was exactly what was needed! As I had the SQL set up to return
only values that matched, I was able to use the code below, reverse the logic
and it is now implemented and working - thanks!
-gary
 
Thank you for the reply, Marshall.
The DCount is exactly what I needed, thank you for the post!
-gary
 
Back
Top