[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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