fester62
asked on
How do you create and store a SELECT query in VBA, QUERYDEF does not seem to apply to select query definitions
I have a complex query that uses other queries which should run off user-supplied dates. I want to create the base queries using a date paramter and store them - then use them in the comples query joins.
Access does not appear to allow you to store a SELECT query using the QUERYDEF statement.
Tony
Access does not appear to allow you to store a SELECT query using the QUERYDEF statement.
Tony
ASKER
I tried doing that originally but the queries run off a data parameter. I can specify a parameter in the query designer but then that pops up a box when the query runs - whereas i want the query to use txtRunDate supplied on a Form. - I am not sure how to get the query to work off that date and not prompt the user, without using code.
your help is appreciated.
tony
your help is appreciated.
tony
>whereas i want the query to use txtRunDate supplied on a Form.
In that case, just feed that value in your query. (Below is air code, rename the obvious)
{SQL View}
SELECT blah, blah, blah
FROM blah
WHERE SomeDate = [Forms]![SomeFormName]![So meTextboxN ame]
{Design View}
In the column that has the date you want to compare to your form field, enter this in the criteria row
[Forms]![SomeFormName]![So meTextboxN ame]
In that case, just feed that value in your query. (Below is air code, rename the obvious)
{SQL View}
SELECT blah, blah, blah
FROM blah
WHERE SomeDate = [Forms]![SomeFormName]![So
{Design View}
In the column that has the date you want to compare to your form field, enter this in the criteria row
[Forms]![SomeFormName]![So
ASKER
Ok, I can see how that should work, actually instead of a textbox i want it to pick up a DTpicker1.value, but when I susbitute that into your suggested code ti does not work (for either a textbox or a DTpicker), do i need to enclose with '#' or some other formatting ? Cant get that to work either.
tony
tony
ASKER
ok, I have it working off a textbox now, just had the date in the wrong format. Now I need to get it working with a dtpicker control. Should there be a difference, cant see why ?
tony
tony
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the requirement for creating these queries via VBA code, and not pre-creating them manually?