QBF in an Access Project using a between

[Mingmar]

I was wondering how to use a between on the Order Date field in the
Stored Text Procedure portion of the MS KB article How to implement
Query by Form (QBF) in an Access project
http://support.microsoft.com/kb/286828/en-us. I tried a whole bunch of
different combinations but not with any success. So if anyone could get
me pointed in the right direction it would be greatly appreciated.

Thanks

 

[Sylvain Lafontaine]

Using the Between clause is very easy, so I don't see how you could have
some problem with it. Could you show us some of your combinations so that we
could understand better where you are making some kind of error?

 

[Mingmar]

Using the Between clause is very easy, so I don't see how you could have
some problem with it. Could you show us some of your combinations so that we
could understand better where you are making some kind of error?

Here was the most promising between that we tried using. We were also
not using an if on it because I don't want the user to be able to
leave it null. Also If I try and run the procedure I get ther error
must declare the variable 'StartDate'.

SELECT @SQLSTRING = 'WHERE OrderDate BETWEEN CONVERT(Varchar(20),
@StartDate) AND CONVERT(Varchar(20), @EndDate)'

 

[Sylvain Lafontaine]

Hum, there is big difference between:

SELECT @SQLSTRING = @SQLSTRING + ' AND OrderDate = ''' +
Convert(varchar(20), @OrderDate) + ''''

And:

SELECT @SQLSTRING = 'WHERE OrderDate BETWEEN CONVERT(Varchar(20),
@StartDate) AND CONVERT(Varchar(20), @EndDate)'

You have removed the enclosing ' and the + operator separating the various
strings. Also, you didn't notice that @OrderDate is a parameter to the
stored procedure and probably you have tried to use something like
@StartDate without adding it as a new parameter.

Without beeing rude, it appears that your problem have nothing to do with
the Between clause itself but simply that you don't know the basis of the
T-SQL language. The best of course of action for you would be to study the
exemple given in great detail; understanding what each line and operator are
doing and learn more about T-SQL and Stored Procedures.

 

[Mingmar]

Hum, there is big difference between:

SELECT @SQLSTRING = @SQLSTRING + ' AND OrderDate = ''' +
Convert(varchar(20), @OrderDate) + ''''

And:

SELECT @SQLSTRING = 'WHERE OrderDate BETWEEN CONVERT(Varchar(20),
@StartDate) AND CONVERT(Varchar(20), @EndDate)'

You have removed the enclosing ' and the + operator separating the various
strings. Also, you didn't notice that @OrderDate is a parameter to the
stored procedure and probably you have tried to use something like
@StartDate without adding it as a new parameter.

Without beeing rude, it appears that your problem have nothing to do with
the Between clause itself but simply that you don't know the basis of the
T-SQL language. The best of course of action for you would be to study the
exemple given in great detail; understanding what each line and operator are
doing and learn more about T-SQL and Stored Procedures.

I think I may have confused you. We are trying to do a date range and
that is why the betweenwas in that line. I can get that SP to work fine
except for when we try and add a between to do a date range. We could
not figure out how to correctly put the between clause in so we can do
a date range in the SP.I also did add both Startdate and Enddate as
parametors. Sorry for the confusion.

Thanks

 

[Sylvain Lafontaine]

From the piece of code that you have given, I cannot see how the SP could
work properly event without the Between clause. However, you can try
something like:

SELECT @SQLSTRING = @SQLSTRING + ' AND OrderDate Between ''' +
Convert(varchar(20), @StartDate) + '''' +
+ ' AND ''' + Convert(varchar(20), @EndDate) + ''''

You can also try replacing the Between with >= and <= ; something like:

SELECT @SQLSTRING = @SQLSTRING + ' AND OrderDate >= ''' +
Convert(varchar(20), @StartDate) + '''' +
+ ' AND OrderDate <= ''' + Convert(varchar(20), @EndDate) + ''''

Also, don't forget that DateTime fields in SQL-Server have a time part
associated with the date and that part must have been set to 0 if you want
to make a <= comparaison. You can also replace the @EndDate with the
following day and use the < operator instead of using the <= . Another
possibility would be to dynamically truncate the time portion while doing
the Select query.

 

[Sylvain Lafontaine]

Of course, my previous post is based on the assumption that you have used a
DateTime field to store your date values. If you have used a string field,
then your piece of code may or may not work based on the local
regionalisation parameters for the server.

In this case, you should add a format at the end of the Convert() function
to make sure that your string comparaisons will work for a date range (or a
date-time range).

 

[123]

11