Records with condition

K

Khalil Handal

Hi,
I have a form "frmbirthsearch" with 2 unbounded objects for birth dates:
txtStartDate and txtEndDate with cmdOK command button.
How do I write the event procedure (on click) so as when this OK button is
clicked the query qryaddress is opened with only the records that are
between the 2 mentioned dates?
 
W

Wolfgang Kais

Hello Khalil.

Khalil said:
Hi,
I have a form "frmbirthsearch" with 2 unbounded objects for birth
dates: txtStartDate and txtEndDate with cmdOK command button.
How do I write the event procedure (on click) so as when this OK
button is clicked the query qryaddress is opened with only the
records that are between the 2 mentioned dates?
Click to expand...

Your query should refer to the controls in your form and use them
for the criteria (use the Expression Builder from the toolbar).
Use the wizard to create a command button on your form that run the
query.
 
G

Guest

Open the query in design view
In the column with txtStartDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName1]

In the column with txtEndDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName2]


Change above
ControlName1 to the name of the control on your form containing the start date
ControlName2 to the name of the control on your form containing the End date

On the form create a button and put this as the OnClick event

Private Sub ButtonName_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub


Change ButtonName to the name of the button
Change QueryName to the name of the query

You may want to include some error handling in case there are no record
within the dates selected - up to you that but it would seem a good idea

good luck
 
K

Khalil Handal

Hi Wayne,
Just to be more clear!
The query has only ONE field for the Birtdates and has the name of
"birthdate". The txtStartDate and txtEndDate are only in the form that is to
be used to search for the records.
When the query is opened i want to see only the records that satisfy the
condition: people whose birth day is between the 2 dates".
so in the column of birthdate in the query it might be something like this:
BETWEEN [Forms]![frmbirthsearch]![ControlName1] and
[Forms]![frmbirthsearch]![ControlName2]

Please correct me if I am mistaken!!
Thanks



Wayne-I-M said:
Open the query in design view
In the column with txtStartDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName1]

In the column with txtEndDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName2]


Change above
ControlName1 to the name of the control on your form containing the start
date
ControlName2 to the name of the control on your form containing the End
date

On the form create a button and put this as the OnClick event

Private Sub ButtonName_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub


Change ButtonName to the name of the button
Change QueryName to the name of the query

You may want to include some error handling in case there are no record
within the dates selected - up to you that but it would seem a good idea

good luck

--
Wayne
Manchester, England.



Khalil Handal said:
Hi,
I have a form "frmbirthsearch" with 2 unbounded objects for birth dates:
txtStartDate and txtEndDate with cmdOK command button.
How do I write the event procedure (on click) so as when this OK button
is
clicked the query qryaddress is opened with only the records that are
between the 2 mentioned dates?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The syntax you show is correct:

BETWEEN [Forms]![frmbirthsearch]![txtStartDate] AND
[Forms]![frmbirthsearch]![txtEndDate]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Khalil Handal said:
Hi Wayne,
Just to be more clear!
The query has only ONE field for the Birtdates and has the name of
"birthdate". The txtStartDate and txtEndDate are only in the form that is
to be used to search for the records.
When the query is opened i want to see only the records that satisfy the
condition: people whose birth day is between the 2 dates".
so in the column of birthdate in the query it might be something like
this:
BETWEEN [Forms]![frmbirthsearch]![ControlName1] and
[Forms]![frmbirthsearch]![ControlName2]

Please correct me if I am mistaken!!
Thanks



Wayne-I-M said:
Open the query in design view
In the column with txtStartDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName1]

In the column with txtEndDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName2]


Change above
ControlName1 to the name of the control on your form containing the start
date
ControlName2 to the name of the control on your form containing the End
date

On the form create a button and put this as the OnClick event

Private Sub ButtonName_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub


Change ButtonName to the name of the button
Change QueryName to the name of the query

You may want to include some error handling in case there are no record
within the dates selected - up to you that but it would seem a good idea

good luck

--
Wayne
Manchester, England.



Khalil Handal said:
Hi,
I have a form "frmbirthsearch" with 2 unbounded objects for birth dates:
txtStartDate and txtEndDate with cmdOK command button.
How do I write the event procedure (on click) so as when this OK button
is
clicked the query qryaddress is opened with only the records that are
between the 2 mentioned dates?
Click to expand...
Click to expand...
Click to expand...
 
K

Khalil Handal

I have just tried it and it worked well.
Thanks agian.

Douglas J. Steele said:
The syntax you show is correct:

BETWEEN [Forms]![frmbirthsearch]![txtStartDate] AND
[Forms]![frmbirthsearch]![txtEndDate]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Khalil Handal said:
Hi Wayne,
Just to be more clear!
The query has only ONE field for the Birtdates and has the name of
"birthdate". The txtStartDate and txtEndDate are only in the form that is
to be used to search for the records.
When the query is opened i want to see only the records that satisfy the
condition: people whose birth day is between the 2 dates".
so in the column of birthdate in the query it might be something like
this:
BETWEEN [Forms]![frmbirthsearch]![ControlName1] and
[Forms]![frmbirthsearch]![ControlName2]

Please correct me if I am mistaken!!
Thanks



Wayne-I-M said:
Open the query in design view
In the column with txtStartDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName1]

In the column with txtEndDate use this as the criteria
[Forms]![frmbirthsearch]![ControlName2]


Change above
ControlName1 to the name of the control on your form containing the
start date
ControlName2 to the name of the control on your form containing the End
date

On the form create a button and put this as the OnClick event

Private Sub ButtonName_Click()
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit
End Sub


Change ButtonName to the name of the button
Change QueryName to the name of the query

You may want to include some error handling in case there are no record
within the dates selected - up to you that but it would seem a good idea

good luck

--
Wayne
Manchester, England.



:

Hi,
I have a form "frmbirthsearch" with 2 unbounded objects for birth
dates:
txtStartDate and txtEndDate with cmdOK command button.
How do I write the event procedure (on click) so as when this OK button
is
clicked the query qryaddress is opened with only the records that are
between the 2 mentioned dates?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenReport Action was Cancelled when attempting to Preview a repor 1
Date Range Query Problem 20
sql where start/end date 7
MS Office cant find object "DoCmd" 27
Parameterised query to return all records or those Between two val 2
Date Range Criteria Allowing Blank Entries 0
What is wrong with my syntax 6
Want a Parameter for date range driving a cross tab query 2

Top