CODEWITHSUNDEEP
sqlms-accessms-access-2007
Marlon
Marlon

Reputation: 127

MS ACCESS query using form to build WHERE clause

I need to be able to read a combo box to determine to column to use for a where clause. Simple example would be SELECT * FROM TABLE WHERE [Forms]![frmNameWhatever]![ComboTime] BETWEEN [blah]![blah]![blah] AND [blah]![blah]![blah]

blah blah blah works... The first part, right after the where, returns zero rows... Am i using the wrong syntax? I've tried this w/ a text box as well and it still returns zero rows... Sorry someone might have to re-write this but i'm tired.. its the end of the day

Thanks for any help ^^

Upvotes: 0

Views: 6796

Answers (3)

wakingrufus
wakingrufus

Reputation: 415

You can concatenate an sql statement and run it with RunSQL like so:

DoCmd.RunSQL("(SELECT * FROM TABLE WHERE " & Forms("frmNameWhatever").ComboTime.Value & " BETWEEN [blah]![blah]![blah] AND [blah]![blah]![blah]);")

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180908

Try putting pound signs around your BETWEEN values.

BETWEEN #8:00 AM# and #12:00 PM#

To create a dynamic SQL string:

strSQL = _
   "Select myColumns FROM myTable WHERE " & Me.myComboBox & " BETWEEN #" & _
   Me.MyFirstTextBoxDate & "# AND #" & Me.MySecondTextBoxDate & "#"

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57093

It may not be a good idea to hard code your Form's control names within your SQL code. Consider a PROCEDURE with strongly-typed parameters e.g. ANSI-92 Query Mode syntax:

CREATE PROCEDURE GetOrdersByPeriod
(
 :start_date DATETIME, 
 :end_date DATETIME
)
AS 
SELECT OrderID, CustomerID, OrderDate
  FROM Orders
 WHERE OrderDate BETWEEN :start_date AND :end_date;

You would then EXECUTE this proc by passing in your controls' values as parameters.

Upvotes: 0

Related Questions