Search criteria using VBA

[Guest]

I wonder if anyone can help me. I need to know how I can insert criteria
into a query using VBA. Without going into too much detail it's not practical
to insert the criteria directly into the query.

 

[Ken Snell \(MVP\)]

Can you give us more details about how you want to do this? Do you want to
change the SQL statement of a stored query? Do you want to pass an SQL
statement to a form or report or combo box or list box? Or do you want to do
something else entirely?

 

[Guest]

I've designed a database which works in 7 day blocks starting on Sunday and
ending on the following Saturday. I've got the main form which has as its
control source a query (qryColdWork). The query has all the fields I need,
including the start date (Sunday) and the end date (Saturday) fields. These
are the only two date fields in the query. I want the user to be able to
search for records by date but like I said it's not really very practical,
from the users point of view, to have to search by only using dates that
start at the beginning of the week or end of the week. So say Sunday was the
1st of the month then Saturday will be the 6th, but if the user wants to
search for a record from the 4th of the month he can't, so what I need to do
is have an input box of some sort so that the user can then type, say, the
4th into it, which in the above case will be Wednesday, and the VBA code will
then work out what the date was on Sunday and then put that date into the
query criteria row.

 

[Ken Snell \(MVP\)]

My apologies...work was / is hectic today, didn't let me have chance to
reply. Will try to do so on Tuesday (11/28).

--

Ken Snell
<MS ACCESS MVP>

I've designed a database which works in 7 day blocks starting on Sunday
and

.. . . . . . . . . . . . .

 

[Allen Browne]

You will need a little unbound form with a text box where the user can enter
a date, and then a command button to run the query (or open the report, or
filter the form, or whatever.)

I take it you want the user to be able to enter any date, and the software
will then return all the records in that week (Sunday to Saturday) of the
week that contains that date.

1. Create a form.

2. Add a text box:
Name txtStartOfWeek
Format Short Date
After Update [Event Procedure]

3. Click the Build button (...) beside the After Update property.
Access opens the code window.
Set up the code like this:
Private Sub txtStartOfWeek_AfterUpdate()
If Not IsNull(Me.txtStartOfWeek) Then
Me.txtStartOfWeek = Me.txtStartOfWeek -
Weekday(Me.txtStartOfWeek) + 1
End If
End Sub

4. Add a command button that opens the query/form/report/whatever.

5. Save the form with the name frmGetStartOfWeek.

6. In your query, enter this (one line) in the Criteria under your date
field:

= [Forms].[frmGetStartOfWeek].[txtStartOfWeek] And

< ([Forms].[frmGetStartOfWeek].[txtStartOfWeek] + 7)

The code in step 3 automatically changes the date to a Sunday.
The criteria the picks up all dates in the week.

If you prefer, you could use the WhereCondition of OpenForm or OpenReport in
the Click of the command button (step 5) instead of putting the criteria in
the query (step 6.)

 

[Guest]

Thanks very much for your help and thank you Ken Snell as well. There is no
need to reply to this post anymore as the post Allen browne wrote works fine.

You will need a little unbound form with a text box where the user can enter
a date, and then a command button to run the query (or open the report, or
filter the form, or whatever.)

I take it you want the user to be able to enter any date, and the software
will then return all the records in that week (Sunday to Saturday) of the
week that contains that date.

1. Create a form.

2. Add a text box:
Name txtStartOfWeek
Format Short Date
After Update [Event Procedure]

3. Click the Build button (...) beside the After Update property.
Access opens the code window.
Set up the code like this:
Private Sub txtStartOfWeek_AfterUpdate()
If Not IsNull(Me.txtStartOfWeek) Then
Me.txtStartOfWeek = Me.txtStartOfWeek -
Weekday(Me.txtStartOfWeek) + 1
End If
End Sub

4. Add a command button that opens the query/form/report/whatever.

5. Save the form with the name frmGetStartOfWeek.

6. In your query, enter this (one line) in the Criteria under your date
field:

= [Forms].[frmGetStartOfWeek].[txtStartOfWeek] And

< ([Forms].[frmGetStartOfWeek].[txtStartOfWeek] + 7)

The code in step 3 automatically changes the date to a Sunday.
The criteria the picks up all dates in the week.

If you prefer, you could use the WhereCondition of OpenForm or OpenReport in
the Click of the command button (step 5) instead of putting the criteria in
the query (step 6.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I've designed a database which works in 7 day blocks starting on Sunday
and
ending on the following Saturday. I've got the main form which has as its
control source a query (qryColdWork). The query has all the fields I need,
including the start date (Sunday) and the end date (Saturday) fields.
These
are the only two date fields in the query. I want the user to be able to
search for records by date but like I said it's not really very practical,
from the users point of view, to have to search by only using dates that
start at the beginning of the week or end of the week. So say Sunday was
the
1st of the month then Saturday will be the 6th, but if the user wants to
search for a record from the 4th of the month he can't, so what I need to
do
is have an input box of some sort so that the user can then type, say, the
4th into it, which in the above case will be Wednesday, and the VBA code
will
then work out what the date was on Sunday and then put that date into the
query criteria row.

 

[Ken Snell \(MVP\)]

Thanks, Allen!
--

Ken Snell
<MS ACCESS MVP>

You will need a little unbound form with a text box where the user can
enter ...