Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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


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
Jimmy_inc
Asked:
Jimmy_inc
  • 3
  • 2
1 Solution
 
Jimmy_incAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
try

Query1.TypeField ='barbecue's'

-->

Query1.TypeField ='barbecue''s'
0
 
HainKurtSr. System AnalystCommented:
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
 
Jimmy_incAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now