I am trying to use BETWEEN in my sq

[karenmierkalns]

I am trying to use BETWEEN in my sql where clause, for my dates. However, my date field is a text datatype, to get around entering nothing into the field.

Is there an easy to get the proper data back?

SELECT * FROM ASB WHERE DateEst BETWEEN '08/24/1994' AND '07/17/2001'

is bringing back:

08/23/2001

Any suggestions?

Thanks - Karen

 

[chiph]

Karen -

Dates are handled differently by different database vendors. Typically you have to use a function to convert your string respresentation to a SQL Date representation.

In SQL Server, you use the CONVERT function:
[tt]CONVERT(datetime, '08/12/99')[/tt]

In MS Access, you surround the date with hash-marks:
[tt]#08/12/99#[/tt]

So, consult your database vendor's documentation.

Chip H.

 

[karenmierkalns]

I am using Access.

I tried the hash marks. The problem lies in the Access field being text, rather than date datatype - not in converting the VB data.

The comparison between my VB variables and the database isn't proper, because of the text field.

ie. it is checking 07 with 08 rather than first the year, then the month, then the day.

It is not evaluating what is already in the database as a date. However, I do need the database to be text, rather than date, because I need to enter nothing into those fields, if need be.

Is there a way to convert the DATABASE fields to date in my SQL statement. I am not looking for SQL functions, because they don't work the same in VB. Converting my VB variables won't help - that is not the problem.

Any help would be appreciated.

Thanks. - Karen

 

[PrerYDoG]

Two methods of doing this. Not sure about the first one working though.

Use the between statement formatted with # signs, like #08/25/79# .

Second option, and this is how I do it on a program I've written.

Instead of storing 08/25/79 Date formats into your text field, store it 19790825 (internation standard date format, YYYYMMDD) into the Text field, then convert it when you need to display it. Then you can run your Between clause with #19790825# and it should work fine.

Mark

 

[karenmierkalns]

Thanks a lot! This worked great.

- Karen