By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Change a query's SQL code from excel

P: n/a
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
Feb 12 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.