Using a data entered for a query

[Curaçaoboy]

Hi,
I want to know how to do the following.
I have a query and it has a Between.... And... function in it. With this I can get record between a period entered. This will be entered in the dialog box that will appear.

I want to the same thing but in a form. So the user enters the begin date and the end date, and Access when running the query will automatically use the values entered in the form. Is there a way to do this??

And also is it possible to run more than one query at the same time using the same information entered in the fields begin date and end date?

Any clue?????

 

[FLabrecque]

I've never actually done it, but I know it is possible. To active many queries, I suggest you do some VBA code. Something like:

DoCmd.OpenQuery "qryMyQuery", acViewNormal

I'm not 100% sure how to give it parameters, but you're a smart person, you'll figure it out. The code you'll need to call the value of the object on the form is this:
[Forms]![frmName]![txtBoxWithDateValue].

I wish you good luck, and hope this lead will help you.

 

[robthedog]

I get round similar situations by assigning the required values to variables, then creating a little function which you can use in the criteria of your query to retrieve the values – this may be an arse around tit way of doing it, but it works!

Create the variables in a new module, then build functions to retrieve the values from your variables:

Public StartDate, EndDate

Function GetStartDate()
GetStartDate = StartDate
End Function

Function GetEndDate()
GetEndDate = EndDate
End Function​

To see it how it works: Paste the code above into a new module. Then create a query using a table containing a date field and set the criteria for that field as:

Between GetStartDate() And GetEndDate()​

Save the Query as "QueryOrders"

Make a new form and add two text boxes – “TxtStartDate” and “TxtEndDate” (Set the format of these to a date style)

Add a command button – CmdGo. Then add the following code to your button:

Private Sub CmdGo_Click()

StartDate = Me.TxtStartDate
EndDate = Me.TxtEndDate

DoCmd.OpenQuery "QueryOrders", acNormal, acEdit

StartDate = Empty
EndDate = Empty

End Sub​

Run the form and enter your start and end dates then click the button to run the query –

This should work, but you need to add some validation and error trapping…

 

[Curaçaoboy]

Many Thinks Dude!!!!!!!!!!!!!

It works perfectly !!!!!! thanks thanks !!!

 

[Mile-O]

Seems like overkill for the question.

You just use a parameter to refer to the control on the form.

i.e.

Between [Forms]![MyForm]![MyControl1] And [Forms]![MyForm]![MyControl2]

 

[robthedog]

Hey! Where’s your sense of adventure? –

I did say that it was an ‘arse around tit way’ but I like it because once the functions are in place then you can use the same query over again passing the parameters from wherever, not just your original form.

I also find validation easier using VBA rather than setting a validation rules to a control – but maybe that’s just me!

You’re right though, a bit of a sledgehammer to crack a walnut for this question, but in some circumstances I find using this way really handy.

Merry Xmas ….