Run Queries using form data as criteria

[sarlo00]

Let me try to restate my problem.

A user opens the database and "main" form opens with a command button and three text boxes. The three text boxes are: Shift, Start Date, and Stop Date.

Upon clicking the command button, input boxes open to allow the user to enter values for each of the three text boxes. After the third text box value is entered, the VBA code opens and runs a number of queries.

How can I use the values in the text boxes as criteria for the queries?

For example:

When the user enters the Start Date (with similar code for Stop Date (pdate2&3)):

pdate = InputBox("enter Production Start Date")
pdate = Format(pdate, shortdate)
pdate1 = Format(DateAdd("d", 1, pdate), shortdate)

If IsDate(pdate) Then
pdate = pdate
pdateyy = Format(pdate, "yyyymmdd")
pdate1 = pdate1
pdate1yy = Format(pdate, "yyyymmdd")
Else
pdate = Format(DateAdd("d", -1, Now()), shortdate)
pdate1 = Format(DateAdd("d", 1, Now()), shortdate)
pdateyy = Format(pdate, "yyyymmdd")
pdate1yy = Format(pdate, "yyyymmdd")
End If

txtStart.SetFocus
txtStart.Text = pdate

I want to use this as criteria :

>=([forms]![main]![pdate]) And <=([forms]![main]![pdate2])
>=([forms]![main]![pdate1]) And <=([forms]![main]![pdate3])

Is there a better way?

My queries are not recognizing "pdate" and are creating blank tables.
!!!!

 

[Andrew Fergus]

Hi

Re :

Upon clicking the command button, input boxes open to allow the user to enter values for each of the three text boxes.

I'm not sure why you want to use VBA to capture the entries when the user could just enter them into the 3 text boxes on the form......if it is to capture data, then you can force the VBA routine to stop if one of them hasn't been completed. Would that be simpler?

Re :

How can I use the values in the text boxes as criteria for the queries?

You don't need to use VBA to get the criteria into the queries. You can enter the criteria directly into the queries using:
=Forms!MyFormName!MyFieldName or something similar

HTH, Andrew

 

[sarlo00]

First, thank you for your reply.

Second, this is not originally my code. I am trying to rebuild the database without losing any of the logic involved using Access 2007. I don't know why the creator of the database originally wanted the data output to be within the date range requested and/or within 1 plus the date range requested.

Is there a way for me to add one day to the dates in the criteria if I allow the user to enter them directly into the text boxes?

I would also like to know why the VBA code works in the older version of Access?

This thread has the VBA code in full http://www.mrexcel.com/forum/showthread.php?t=327020

 

[Andrew Fergus]

Hi

I have responded in your original thread. It will be simpler to continue in the one thread.

Andrew

 

[Von Pookie]

Please continue in your other thread
http://www.mrexcel.com/forum/showthread.php?t=327020