Help with basic SELECT statement

[majordog]

Hey,
I would like to base my query on a date the user inputs. The field in the database smalldatetime, also records the time. I want to get all the records on a specific date regardless of the time.

Below is what I have, with txtDate = 12/19/2002

SELECT * FROM vTestEditor WHERE DatePerformed =" & CDate(txtDate.Text)

What method can I use to stipulate that I want the date search without time being a factor?

Thanks guys -

 

[JohnYingling]

Instead of "=", use BETWEEN for 2000/12/19 00:00:00 and 2000/12/19 23:59:59.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[rann]

hi,
If you want just the date use.

SELECT * FROM vTestEditor WHERE DatePerformed =" &
CDate(Format(txtDate.Text,"mm/dd/yyyy&quot)

rann.

 

[JohnYingling]

What Data Type was used to define the DatePerformed field on the database?

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[majordog]

smalldatetime

 

[FRoeCassNet]

That won't help much, since the times are in the database, not the txtDate field.

Either use JohnYingling's example, or look into your database documentation to what it uses for a CDate() type of function. For instance, in SQL Server you could do something like

SELECT * FROM vTestEditor WHERE convert( varchar(10),DatePerformed,101)=" & CDate(txtDate.Text)

 

[MarkSweetland]

Some Other ways (note the single quotes)

SQLServer
"SELECT * FROM vTestEditor WHERE convert(varchar, DatePerformed, 1) = '" & CDate(txtDate.Text) & "'"

Access

"SELECT * from dbo_tbls_qtm WHERE datediff("d", DatePerformed, '" & CDate(txtDate.Text) & "') =0 "



Mark

 

[majordog]

Thanks all, much appreciated - and the database was SQL Server