Set your Query Criteria using VBA

[gheyman]

ACCESS QUESTION

Can you set your queries criteria using VBA (Initiated by a Button Click event)?

I am trying to figure a way to query by a date range the user selects or show all records if the user does not select dates.

(I don't want to default the dates)

 

[ranman256]

Use a form.
the form has 2 boxes, txtStart and txtEnd.
2 queries; 1 for all, 1 for the range.

clicking the Find button opens one...:

sub btnFind_click()
If IsNull(txtStart) then
    Docmd.openquery "qsAllDates
else
    Docmd.openquery "qsDateRng
end if
end sub

the query for qsDateRng looks at the form to get the range....
select * from table where [dateFld] between forms!myForm!txtStart and forms!myForm!txtEnd

 

[xenou]

(I don't want to default the dates)

If you show all records you have the same result as including all records from the earliest date to the latest date (plus all records with null dates). So you probably could achieve this with default dates after all.