Struggling with a Type Mismatch 3

[Toby1kenobe]

Hi All

I'm trying to get results between two dates. The dates are prompted from the user in two text boxes. I then make two variables 'Sdate' and 'Edate' equal to the textboxes. Both these variables are set as Date



sql2 = "SELECT * FROM daily_points WHERE date BETWEEN ' & #sdate# & ' AND ' & #edate# & '"

rsPoints.Open sql2, CN, adOpenForwardOnly, adLockReadOnly

When i run this code i get 'Type Mismatch' and the second line above is highlighted.

Any help is as always greatly appreciated.

Toby

 

[johnwm]

If you're using Jet db then the #'s are in the wrong place:

sql2 = "SELECT * FROM daily_points WHERE [date] BETWEEN #' & sdate & '# AND #' & edate & '#"

If you're not using Jet, don't use # at all. Also if you're must use a field name that is a keyword enclose it in square brackets for safety

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Toby1kenobe]

Thanks for the speedy response

I am using the jet engine v4 with an Access back_end. The code you suggested generates a syntax error, but not a type mismatch.

Thanks again
Toby

'trying to think of something amusing to type here!'

 

[vidru]

This seems too obvious, but I think it'll work...

sql2 = "SELECT * FROM daily_points WHERE [date] BETWEEN #" & sdate & "# AND #" & edate & "#;"

-dave

 

[SonOfEmidec1100]

In johnwm's solution I think the single quotes should be double quotes to build the string correctly.

 

[SonOfEmidec1100]

Just missed it; this then gives you vidrus solution.

 

[johnwm]

Thanks for the correction - I must wake up in the morning before posting, especially when I just edit someone else's line!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Toby1kenobe]

Thanks for everyones help

Replacing the singles with double quotes has done the trick

Thanks Again

Toby