SQL statement help needed

[Judda]

--------------------------------------------------------------------------------
Hi there,

I have an access 2000 db with a column of data defined as a DOUBLE.

If I construct an SQL query in access like this:

SELECT [table].[item]From mydb WHERE [table].[item] Between 20010101 And 20010105;

It work fine and data is selected.

However if I try it in VB6 (sp5) like this:

strSQL = "SELECT * FROM [mydb] WHERE [table].[item] BETWEEN '" & sdf & "' and '" & xyz & "'"

I get nothing ! sdf and xyz are defined as a doubles (variable names are examples only hence weird names !)

If I try it like this :

strSQL = "SELECT * FROM [mydb] WHERE [table].[item] BETWEEN " & sdf & " and " & xyz & ""

I get data type mismatch.

Any idea on how I should properly construct the query in vb ?

Many thanks
Judda

[peet]

looks correct, if mydb is a table that is ...

Try this syntax:

strSQL = "SELECT * FROM Table WHERE Item BETWEEN " & sdf & " and " & xyz & ""

[ksw]

If the above suggestion doesn't help, I would try some other data types. I'm not sure that a VB Double would be the same as an MSAccess Double. That could be causing the Type Mismatch. I couldn't really find anything in MSAccess help about the Double data type so that's why I don't know it is the same as VB's.

[Karin]

Using text fields for dates gives you best control and it does not randomly change format on you :"SELECT * FROM Table WHERE Datevalue(iif(Item is null,'1/1/1900',Item)) BETWEEN Datevalue(' " & 123 & " ') AND Datevalue(' " & 456 & " ')"

If you have to use double, you might want to check "isdate" in VB :
"SELECT * FROM Table WHERE iif(Item is null,01011900,Item) BETWEEN " & sdf & " and " & xyz & ""

Null values in the date field give you data type mismatch, too.

[ksw]

If you do use a text field for dates, you need to be extremely careful of what format you have chosen to use. Text fields sort differently than date fields. I had this happen once where my program said that 1/1/2000 was before 12/31/1999. Logically it's not, but using an alphabetical sort, it is. You always have to include the leading zeros on the month and day fields. And if you want to make sure that mulitple years sort properly, the best format to use is yyyy-mm-dd (or yyyy/mm/dd). In the last suggestion, the DateValue probably handles this, but personally I have never used it.
I've found that if you have a true date, you end up better off in the long run using a date data type.

[peet]

hmm.. I normally save all dates with format YYYYMMDD in text fields.

works fine for me....