SQL Code to Make Query / Report

[agent-]

Hello, I have an interesting challenge right now.



I have created a form that will generate a SQL statement based on many user choices regarding columns to be in the query/report, and filters. I have tested my code and it generates a valid SQL statement that I can enter into a query SQL View. I am trying to use this SQL statement to either alter a pre-made query so that it can be exported to Excel, or create a temporary query so that it can be exported to Excel. I would prefer to do query instead of report since there are 40 columns that can be chosen.

Has anyone seen this done before? I have been doing some research and have not found a close match.

Thanks for the help!

[agent-]

Another alternative that I am testing would be creating a query that is based on the Filter form

EX:

Select IIF(Forms!Filter!check37=True, 'tblxxxxxx,fieldname', '') with an IIF for each field name
FRom tblname
WHERE tblxxxx.fieldname like "**" and forms!filter!field and "**"
and ....

but for some reason, the first IIF statement is not having it select the column name correctly. I will keep playing with this.

[orange]

Can you show the code that creates the query? Are you working with vba?

Why modify an existing query if you can generate the query??? Just wondering.

[agent-]

Dim StrSelect
Dim StrFilter
Dim sSQL
StrSelect = "Select "

If Me.Check37 = True Then
StrSelect = StrSelect + "Contact, "
End If

If Me.Check39 = True Then
StrSelect = StrSelect + "SSWAccountNumber, "
End If

If Me.Check41 = True Then
StrSelect = StrSelect + "Company, "
End If

If Me.Check43 = True Then
StrSelect = StrSelect + "Marketer, "
End If

If Me.Check47 = True Then
StrSelect = StrSelect + "ContactType, "
End If

If Me.Check47 = True Then
StrSelect = StrSelect + "Business, "
End If

If Me.Check49 = True Then
StrSelect = StrSelect + "Interest, "
End If

If Me.Check51 = True Then
StrSelect = StrSelect + "AccountManager, "
End If

If Me.Check53 = True Then
StrSelect = StrSelect + "Status, "
End If

If Me.Check55 = True Then
StrSelect = StrSelect + "PrimaryEmail, "
End If

If Me.Check57 = True Then
StrSelect = StrSelect + "Phone, "
End If

If Me.Check59 = True Then
StrSelect = StrSelect + "Mobile, "
End If

If Me.Check61 = True Then
StrSelect = StrSelect + "Fax, "
End If

If Me.Check63 = True Then
StrSelect = StrSelect + "Address, "
End If

If Me.Check65 = True Then
StrSelect = StrSelect + "Address2, "
End If

If Me.Check67 = True Then
StrSelect = StrSelect + "City, "
End If

If Me.Check69 = True Then
StrSelect = StrSelect + "State, "
End If

If Me.Check71 = True Then
StrSelect = StrSelect + "Zip, "
End If

If Me.Check73 = True Then
StrSelect = StrSelect + "AnnualVolumes, "
End If

If Me.Check75 = True Then
StrSelect = StrSelect + "MonthlyVolumes, "
End If

If Me.Check77 = True Then
StrSelect = StrSelect + "LDC, "
End If

If Me.Check81 = True Then
StrSelect = StrSelect + "Class_C_Begin, "
End If

If Me.Check83 = True Then
StrSelect = StrSelect + "District, "
End If

If Me.Check85 = True Then
StrSelect = StrSelect + "DUNS, "
End If

If Me.Check87 = True Then
StrSelect = StrSelect + "FedTaxID, "
End If

If Me.Check89 = True Then
StrSelect = StrSelect + "ContractNumber, "
End If

If Me.Check91 = True Then
StrSelect = StrSelect + "ContractStart, "
End If

If Me.Check93 = True Then
StrSelect = StrSelect + "ExpirationDate, "
End If

If Me.Check95 = True Then
StrSelect = StrSelect + "DeliveryPoint, "
End If

If Me.Check97 = True Then
StrSelect = StrSelect + "LDCAccount, "
End If

If Len(StrSelect) > 10 Then
StrSelect = Left(StrSelect, Len(StrSelect) - 2)
End If
Me.SelectStmt = StrSelect

StrFilter = StrFilter & " tblGoldMine.Contact Like ""*" & Me.Contact & "*"""
StrFilter = StrFilter & "AND tblGoldMine.SSWAccountNumber Like ""*" & Me.SSWAccountNumber & "*"""
StrFilter = StrFilter & "AND tblGoldMine.Company Like ""*" & Me.Company.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.Business Like ""*" & Me.Business.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.Interest Like ""*" & Me.Interest.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.ContactType Like ""*" & Me.ContactType.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.Status Like ""*" & Me.Status.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.AccountManager Like ""*" & Me.AccountManager.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.Marketer Like ""*" & Me.Marketer.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.ContractStart Like ""*" & Me.ContractStart & "*"""
StrFilter = StrFilter & "AND tblGoldMine.ExpiratioNDate Like ""*" & Me.ExpirationDate & "*"""
StrFilter = StrFilter & "AND tblGoldMine.LDC Like ""*" & Me.LDC.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.FedTaxID Like ""*" & Me.FedTaxID & "*"""
StrFilter = StrFilter & "AND tblGoldMine.ContractNumber Like ""*" & Me.ContractNumber & "*"""
StrFilter = StrFilter & "AND tblGoldMine.DeliveryPoint Like ""*" & Me.DeliveryPoint.Value & "*"""
StrFilter = StrFilter & "AND tblGoldMine.LDC Like ""*" & Me.LDC & "*"""



sSQL = StrSelect & " FROM tblGoldMine WHERE " & StrFilter & " Order BY tblgoldmine.Company;"
Me.Text106 = sSQL

I am trying to do an array to simplify the if statements but that is how it is for now. Sorry for the mess!

Basically it has field names for the columns, if those are selected that column name is added to Select clause. If they select filters, those are added to the where clause.

I am wondering how I can transfer this SQL statement to a query that can be exported. It seems easy in theory, but I have not been able to figure it out.

Thanks a lot Orange!

[orange]

I'd do a debug.print strSelect and strFilter to make sure what is actually generated.

I think you could drop the " before the *, or insert a & between the "^"*" & (where the ^ is)

Also, do you really need * FieldsValue *, that's a lot of processing without any use of indexes. But you know the situation, not me.

Also, since it's all from one table you could remove all of the tblGoldMine. for simplicity.

You could create a queryDef using your SQL as the SQL value of the querydef.

Then just export the querydef by Name. Untested, but I think it would work.

[agent-]

Ok Thanks for the help orange. I made the changes to my code, but now I realized that putting Like "*" does not include blank columns in the results so I have to reconfigure my where clause. The querydef seemed to work if I did not have the query results issue.

Any idea how I can not include the WHERE filter if the value is null? I was trying something like this:

'Create the Where Clause
If Me.Contact.Value = Null Then
Else
StrFilter = StrFilter & "Contact Like """ & Me.Contact & "*"""
End If

If Me.SSWAccountNumber = Null Then
Else
StrFilter = StrFilter & " AND SSWAccountNumber Like """ & Me.SSWAccountNumber & "*"""
End If

If Me.Company = Null Then
Else
StrFilter = StrFilter & " AND Company Like ""*" & Me.Company.Value & "*"""
End If

If Me.Business = Null Then
Else
StrFilter = StrFilter & " AND Business Like """ & Me.Business.Value & "*"""
End If

If Me.Interest = Null Then
Else
StrFilter = StrFilter & " AND Interest Like """ & Me.Interest.Value & "*"""
End If

If Me.ContactType = Null Then
Else
StrFilter = StrFilter & " AND ContactType Like """ & Me.ContactType.Value & "*"""
End If

If Me.Status = Null Then
Else
StrFilter = StrFilter & " AND Status Like """ & Me.Status.Value & "*"""
End If

If Me.AccountManager = Null Then
Else
StrFilter = StrFilter & " AND AccountManager Like """ & Me.AccountManager.Value & "*"""
End If

If Me.Marketer = Null Then
Else
StrFilter = StrFilter & " AND Marketer Like """ & Me.Marketer.Value & "*"""
End If

If Me.ContractStart = Null Then
Else
StrFilter = StrFilter & " AND ContractStart Like """ & Me.ContractStart & "*"""
End If

If Me.ExpirationDate = Null Then
Else
StrFilter = StrFilter & " AND ExpiratioNDate Like """ & Me.ExpirationDate & "*"""
End If

If Me.LDC = Null Then
Else
StrFilter = StrFilter & " AND LDC Like """ & Me.LDC.Value & "*"""
End If

If Me.FedTaxID = Null Then
Else
StrFilter = StrFilter & " AND FedTaxID Like """ & Me.FedTaxID & "*"""
End If

If Me.ContractNumber = Null Then
Else
StrFilter = StrFilter & " AND ContractNumber Like """ & Me.ContractNumber & "*"""
End If

If Me.DeliveryPoint = Null Then
Else
StrFilter = StrFilter & " AND DeliveryPoint Like """ & Me.DeliveryPoint.Value & "*"""
End If

If Me.LDCAccount = Null Then
Else
StrFilter = StrFilter & " AND LDCAccount Like """ & Me.LDCAccount & "*"""
End If

But it is still giving me the full WHERE Clause when there are no values in any of the filters.

Thanks again!

Also, the selections are either Text Boxes or Combo Boxes with dropdowns

[orange]

You can't use = Null.

Null has no value and can not be equal to anything.

You can check in sql, using IS Null operator. In an expresion you use the function IsNull. You can also check if the len(fieldvalue) = 0.

For example

If Me.Status = Null Then
Else
StrFilter = StrFilter & " AND Status Like """ & Me.Status.Value & "*"""
End If

If Len(Trim(Me.Status)) > 0 Then
StrFilter = StrFilter & " AND Status Like '" & Me.Status.Value & "*'"
End If

[Note there is a single quote ' after the Like and after the *]

I'm not sure if any of your fields are numeric data type, but if so, you DO NOT ENCLOSE WITH QUOTES. Just a caution.

[agent-]

Perfect, the query works perfectly. I also created the querydef part and that seems to be functional. Here is what I am using, let me know if you have a better suggestion!

(Part of the Click_function)

Dim FilterGoldMine As QueryDef
Dim dbLib As Database
Set dbLib = CurrentDb()
Debug.Print dbLib.Name
Set FilterGoldMine = dbLib.CreateQueryDef("FilterGoldMine", sSQL)

DoCmd.OutputTo acOutputQuery, "FilterGoldMine", acFormatXLS, , True

fnWait (5)

DoCmd.DeleteObject acQuery, "FilterGoldMine"

End Sub

---------------------------------------
Public Function fnWait(intNrOfSeconds As Integer)
Dim varStart As Variant
varStart = Timer
Do While Timer < varStart + intNrOfSeconds
Loop
End Function

[orange]

Looks good, and if it works as you need, even better.

[agent-]

Ok is there any way to replace the query instead of deleting old one? I am just worried that this could cause Access to crash occasionally. If you think it is good, I will keep it like that but that was just something I decided to try and see if it would work like that.

Thanks for all your help, you are awesome orange!

[orange]

Yes there is a way, but let me think of just how it works. If you don't delete

DoCmd.DeleteObject acQuery, "FilterGoldMine"

It will still be there. But of course, next time different boxes will be checked, so the query will be different.
What you really want to do is replace the SQL for "FilterGoldMine". Leave the name, so you can reference the query. Overtime, replacing the sql will cause some bloat in Access and you'd have to do a Compact and repair to retrieve "lost" space.

Here is some code, I just mocked this up. I created a query called "FilterGoldMine", it was a simple Select statement.
Then I had a newsql string (which could be your Select and filter result) and assigned it to the Querydef. This replaces the original sql.
Hope it helps.

'---------------------------------------------------------------------------------------
' Procedure : Goldmine
' Author : Jack
' Created : 6/17/2011
' Purpose : Routine to Update the SQL related to a named query.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub Goldmine()

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
On Error GoTo Goldmine_Error
Set db = CurrentDb
strQuery = "FilterGoldMine"
Debug.Print db.QueryDefs(strQuery).SQL
Dim newsql As String
'
'You would use the generated Sql based on your Form and selections
'
newsql = "SELECT A.AnimalId, A.SightingDate, A.GPSLat, A.GPSLong, " _
& " Animal.AName, AnimalCapture.CaptureDate" _
& " FROM (AnimalLocs AS A INNER JOIN Animal ON A.AnimalId = Animal.AnimalId) " _
& " INNER JOIN AnimalCapture ON Animal.AnimalId = AnimalCapture.AnimalId;"
db.QueryDefs(strQuery).SQL = newsql
Debug.Print db.QueryDefs(strQuery).SQL

On Error GoTo 0
Exit Sub

Goldmine_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Goldmine of Module AWF_Related"
End Sub

Here are the 2 sql strings in my sample

SELECT Animal.AnimalId, Animal.AName
FROM Animal;

SELECT A.AnimalId, A.SightingDate, A.GPSLat, A.GPSLong, Animal.AName, AnimalCapture.CaptureDate FROM (AnimalLocs AS A INNER JOIN Animal ON A.AnimalId = Animal.AnimalId) INNER JOIN AnimalCapture ON Animal.AnimalId = AnimalCapture.AnimalId;

I have attached a picture of the Goldmine query output.

So it works.

Ps Just thought of something. Your FilterGoldMine doesn't have to be anything at the start. You could have

Select * from PickOneOfYourTables then save that query as FilterGoldMine.

In your code with the form etc, just update the FilterGoldMine.sql as in my sample.

You don't need the CreateQuerydef stuff, since the query is already known to Access-- you saved it with a simple select. It's the name that's key to you. Once you've updated the SQL, just run /export the FilterGoldMine query.

Ta da!!!