Change a query's SQL code from excel

C

cht13er

Good day all, I'm a newish VBA coder who was taught from Google Groups
- so thanks to you all very much!

I have run into a minor problem, I hope you can give me some help ...
I'm running office 2003 for this one.

In excel I have a program running ... and based on the results of an
optionbutton (called "Form2.Radio_AllParameters") I want to change the
SQL that runs a query in access. If the button is True, the SQL should
read "WHERE (((ChemistryReadings.UsedDate)=Yes) AND
((ChemistryReadings.UsedLocation)=Yes) AND ((MOE_Tables1to6.Used)<>No
Or (MOE_Tables1to6.Used) Is Null) AND
((MOE_Standard_Definitions.Used)<>No Or
(MOE_Standard_Definitions.Used) Is Null));"

but when the button is False it should read ".....AND
((MOE_Tables1to6.Used=Yes) AND...."

The reasons that I think that I want to change the SQL and not create
a new query are: (1) it is a crosstab query that's built off of this
query that is what is imported into excel and (2) I don't know how to
create the relationships from excel.

My excel VBA snippet is like this:

'--------------------------------code start-------------------------
'Public Sub cmdGo_Click()

'Declarations
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim FLD As DAO.Field
Dim qryDef1 As DAO.QueryDef
Dim strSQL As String

'Recall filepath and open database
FileName = Sheets("README").Cells(1, 20).Value
Set DB = DAO.OpenDatabase(FileName)
'---------------------------------------------------------------------------------
'Re-create the query "ChemistryQuery" based on whether you want ALL
the parameters (including those not in the standards) or not:
Set qryDef1 = DB.QueryDefs("ChemistryQuery")

If Form2.Radio_AllParameters = True Then
strSQL = "SELECT [ChemistryReadings].[Parcel] & ' ' &
[Location] & ' ' & [LaboratoryID] & ' ' & [LaboratoryJobNumber] & ' '
& [SampleDate] AS Header, Parameter_Names.[CAS#], MOE_Tables1to6.
[Parameter ID], ChemistryReadings.Parameter,
MOE_Tables1to6.StandardName, MOE_Standard_Definitions.ID,
MOE_Tables1to6.Standard, MOE_Tables1to6.Units AS MOE_Tables1to6_Units,
MOE_Tables1to6.Notes, ChemistryReadings.SampleDate,
ChemistryReadings.Reading, ChemistryReadings.Units AS Reading_Units,
ChemistryReadings.Comments, ChemistryReadings.UsedDate,
ChemistryReadings.UsedLocation, MOE_Tables1to6.Used,
MOE_Standard_Definitions.Used FROM MOE_Standard_Definitions RIGHT JOIN
((Parameter_Names RIGHT JOIN ChemistryReadings ON
Parameter_Names.ParameterName = ChemistryReadings.Parameter) LEFT JOIN
MOE_Tables1to6 ON ChemistryReadings.Parameter =
MOE_Tables1to6.Parameter) ON MOE_Standard_Definitions.FullName =
MOE_Tables1to6.StandardName" & _
"WHERE (((ChemistryReadings.UsedDate)=Yes) AND
((ChemistryReadings.UsedLocation)=Yes) AND ((MOE_Tables1to6.Used)<>No
Or (MOE_Tables1to6.Used) Is Null) AND
((MOE_Standard_Definitions.Used)<>No Or
(MOE_Standard_Definitions.Used) Is Null));"
Else
strSQL = "SELECT [ChemistryReadings].[Parcel] & ' ' &
[Location] & ' ' & [LaboratoryID] & ' ' & [LaboratoryJobNumber] & ' '
& [SampleDate] AS Header, Parameter_Names.[CAS#], MOE_Tables1to6.
[Parameter ID], ChemistryReadings.Parameter,
MOE_Tables1to6.StandardName, MOE_Standard_Definitions.ID,
MOE_Tables1to6.Standard, MOE_Tables1to6.Units AS MOE_Tables1to6_Units,
MOE_Tables1to6.Notes, ChemistryReadings.SampleDate,
ChemistryReadings.Reading, ChemistryReadings.Units AS Reading_Units,
ChemistryReadings.Comments, ChemistryReadings.UsedDate,
ChemistryReadings.UsedLocation, MOE_Tables1to6.Used,
MOE_Standard_Definitions.Used FROM MOE_Standard_Definitions RIGHT JOIN
((Parameter_Names RIGHT JOIN ChemistryReadings ON
Parameter_Names.ParameterName = ChemistryReadings.Parameter) LEFT JOIN
MOE_Tables1to6 ON ChemistryReadings.Parameter =
MOE_Tables1to6.Parameter) ON MOE_Standard_Definitions.FullName =
MOE_Tables1to6.StandardName" & _
"WHERE (((ChemistryReadings.UsedDate)=Yes) AND
((ChemistryReadings.UsedLocation)=Yes) AND ((MOE_Tables1to6.Used)<>No
Or (MOE_Tables1to6.Used) Is Null) AND
((MOE_Standard_Definitions.Used)=Yes);"
End If

qryDef1.Execute strSQL
-------------------end code-----------------


My problem is that I am unsure of IF I can change the SQL of the query
at all ..... but also how to do so if I can (the last line is what is
giving me the error called "runtime error 3421 data type conversion
error".

Thank you very much for any help that you can supply, I will monitor
this thread super closely in case I should have provided any more
information.

Chris
 
J

Jeanette Cunningham

Concentrating on just the bit for MOE_Tables1to6.Used

-- MOE_Tables1to6.Used<>No Or MOE_Tables1to6.Used Is Null
will get the records where Used is either unknown or Yes (assuming we
are talking about a Yes/No field)

-- MOE_Tables1to6.Used=Yes Or MOE_Tables1to6.Used Is Null
will get the records where Used is either unknown or Yes

-- MOE_Tables1to6.Used=No
will get the records where Used is No

The above seems as though it needs a bit more work to know exactly which
results you want to see.

Seeing that you have both left and right joins in your query, how are you
making sure that the following is not affecting your query?
http://www.allenbrowne.com/bug-14.html
see also
http://www.allenbrowne.com/NoYesNo.html

Yes, there is a way to change the query using SQL fairly easily after the
above points have been dealt with.

Jeanette Cunningham

cht13er said:
Good day all, I'm a newish VBA coder who was taught from Google Groups
- so thanks to you all very much!

I have run into a minor problem, I hope you can give me some help ...
I'm running office 2003 for this one.

In excel I have a program running ... and based on the results of an
optionbutton (called "Form2.Radio_AllParameters") I want to change the
SQL that runs a query in access. If the button is True, the SQL should
read "WHERE (((ChemistryReadings.UsedDate)=Yes) AND
((ChemistryReadings.UsedLocation)=Yes) AND ((MOE_Tables1to6.Used)<>No
Or (MOE_Tables1to6.Used) Is Null) AND
((MOE_Standard_Definitions.Used)<>No Or
(MOE_Standard_Definitions.Used) Is Null));"

but when the button is False it should read ".....AND
((MOE_Tables1to6.Used=Yes) AND...."

The reasons that I think that I want to change the SQL and not create
a new query are: (1) it is a crosstab query that's built off of this
query that is what is imported into excel and (2) I don't know how to
create the relationships from excel.

My excel VBA snippet is like this:

'--------------------------------code start-------------------------
'Public Sub cmdGo_Click()

'Declarations
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim FLD As DAO.Field
Dim qryDef1 As DAO.QueryDef
Dim strSQL As String

'Recall filepath and open database
FileName = Sheets("README").Cells(1, 20).Value
Set DB = DAO.OpenDatabase(FileName)
'---------------------------------------------------------------------------------
'Re-create the query "ChemistryQuery" based on whether you want ALL
the parameters (including those not in the standards) or not:
Set qryDef1 = DB.QueryDefs("ChemistryQuery")

If Form2.Radio_AllParameters = True Then
strSQL = "SELECT [ChemistryReadings].[Parcel] & ' ' &
[Location] & ' ' & [LaboratoryID] & ' ' & [LaboratoryJobNumber] & ' '
& [SampleDate] AS Header, Parameter_Names.[CAS#], MOE_Tables1to6.
[Parameter ID], ChemistryReadings.Parameter,
MOE_Tables1to6.StandardName, MOE_Standard_Definitions.ID,
MOE_Tables1to6.Standard, MOE_Tables1to6.Units AS MOE_Tables1to6_Units,
MOE_Tables1to6.Notes, ChemistryReadings.SampleDate,
ChemistryReadings.Reading, ChemistryReadings.Units AS Reading_Units,
ChemistryReadings.Comments, ChemistryReadings.UsedDate,
ChemistryReadings.UsedLocation, MOE_Tables1to6.Used,
MOE_Standard_Definitions.Used FROM MOE_Standard_Definitions RIGHT JOIN
((Parameter_Names RIGHT JOIN ChemistryReadings ON
Parameter_Names.ParameterName = ChemistryReadings.Parameter) LEFT JOIN
MOE_Tables1to6 ON ChemistryReadings.Parameter =
MOE_Tables1to6.Parameter) ON MOE_Standard_Definitions.FullName =
MOE_Tables1to6.StandardName" & _
"WHERE (((ChemistryReadings.UsedDate)=Yes) AND
((ChemistryReadings.UsedLocation)=Yes) AND ((MOE_Tables1to6.Used)<>No
Or (MOE_Tables1to6.Used) Is Null) AND
((MOE_Standard_Definitions.Used)<>No Or
(MOE_Standard_Definitions.Used) Is Null));"
Else
strSQL = "SELECT [ChemistryReadings].[Parcel] & ' ' &
[Location] & ' ' & [LaboratoryID] & ' ' & [LaboratoryJobNumber] & ' '
& [SampleDate] AS Header, Parameter_Names.[CAS#], MOE_Tables1to6.
[Parameter ID], ChemistryReadings.Parameter,
MOE_Tables1to6.StandardName, MOE_Standard_Definitions.ID,
MOE_Tables1to6.Standard, MOE_Tables1to6.Units AS MOE_Tables1to6_Units,
MOE_Tables1to6.Notes, ChemistryReadings.SampleDate,
ChemistryReadings.Reading, ChemistryReadings.Units AS Reading_Units,
ChemistryReadings.Comments, ChemistryReadings.UsedDate,
ChemistryReadings.UsedLocation, MOE_Tables1to6.Used,
MOE_Standard_Definitions.Used FROM MOE_Standard_Definitions RIGHT JOIN
((Parameter_Names RIGHT JOIN ChemistryReadings ON
Parameter_Names.ParameterName = ChemistryReadings.Parameter) LEFT JOIN
MOE_Tables1to6 ON ChemistryReadings.Parameter =
MOE_Tables1to6.Parameter) ON MOE_Standard_Definitions.FullName =
MOE_Tables1to6.StandardName" & _
"WHERE (((ChemistryReadings.UsedDate)=Yes) AND
((ChemistryReadings.UsedLocation)=Yes) AND ((MOE_Tables1to6.Used)<>No
Or (MOE_Tables1to6.Used) Is Null) AND
((MOE_Standard_Definitions.Used)=Yes);"
End If

qryDef1.Execute strSQL
-------------------end code-----------------


My problem is that I am unsure of IF I can change the SQL of the query
at all ..... but also how to do so if I can (the last line is what is
giving me the error called "runtime error 3421 data type conversion
error".

Thank you very much for any help that you can supply, I will monitor
this thread super closely in case I should have provided any more
information.

Chris
Click to expand...
 
C

cht13er

In response to the first point (which one of the three choices I'd
prefer for 'MOE_Tables1to6.Used'):
Either of the first two options is fine. Yes, I am using a yes/no
checkbox for that field - since I'm re-populating that field purely
from my code in excel, it would be trivial to change it so that a
"abc" means "Yes" and "xxx" means "No" - there is no real reason to
use checkboxes....

In response to the points raised by the two articles you linked
to .... I do not get any errors or missing records when I run the
queries I show in my IF statement (I can run both of them in Access,
that is.)

So I guess I just need to know how to change the SQL query!

Looking forward to the (no doubt simple) solution,

Chris
 
J

Jeanette Cunningham

'--------------------------------code start-------------------------
'Public Sub cmdGo_Click()
On Error GoTo SubErr
'Declarations
Dim DB As DAO.Database
Dim strSQL As String
Dim strWhereFirst as String
Dim strWhereMiddleTrue as String
Dim strWhereMiddleFalse as String

'Recall filepath and open database
FileName = Sheets("README").Cells(1, 20).Value
Set DB = DAO.OpenDatabase(FileName)

' I like to use a constant for the non-changing part of the query

Const cstrStub = strSQL = "SELECT [ChemistryReadings].[Parcel] & ' ' &
[Location] & ' ' & [LaboratoryID] & ' ' & [LaboratoryJobNumber] & ' '
& [SampleDate] AS Header, Parameter_Names.[CAS#], MOE_Tables1to6.
[Parameter ID], ChemistryReadings.Parameter,
MOE_Tables1to6.StandardName, MOE_Standard_Definitions.ID,
MOE_Tables1to6.Standard, MOE_Tables1to6.Units AS MOE_Tables1to6_Units,
MOE_Tables1to6.Notes, ChemistryReadings.SampleDate,
ChemistryReadings.Reading, ChemistryReadings.Units AS Reading_Units,
ChemistryReadings.Comments, ChemistryReadings.UsedDate,
ChemistryReadings.UsedLocation, MOE_Tables1to6.Used,
MOE_Standard_Definitions.Used FROM MOE_Standard_Definitions RIGHT JOIN
((Parameter_Names RIGHT JOIN ChemistryReadings ON
Parameter_Names.ParameterName = ChemistryReadings.Parameter) LEFT JOIN
MOE_Tables1to6 ON ChemistryReadings.Parameter =
MOE_Tables1to6.Parameter) ON MOE_Standard_Definitions.FullName =
MOE_Tables1to6.StandardName WHERE "

strWhereFirst = "ChemistryReadings.UsedDate=Yes AND
ChemistryReadings.UsedLocation=Yes AND "

strWhereMiddleTrue = "(MOE_Tables1to6.Used=Yes Or MOE_Tables1to6.Used Is
Null) AND"

strWhereMiddleFalse = "MOE_Tables1to6.Used=No AND "

strWhereLast = "(MOE_Standard_Definitions.Used<>No Or
MOE_Standard_Definitions.Used Is Null)"

'You build the query like this for the true query
'strSQL = cstrStub & strWhereFirst & strWhereMiddleTrue & strWhereLast
'and like this for the false query
'strSQL = cstrStub & strWhereFirst & strWhereMiddleFalse & strWhereLast

If Form2.Radio_AllParameters = True Then
strSQL = cstrStub & strWhereFirst & strWhereMiddleTrue & strWhereLast
Else
strSQL = cstrStub & strWhereFirst & strWhereMiddleFalse & strWhereLast
End If
Debug.Print strSQL


SubExit:
Exit Sub

SubErr:
msgbox err.Number & " " & err.Description
Resume SubExit

End Sub
-------------------------------
Notes: I removed this line * Dim qryDef1 As DAO.QueryDef
this line* Set qryDef1 = DB.QueryDefs("ChemistryQuery")
and this line * qryDef1.Execute strSQL
The code I have written replaces the qryDef, so we don't need it. This
should remove that error you were getting on the last line.

I removed this line * Dim RS As DAO.Recordset
and this line* Dim FLD As DAO.Field
as you are not using a recordset or a field

I removed many of the brackets from the where clause as they are not needed
when you do the sql in VBA and I find
it makes the code easier to read and check.
I added brackets around the parts where an OR was involved, I assume I
interpreted your meaning correctly.
Here* strWhereMiddleTrue = "(MOE_Tables1to6.Used=Yes Or MOE_Tables1to6.Used
Is Null) AND"

and here* strWhereLast = "(MOE_Standard_Definitions.Used<>No Or
MOE_Standard_Definitions.Used Is Null)"


I haven't tested the above, and I haven't tried to open an access database
from excel, so you may need to tweak the code a bit.
Good luck with this.

Jeanette Cunningham
 
Top