How to write If Else Statement in SQL of query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doyle60@aol.com - 11 Jul 2005 13:59 GMT

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

Reply to this Message

David C. Holley - 11 Jul 2005 15:34 GMT

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 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
[quoted text clipped - 23 lines]
>
> Matt

Reply to this Message

doyle60@aol.com - 11 Jul 2005 16:51 GMT

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

Reply to this Message

doyle60@aol.com - 12 Jul 2005 12:22 GMT

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

Reply to this Message

David C. Holley - 12 Jul 2005 15:06 GMT

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.

> 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
[quoted text clipped - 23 lines]
>
> Matt

Reply to this Message

Brendan Reynolds - 12 Jul 2005 16:43 GMT

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

Signature

Brendan Reynolds (MVP)

>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
[quoted text clipped - 23 lines]
>
> Matt

Reply to this Message

doyle60@aol.com - 13 Jul 2005 15:33 GMT

Thanks. I will give it a try.

Matt

Reply to this Message