2 Questions. ASP & SQL

[Stuart]

Hi All,

Can anyone help.

Q1. How do I send 2 dates to a stored query in access. What I have in the
query so far is

Select * from tblOrders where Date_Archived between #3/01/04# and #3/31/04#

What I want is to send to variables to this query that the user inputs.

Q2. I know that this is probably not the right group but I am trying to
trim a string (first name to 1 letter) in an SQL statement.

Does any one know how to do this? I do not want to use ASP as I will be
build a complete CSV file on the file (column names and data)

I appreicate any help on these matters.

Regards,

Stuart

 

[Rob Meade]

...

Q1. How do I send 2 dates to a stored query in access. What I have in the
query so far is

Select * from tblOrders where Date_Archived between #3/01/04# and

#3/31/04#

I'm guessing you already have your sql statement in a string....


strDateOne = "03/01/04"
strDateTwo = "03/31/04"

SQL = "Select * from tblOrders where Date_Archived between #" & strDateOne &
"# and #" & strDateTwo & "#"

You'd obviously want to validate the date entries before doing this...

Q2. I know that this is probably not the right group but I am trying to
trim a string (first name to 1 letter) in an SQL statement.

You mean the results after the query has been executed or actually in the
sql statement?

If the former then...

strInitial = RS("Forename") ' guessing your field names
strInitial = Left(strInitial, 1)

Does any one know how to do this? I do not want to use ASP as I will be
build a complete CSV file on the file (column names and data)

Aha! You want the latter...

SQL = "SELECT Left(Forename, 1) AS Initial FROM your_table"

Hope this helps...

Regards

Rob

 

[Bob Barrows]

Hi All,

Can anyone help.

Q1. How do I send 2 dates to a stored query in access. What I have
in the query so far is

Select * from tblOrders where Date_Archived between #3/01/04# and
#3/31/04#

What I want is to send to variables to this query that the user
inputs.

Great! I am happy to see you are not contemplating using dynamic sql. It is
much more efficient to use a saved query.
The first step is to parameterize this query. Change the sql statement to:

Select <column list> from tblOrders where Date_Archived between [pStart] and
[pEnd]

Save the query as qGetOldOrders. When you test it (what? you weren't
planning to test this in Access? shame - one of the benefits of using
saved queries is that you get to test them in Access before trying to run
them in ASP), you will find that Access will prompt you for values for the
parameters. You will supply those values in your vbscript code.

Note: do not use selstar (select *) in production code: by being lazy, you
make the query engine work harder which is not good if you want an efficient
application

Now, in asp, do this
'create and open a connection called cn, then
set rs=createobject("adodb.recordset")
cn.qGetOldOrders #2004/03/01#,#2004/03/31#,rs

Your recordset will now be open.

Q2. I know that this is probably not the right group but I am trying
to trim a string (first name to 1 letter) in an SQL statement.

Unrelated questions should be split into separate posts. I'll make an
exception this time and answer it. In the future, please follow the
guideline of one question per post.

You can use the VBA Left() function in a Jet SQL statement:

Select Left(column_name,1), etc.

HTH,
Bob Barrows