Building SQL in Visual Basic

[Faraz Meghani]

Hello,

I am trying to build an SQL in Visual Basic and need some help with
quotation mark(s).

I have two text fields on a form where the user enters a sales group
range to view records between those sales groups. For example:
Salesgroup 1 through Salesgroup 10. I have written the following piece
of code to get the records between me.salesgroup1 and me.salesgroup2 but
it gives me a compile error and says expected end of statement.

strWhere = strWhere & "([SGRP] = " BETWEEN """" & Me.SalesGroup1 & "'
AND '" Me.SalesGroup2 "') And "

Does anyone see what it is that is wrong with the above statement?

Thanks in advance for any help.

*** Sent via Developersdex http://www.developersdex.com ***

[Bruce]

On Feb 28, 10:17 am, Faraz Meghani <[e-mail]:

Hello,

I am trying to build an SQL in Visual Basic and need some help with
quotation mark(s).

I have two text fields on a form where the user enters a sales group
range to view records between those sales groups. For example:
Salesgroup 1 through Salesgroup 10. I have written the following piece
of code to get the records between me.salesgroup1 and me.salesgroup2 but
it gives me a compile error and says expected end of statement.

strWhere = strWhere & "([SGRP] = " BETWEEN """" & Me.SalesGroup1 & "'
AND '" Me.SalesGroup2 "') And "

Does anyone see what it is that is wrong with the above statement?

Thanks in advance for any help.

*** Sent via Developersdexhttp://www.developersdex.com***

If SalesGroup1 and SalesGroup2 are string values then I think you want
the following:

strWhere = strWhere & "([SGRP] BETWEEN '" & Me.SalesGroup1 & "' AND
'" & Me.SalesGroup2 & "') And "

If SalesGroup1 and SalesGroup2 are numeric values then simply remove
the single quotes.

Bruce

[Larry Linson]

You do understand that, due to the sort characteristics of alphanumeric
data, if your are using the text "Salesgroup 1", "Salesgroup 2", ...
"Salesgroup 10", that Salesgroups 2, 3, 4, 5, 6, 7, and 8 will not be
returned by a WHERE clause of

BETWEEN "Salesgroup 1" and "Salesgroup 10"

That is because the sorted values, in Ascending order, are:

Salesgroup 1
Salesgroup 10
Salesgroup 2
Salesgroup 3
Salesgroup 4
.. . .

This might be contributing to the problem that you are experiencing. That
is, even if you get the quotes corrected on the "BETWEEN", you may not get
the results you expect.

Larry Linson
Microsoft Access MVP