Text box input in SQL statement

[bill haven]

How can I write an SQL statement using an access data base with visual basic 6 that will except the text from a text box. mainly a date?
This Can be an sql statement
SELECT * FROM [TABLE NAME] WHERE [DATE] LIKE '"TXTDATE.TEXT'"
The ubove does not work??

[nicc777]

I don't know a thing about VB, but here's a thought. I assume you can concat strings in VB. Simply do something like:

varname = "SELECT * FROM [TABLENAME] WHERE [DATE] LIKE '" + TXTDATE.TEXT + "'"

The above of course might need to be adjusted to fit the VB Syntax, but I think you get my point. Out of pure interest, the Perl equiv would look like this:

$sql = "SELECT * FROM `tablename` WHERE `date` LIKE '$editboxvar'";

Of course you would need to untaint the $editboxvar, otherwise you can have a SQL injection attack on your code. The same applies for your VB. You must untaint the TXTDATE.TXT var.

Cheers

[magelatt]

Using SQL 7/2000 and VB6:
(Using MS Access may require different formatting of the Query)

If you already have a SELECT statement you like all you have to do is create 3 variables - one to capture the contents of the Text Box, one to hold the SELECT statement and one to hold the WHERE clause.

Assume you have a Text Box on your form named txtState that the user will type in a state choice like 'TX'. You can issue the following on your CommandButton that runs your query: (or anywhere else it makes sense)

Dim SQLString As String
Dim SQLWhere As String
Dim st As String

' get the value of txtState
st = UCase(txtState.Text)

' start building the SQL Query
' NOTE: you need the trailing spaces at the end
SQLString = "SELECT CustID, Name, Addr1, Addr2, " & _
"Rtrim(CITY) + ', ' + State + ' ' + ZipCode as CityStZip " & _
"From dbo.vw_CustList "

' Now, build your where clause using the variable st. Watch the double quotes... ;-)
SQLWhere = " WHERE State IN(" & st & ") "

' Concatenate the two string variables together.
SQLString = SQLString + SQLWhere

Now run your query using SQLString

[Anbu]

You can use the following Build-in Function in MS-Access for setting the query:

DateValue («stringexpr»)

Thus the query:

SELECT * FROM [TABLE NAME] WHERE [DATE] LIKE DateValue ('"TXTDATE.TEXT'")