Solved

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

Posted on 2011-03-22
5
241 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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