VB Forum / COM / February 2008

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