Search Date Range 2

[BrendaD]

I need to search my SQL Server database by a date range - find all calls placed between this date and that date.

I am using the smalldatetime data type for the field, which is named txtDateTimeCall.

I have been able to find out how to write this for Access with variables but not how to write this for SQL.

Any ideas on how to set up the SQL in the RecordSet Window's SQL box?

Select *
FROM dbo.vwDispatch
WHERE txtDateTimeCall BETWEEN varStartDate AND varEndDate

Default Value = % for both variables
Run-time Value = Request("txtDateTimeCall&quot for both variables

Does not work.....

Thanks!

Brenda

 

[lebisol]

"Request("txtDateTimeCall&quot "
what is this?requesting a field from DB?
are u using a two fields in form to sumbit to your querry?
if so then use
--form.asp---
...
<input name=&quot;field1&quot; type=&quot;text&quot;...
-------------

---action.asp--------
...
Request.Form(&quot;field1&quot
----------------------

all the best!

 

[Cheech]

&quot;Select *
FROM dbo.vwDispatch
WHERE txtDateTimeCall BETWEEN varStartDate AND varEndDate

Default Value = % for both variables
Run-time Value = Request(&quot;txtDateTimeCall&quot for both variables&quot;

I may be wrong but the way I read that sql statment you are trying to select records that fall between the same date. esp as Run-time Value for both variables is set to Request(&quot;txtDateTimeCall&quot. So your select would read -

SELECT * FROM dbo.vwDispatch WHERE &quot;01/01/01&quot; BETEWEEN &quot;01/01/01&quot; AND &quot;01/01/01&quot;

Sorry if I am reading it wrong but that dont make sense to me.

Cheech

[Peace][Pipe]
The secret of life is honesty and fair dealing. If you can fake that, you've got it made.
Groucho Marx (1895-1977)

 

[BrendaD]

Thank you, both of you are right.

I reworked the SQL:

<input name=&quot;StartDate&quot;...
<input name=&quot;EndDate&quot;...

....Where txtDateTimeCall BETWEEN 'varStartDate' and 'varEndDate'

Default Value = 2002 and 2003 instead of wildcards
Run-Time Value = Request.Form(&quot;StartDate&quot and Request.Form(&quot;EndDate&quot

It works perfectly!

Thank you for pointing me in the right direction - which would be the opposite of what I was attempting!!!!

Brenda

 

[lebisol]

nice one Cheech... 2+2=4

 

[Cheech]

;-) only here to help and sometimes be helped ;-)

[Peace][Pipe]
The secret of life is honesty and fair dealing. If you can fake that, you've got it made.
Groucho Marx (1895-1977)

 

[aamaker]

im trying to do something similar using

two input text fields that accept a from_date date and a to_date ... the sql statement is

SELECT *
FROM TABLENAME
WHERE entered_date BETWEEN 'VARfromdate' AND 'VARtodate'

Default value for variables is 1/1/2002 and 1/1/2003

I keep getting Data type mismatch in criteria expression.

Note: the database is access 2002, using DSN-less connection and the database field &quot;entered_date&quot; is in Short date format.

What am I missing here?

 

[lebisol]

well....sounds like your DB is not set to accept the format of 1/1/2002 or the data in the form is mistyped (so validate with client side VB or JS)
All the best!

> need more info?
:: don't click HERE ::