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
