Solved

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

Posted on 2011-03-22
5
242 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
  • 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 51

Expert Comment

by:HainKurt
ID: 35193985
try

Query1.TypeField ='barbecue's'

-->

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

Expert Comment

by:HainKurt
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 51

Accepted Solution

by:
HainKurt 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 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