Solved

Help needed with SQL Syntax for .CommandText using variables and literal values

Posted on 2011-03-22
5
245 Views
Last Modified: 2012-05-11

Using Excel 2000 - 2003, I need help with setting the syntax used for querying a datasource (pivotTable connecting to Access Query)

As I need to convert the following SQL Strings

SELECT * FROM Query1 WHERE Query1.MyDateField BETWEEN #01/02/2009# and #28/02/2009# and Query1.TypeField ='barbecue's'

SELECT * FROM Query1 WHERE Query1.MyDateField BETWEEN #01/02/2009# and #28/02/2009# and Query1.TypeField ='Restaurant'

or I could try use:

dim Mycriteria as string

MyCriteria = "'barbecue's'"

SELECT * FROM Query1 WHERE Query1.MyDateField BETWEEN #01/02/2009# and #28/02/2009# and Query1.Type = MyCriteria

MyCriteria = "Restaurant"

SELECT * FROM Query1 WHERE Query1.MyDateField BETWEEN #01/02/2009# and #28/02/2009# and Query1.Type = MyCriteria

But either way I need the .CommandText to accept the SQL string
0
Comment
Question by:Jimmy_inc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:Jimmy_inc
ID: 35193945
I also meant to add that:::::::::::

I would also want to use a variable to hold the MyDateField s value , so :

e.g instead of just:

SELECT * FROM Query1 WHERE Query1.MyDateField BETWEEN #01/02/2009# and #28/02/2009# and Query1.TypeField ='barbecue's'

It could be

dim dtMyDatefield

dtMyDateField = MyDateField

SELECT * FROM Query1 WHERE Query1.dtMyDateField  BETWEEN #01/02/2009# and #28/02/2009# and Query1.TypeField ='barbecue's'


SELECT * FROM Query1 WHERE Query1.dtMyDateField  BETWEEN #01/02/2009# and #28/02/2009# and Query1.TypeField ='Restaurant'

or I could try use:

dim dtMyDatefield

dtMyDateField = MyDateField

dim Mycriteria as string

MyCriteria = "'barbecue's'"

SELECT * FROM Query1 WHERE Query1.dtMyDateField  BETWEEN #01/02/2009# and #28/02/2009# and Query1.Type = MyCriteria

MyCriteria = "Restaurant"

SELECT * FROM Query1 WHERE Query1.dtMyDateField  BETWEEN #01/02/2009# and #28/02/2009# and Query1.Type = MyCriteria

But either way I need the .CommandText to accept the SQL string
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35193985
try

Query1.TypeField ='barbecue's'

-->

Query1.TypeField ='barbecue''s'
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35193999
how do you set this, or where do you put these codes?

MyCriteria = "'barbecue's'"

SELECT * FROM Query1 WHERE Query1.dtMyDateField  BETWEEN #01/02/2009# and #28/02/2009# and Query1.Type = MyCriteria

0
 

Author Comment

by:Jimmy_inc
ID: 35195622
I'm almost inclined to delete this question but in the meantime, look at this code instead.


Dim MyCriteria as String
Dim strQueryName

strQueryName = "Query1"
MyCriteria = "'barbecue's'"


.CommandText = "SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = MyCriteria"

MyCriteria = "Restaurant"

.CommandText = "SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = MyCriteria"


This is what I meant.
0
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 35197802
try
Dim MyCriteria as String
Dim strQueryName

strQueryName = "Query1"
MyCriteria = "barbecue''s" 


.CommandText = "SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = '" & MyCriteria & "'"

MyCriteria = "Restaurant"

.CommandText = "SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = '" & MyCriteria & "'"

Open in new window

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question