How to write If Else Statement in SQL of query

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

I have 24 queries or so that run data by Day. I just realized I may
want to see data by Year. Rather than do a whole set of 24 queries all
over again, I see that if I just interrupt one with a little If-Else
code, I could be set.

One of the queries is this:

SELECT A2OnHandByDayqry.*
FROM A2OnHandByDayqry;

So I created one other query by Year and tried to write an If-Else in
SQL. It didn't work. I think I'm mixing SQL with VB. I tried this
(having a form with a combo box called "DayYearChosen"):

If Forms!MyForm!DayYearChosen = "Day" Then
SELECT A2OnHandByDayqry.*
FROM A2OnHandByDayqry;
Else
SELECT A2OnHandByYearqry.*
FROM A2OnHandByYearqry;
End If

Something tells me I need some quotation marks or something.

Thanks,

Matt
 
SQL is a data-access language as opposed to a programming language. As
such, the if...then syntax does not exist. You can however run SQL
statements from within VBA code. I have never run a SELECT query via
code - only action queries, so displaying the records returned has not
been a problem. I do not believe that there is a way to run a SELECT
query and display the rec's returned - I could be mistaken. If you need
to display the records, I would create a form to display them. Its
obvious that you're already using a form so I would add a button 'VIEW
RECORDS' with code similar to this in the onClick event.

DoCmd.OpenForm "viewRecords"

Then in the onLoad() event for the viewRecords form, I would add code
similar to this

If Forms!MyForm!DayYearChosen = "Day" Then
Me.RecordSource = "SELECT A2OnHandByDayqry.* FROM A2OnHandByDayqry;"
Else
Me.RecordSource = "SELECT A2OnHandByYearqry.* FROM A2OnHandByYearqry;"
End If

If the viewRecords is set to display the records as a table as opposed
to continuous records, you shouldn't have any problems displaying the
reocrds. However, if you're not useing display as a table, you'll need
to ensure that there's consistency between the two queries to ensure
that the data is displayed. When you have bound controls on a form, each
control is bound to a specific field in the underlying recordset. If the
field does not exist, you'll get an error.

Keep in mind that you may need to tweak the code as appropriate and
possibly work out a bug or two as I've done this off the top of my head.

David H
 
I have a query based on one other query (which is directly from tables)
that is used for five other queries. I then do this procedure three
more times with different groupings.

I then realized I may want to do all this by year instead of day. If I
just copy the whole of the database and change the groupings in four
early queries, I've done it! Done in two minutes.

I'd rather keep one database of course and I'd rather not create
another 20 or so queries.

I already have a form, you see, with four subs in it. These subs
change after the user selects a ShowBy button (with four choices). So
I'm doing a bit of code already.

Your solution doesn't really save me much. I've long had problems with
changing a subform's source with a choice made on the main form,
however. So your concept above is helpful nonetheless.

For example, I do have 16 subs presently and with your procedure, I
could reduce it to 4.

But I won't change it now as that would mean some name changing. It
works and I'll move on.

So thanks,

Matt
 
I was wondering, however. Is it possible to have code that actually
changes a query's SQL code? That could solve my problem. So if the
code is:

SELECT AgeCount010qry.DateYear, AgeCount010qry.Age,
Count(AgeCount010qry.NameID) AS CountOfNameID
FROM AgeCount010qry
GROUP BY AgeCount010qry.DateYear, AgeCount010qry.Age;

Can I change it to:

SELECT AgeCount010qry.DateYear, AgeCount010qry.Age,
Count(AgeCount010qry.NameID) AS CountOfNameID
FROM AgeCount099qry
GROUP BY AgeCount010qry.DateYear, AgeCount010qry.Age;

using a command button?

That is, can the query itself be physically different and if I go in
it, I will see whatever the user "pasted" there last?

Since I have a long query chain which branches off as well, doing any
other suggestion would not work (at this point).

Thanks,

Matt
 
It might be possible using the Access Object Model, however I have never
develed into it as the only times that I've dynamically created or
modified a SQL statement has been situations where the SQL statement was
executed via VBA code.
 
You can manipulate the SQL property of a DAO.QueryDef object. For example
....

Private Sub Command0_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
If InStr(1, qdf.SQL, "<5") > 0 Then
qdf.SQL = "SELECT Categories.* FROM Categories WHERE
(((Categories.CategoryID)>4));"
Else
qdf.SQL = "SELECT Categories.* FROM Categories WHERE
(((Categories.CategoryID)<5));"
End If
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery "Query2"

End Sub
 
Back
Top