ansaurus

Question

MS ACCESS query using form to build WHERE clause

Answer 1

+1 A:

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 & "#"

Robert Harvey 2009-10-22 20:47:13

the between values work perfectly. but, right after the WHERE keyword, needs to be "dynamic". I want to know if its possible In Access, to change this to be something that is dependent on what is selected inside a combo box

Marlon 2009-10-23 14:41:54

@marloncopeland, see my edited answer.

Robert Harvey 2009-10-23 15:21:13

I'll try this, but I got a feeling you're misinterpreting my problem. In your example, I need "myDateColumn" to come from a value in a combo box, the value in the combo box will say a column in the table. so, WHERE [comboxboxvalue] BETWEEN [textboxdate] AND [textboxdate]

Marlon 2009-10-23 15:36:53

I made the edit, but of course, now you need a way to execute it. Is this string going into the DataSource of a subform, or something similar?

Robert Harvey 2009-10-23 15:49:34

hmm, I see your using vba... i was using the sql form of the query designer... I was trying to avoid alot of work..

Marlon 2009-10-23 16:45:18

You can set conditions based on form controls in the query designer, but I don't believe you can specify columns dynamically in this way. If you try, you will get the value of the form control as the column output, not the value of the table column.

Robert Harvey 2009-10-23 17:06:10

You still haven't said what you're using the output of the query for. If it's for a subform, you can simply put the SQL string I gave you into the DataSource property of the subform.

Robert Harvey 2009-10-23 17:07:29

Answer 2

A:

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.

onedaywhen 2009-10-23 09:20:03

my problem is the "item" (in ur example it would be OrderDate) right after the WHERE keyword, needs to be "dynamic". I want to know if its possible In Access, to change this to be something that is dependent on what is selected inside a combo box

Marlon 2009-10-23 14:40:20

So have multiple procs -- GetOrderByOrderDate, GetOrderByRequiredDate, GetOrderByShippedDate, etc -- then use your combobox's value to 'dynamically' choose the required proc.

onedaywhen 2009-10-24 06:14:41

Answer 3

+1 A:

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]);")

wakingrufus 2009-11-16 21:18:17

ansaurus

tags:

views:

answers:

MS ACCESS query using form to build WHERE clause

related questions