Have to cycle through parameter values twice to pull info.

[Ron Weaver]

I have a form with a list box connected to a query. I am trying to limit the
list to criteria between two dates. It finally works, but not until I cycle
through the beginning and ending dates twice. Once in a while it works on the
first try. I know I am entering the correct values. Also, is there a tutorial
out there That shows how to create this same function on the form with two
text boxes?

 

[Brian Bastl]

Ron,

It helps when we can see what you're doing. Can you post the query's SQL?

Brian

 

[Ron Weaver]

SELECT Orders.StartDate, Orders.OrderID, [LastName] & ", " & [FirstName] AS
[Customer Name], Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Start Date] And [End Date]))
ORDER BY [LastName] & ", " & [FirstName];
Thanks. I hope this helps.

 

[Ken Sheridan]

Ron:

Its pretty straightforward. Lets assume the form is called frmMyForm, the
two text boxes on the form are called txtStart and txtEnd and the list box is
called lstMyList. There are various ways you can update the list when dates
are entered in the text boxes, but lets say you want to update the list as
soon as either text box’s value is changed. Lets also assume that you want
each text box to an optional parameter, i.e. if you enter a date in txtStart
only you’ll get all rows on or after that date, if you enter a date in txtEnd
only you’ll get all rows on or before that date, if you enter both you’ll get
all rows on or after the first date and on or before the second date.

To do the above, as well as referencing the text boxes for a value you also
test for them being Null. As you are dealing with dates its also as well to
declare the parameters as DateTime as a value in short date format can
otherwise be interpreted as an arithmetical expression. So the RowSource
property for the list box would be along these lines:

PARAMETERS
Forms!frmMyForm!txtStart DATETIME,
Forms!frmMyForm!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!frmMyForm!txtStart OR Forms!frmMyForm!Start IS NULL)
AND
(SatrtDate < Forms!frmMyForm!txtEnd + 1 OR Forms!frmMyForm!End IS NULL)
ORDER BY LastName, FirstName;

The first expression within parentheses will evaluate independently to TRUE
if the date in the row is on or after txtStart or if txtStart is Null, i.e.
left blank. Similarly the second expression will evaluate to TRUE if the
date is before one day after txtEnd or txtEnd is Null. Defining the range in
this way is better than using BETWEEN….AND as that will not return any rows
on the last date if the values contain a non-zero time of day, i.e. the time
would have to be midnight at the start of the day, which is what you get if
you enter a date into a date/time column without a time of day. However,
non-zero times of day can easily creep in unseen, the usual culprit being the
inappropriate use of Now() as the default value. On the whole it is best to
play safe and assume that dates might also include a non-zero time of day.

To update the list box when a value is entered into either of the text boxes
you requery the list box in the AfterUpdate event procedure of each text box
with:

Me.lstMyList.Requery

You can further enhance this if you wish by including some validation code
in the BeforeUpdate event procedure of each text box to do things such as
check that a valid date has been entered (or the control has been made
Null),or that if both text boxes have dates txtEnd does not contain an
earlier date than txtStart. If the validation fails you cancel the update
with:

Cancel = True

This prevents the AfterUpdate event from firing so the list box is not
requeried until the validation criteria have been satisfied.

Ken Sheridan
Stafford, England

 

[Brian Bastl]

Ron,

Nothing stands out in your query, and I can't replicate it in my test db. Is
it possible that there is another control on your form which also uses this
query as a rowsource? Just guessing here.

Another option would be to place two unbound text boxes on your form:
txtStartDate and txtEndDate, and use those as the criteria: Between
Forms!MyForm!txtStartDate And Forms!MyForm!txtEndDate

Brian

SELECT Orders.StartDate, Orders.OrderID, [LastName] & ", " & [FirstName] AS
[Customer Name], Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Start Date] And [End Date]))
ORDER BY [LastName] & ", " & [FirstName];
Thanks. I hope this helps.

Ron,

It helps when we can see what you're doing. Can you post the query's SQL?

Brian


limit
the on
the

 

[Ron Weaver]

Thanks Brian. I will give those suggestions a try.

Ron,

Nothing stands out in your query, and I can't replicate it in my test db. Is
it possible that there is another control on your form which also uses this
query as a rowsource? Just guessing here.

Another option would be to place two unbound text boxes on your form:
txtStartDate and txtEndDate, and use those as the criteria: Between
Forms!MyForm!txtStartDate And Forms!MyForm!txtEndDate

Brian

SELECT Orders.StartDate, Orders.OrderID, [LastName] & ", " & [FirstName] AS
[Customer Name], Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Start Date] And [End Date]))
ORDER BY [LastName] & ", " & [FirstName];
Thanks. I hope this helps.

Ron,

It helps when we can see what you're doing. Can you post the query's SQL?

Brian


I have a form with a list box connected to a query. I am trying to limit
the
list to criteria between two dates. It finally works, but not until I
cycle
through the beginning and ending dates twice. Once in a while it works on
the
first try. I know I am entering the correct values. Also, is there a
tutorial
out there That shows how to create this same function on the form with two
text boxes?

 

[Ron Weaver]

Ken I tried this and got the following message:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", "&''',specified on this form or
report does not exist.
This is the code I entered, showing the form my list box and text boxes are
in:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!Search By Date!txtStart OR Forms!Search By Date!Start IS
NULL)
AND
(EndDate < Forms!Search By Date!txtEnd + 1 OR Forms!Search By Date!End IS
NULL)
ORDER BY LastName, FirstName;
Do you see anything wrong with this, or did I miss a step?

Ron:

Its pretty straightforward. Lets assume the form is called frmMyForm, the
two text boxes on the form are called txtStart and txtEnd and the list box is
called lstMyList. There are various ways you can update the list when dates
are entered in the text boxes, but lets say you want to update the list as
soon as either text box’s value is changed. Lets also assume that you want
each text box to an optional parameter, i.e. if you enter a date in txtStart
only you’ll get all rows on or after that date, if you enter a date in txtEnd
only you’ll get all rows on or before that date, if you enter both you’ll get
all rows on or after the first date and on or before the second date.

To do the above, as well as referencing the text boxes for a value you also
test for them being Null. As you are dealing with dates its also as well to
declare the parameters as DateTime as a value in short date format can
otherwise be interpreted as an arithmetical expression. So the RowSource
property for the list box would be along these lines:

PARAMETERS
Forms!frmMyForm!txtStart DATETIME,
Forms!frmMyForm!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!frmMyForm!txtStart OR Forms!frmMyForm!Start IS NULL)
AND
(SatrtDate < Forms!frmMyForm!txtEnd + 1 OR Forms!frmMyForm!End IS NULL)
ORDER BY LastName, FirstName;

The first expression within parentheses will evaluate independently to TRUE
if the date in the row is on or after txtStart or if txtStart is Null, i.e.
left blank. Similarly the second expression will evaluate to TRUE if the
date is before one day after txtEnd or txtEnd is Null. Defining the range in
this way is better than using BETWEEN….AND as that will not return any rows
on the last date if the values contain a non-zero time of day, i.e. the time
would have to be midnight at the start of the day, which is what you get if
you enter a date into a date/time column without a time of day. However,
non-zero times of day can easily creep in unseen, the usual culprit being the
inappropriate use of Now() as the default value. On the whole it is best to
play safe and assume that dates might also include a non-zero time of day.

To update the list box when a value is entered into either of the text boxes
you requery the list box in the AfterUpdate event procedure of each text box
with:

Me.lstMyList.Requery

You can further enhance this if you wish by including some validation code
in the BeforeUpdate event procedure of each text box to do things such as
check that a valid date has been entered (or the control has been made
Null),or that if both text boxes have dates txtEnd does not contain an
earlier date than txtStart. If the validation fails you cancel the update
with:

Cancel = True

This prevents the AfterUpdate event from firing so the list box is not
requeried until the validation criteria have been satisfied.

Ken Sheridan
Stafford, England

I have a form with a list box connected to a query. I am trying to limit the
list to criteria between two dates. It finally works, but not until I cycle
through the beginning and ending dates twice. Once in a while it works on the
first try. I know I am entering the correct values. Also, is there a tutorial
out there That shows how to create this same function on the form with two
text boxes?

 

[Ron Weaver]

Brian, I was using the same query on two different forms. I created another
query, but it still presents the same problem intermittently. I tried using
the code with two text boxes named txtStartDate and txtEndDate. It didn't
work. Syntax error. I put the code in the query, in the critera, just like
you had it except I subsituted my forms name where you had MyForm. Is this
correct?

Ron,

Nothing stands out in your query, and I can't replicate it in my test db. Is
it possible that there is another control on your form which also uses this
query as a rowsource? Just guessing here.

Another option would be to place two unbound text boxes on your form:
txtStartDate and txtEndDate, and use those as the criteria: Between
Forms!MyForm!txtStartDate And Forms!MyForm!txtEndDate

Brian

SELECT Orders.StartDate, Orders.OrderID, [LastName] & ", " & [FirstName] AS
[Customer Name], Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Start Date] And [End Date]))
ORDER BY [LastName] & ", " & [FirstName];
Thanks. I hope this helps.

Ron,

It helps when we can see what you're doing. Can you post the query's SQL?

Brian


I have a form with a list box connected to a query. I am trying to limit
the
list to criteria between two dates. It finally works, but not until I
cycle
through the beginning and ending dates twice. Once in a while it works on
the
first try. I know I am entering the correct values. Also, is there a
tutorial
out there That shows how to create this same function on the form with two
text boxes?

 

[Brian Bastl]

Ron,

I put the code in the query, in the critera, just like
you had it except I subsituted my forms name where you had MyForm.

yes, I'm glad you picked up on that. I should have mentioned it.

I didn't think to ask about your computer's regional settings. If you're
computer's regional system settings for handling dates is not the US default
of mm/dd/yyyy, then you should take a look at the following website:

http://allenbrowne.com/ser-36.html

Otherwise, I'm really not sure. Sorry.

Brian

Brian, I was using the same query on two different forms. I created another
query, but it still presents the same problem intermittently. I tried using
the code with two text boxes named txtStartDate and txtEndDate. It didn't
work. Syntax error. I put the code in the query, in the critera, just like
you had it except I subsituted my forms name where you had MyForm. Is this
correct?

Ron,

Nothing stands out in your query, and I can't replicate it in my test db. Is
it possible that there is another control on your form which also uses this
query as a rowsource? Just guessing here.

Another option would be to place two unbound text boxes on your form:
txtStartDate and txtEndDate, and use those as the criteria: Between
Forms!MyForm!txtStartDate And Forms!MyForm!txtEndDate

Brian

SELECT Orders.StartDate, Orders.OrderID, [LastName] & ", " &

[FirstName]
AS

[Customer Name], Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Start Date] And [End Date]))
ORDER BY [LastName] & ", " & [FirstName];
Thanks. I hope this helps.

:

Ron,

It helps when we can see what you're doing. Can you post the query's SQL?

Brian


I have a form with a list box connected to a query. I am trying to limit
the
list to criteria between two dates. It finally works, but not until I
cycle
through the beginning and ending dates twice. Once in a while it

works
on

the
first try. I know I am entering the correct values. Also, is there a
tutorial
out there That shows how to create this same function on the form

with
two

text boxes?

 

[Ron Weaver]

Thanks for trying!

Ron,

I put the code in the query, in the critera, just like
you had it except I subsituted my forms name where you had MyForm.

yes, I'm glad you picked up on that. I should have mentioned it.

I didn't think to ask about your computer's regional settings. If you're
computer's regional system settings for handling dates is not the US default
of mm/dd/yyyy, then you should take a look at the following website:

http://allenbrowne.com/ser-36.html

Otherwise, I'm really not sure. Sorry.

Brian

Brian, I was using the same query on two different forms. I created another
query, but it still presents the same problem intermittently. I tried using
the code with two text boxes named txtStartDate and txtEndDate. It didn't
work. Syntax error. I put the code in the query, in the critera, just like
you had it except I subsituted my forms name where you had MyForm. Is this
correct?

Ron,

Nothing stands out in your query, and I can't replicate it in my test db. Is
it possible that there is another control on your form which also uses this
query as a rowsource? Just guessing here.

Another option would be to place two unbound text boxes on your form:
txtStartDate and txtEndDate, and use those as the criteria: Between
Forms!MyForm!txtStartDate And Forms!MyForm!txtEndDate

Brian


SELECT Orders.StartDate, Orders.OrderID, [LastName] & ", " & [FirstName]
AS
[Customer Name], Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Start Date] And [End Date]))
ORDER BY [LastName] & ", " & [FirstName];
Thanks. I hope this helps.

:

Ron,

It helps when we can see what you're doing. Can you post the query's
SQL?

Brian


I have a form with a list box connected to a query. I am trying to
limit
the
list to criteria between two dates. It finally works, but not until I
cycle
through the beginning and ending dates twice. Once in a while it works
on
the
first try. I know I am entering the correct values. Also, is there a
tutorial
out there That shows how to create this same function on the form with
two
text boxes?

 

[Ken Sheridan]

Ron:

I think the problem probably is that because you’ve used spaces in the form
name you need to enclose it in brackets whenever you refer to it in the query:

[Search By Date]

Ken Sheridan
Stafford, England

Ken I tried this and got the following message:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", "&''',specified on this form or
report does not exist.
This is the code I entered, showing the form my list box and text boxes are
in:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!Search By Date!txtStart OR Forms!Search By Date!Start IS
NULL)
AND
(EndDate < Forms!Search By Date!txtEnd + 1 OR Forms!Search By Date!End IS
NULL)
ORDER BY LastName, FirstName;
Do you see anything wrong with this, or did I miss a step?

Ron:

Its pretty straightforward. Lets assume the form is called frmMyForm, the
two text boxes on the form are called txtStart and txtEnd and the list box is
called lstMyList. There are various ways you can update the list when dates
are entered in the text boxes, but lets say you want to update the list as
soon as either text box’s value is changed. Lets also assume that you want
each text box to an optional parameter, i.e. if you enter a date in txtStart
only you’ll get all rows on or after that date, if you enter a date in txtEnd
only you’ll get all rows on or before that date, if you enter both you’ll get
all rows on or after the first date and on or before the second date.

To do the above, as well as referencing the text boxes for a value you also
test for them being Null. As you are dealing with dates its also as well to
declare the parameters as DateTime as a value in short date format can
otherwise be interpreted as an arithmetical expression. So the RowSource
property for the list box would be along these lines:

PARAMETERS
Forms!frmMyForm!txtStart DATETIME,
Forms!frmMyForm!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!frmMyForm!txtStart OR Forms!frmMyForm!Start IS NULL)
AND
(SatrtDate < Forms!frmMyForm!txtEnd + 1 OR Forms!frmMyForm!End IS NULL)
ORDER BY LastName, FirstName;

The first expression within parentheses will evaluate independently to TRUE
if the date in the row is on or after txtStart or if txtStart is Null, i.e.
left blank. Similarly the second expression will evaluate to TRUE if the
date is before one day after txtEnd or txtEnd is Null. Defining the range in
this way is better than using BETWEEN….AND as that will not return any rows
on the last date if the values contain a non-zero time of day, i.e. the time
would have to be midnight at the start of the day, which is what you get if
you enter a date into a date/time column without a time of day. However,
non-zero times of day can easily creep in unseen, the usual culprit being the
inappropriate use of Now() as the default value. On the whole it is best to
play safe and assume that dates might also include a non-zero time of day.

To update the list box when a value is entered into either of the text boxes
you requery the list box in the AfterUpdate event procedure of each text box
with:

Me.lstMyList.Requery

You can further enhance this if you wish by including some validation code
in the BeforeUpdate event procedure of each text box to do things such as
check that a valid date has been entered (or the control has been made
Null),or that if both text boxes have dates txtEnd does not contain an
earlier date than txtStart. If the validation fails you cancel the update
with:

Cancel = True

This prevents the AfterUpdate event from firing so the list box is not
requeried until the validation criteria have been satisfied.

Ken Sheridan
Stafford, England

I have a form with a list box connected to a query. I am trying to limit the
list to criteria between two dates. It finally works, but not until I cycle
through the beginning and ending dates twice. Once in a while it works on the
first try. I know I am entering the correct values. Also, is there a tutorial
out there That shows how to create this same function on the form with two
text boxes?

 

[Ron Weaver]

Ken: I got it working. Thanks.

Ron:

I think the problem probably is that because you’ve used spaces in the form
name you need to enclose it in brackets whenever you refer to it in the query:

[Search By Date]

Ken Sheridan
Stafford, England

Ken I tried this and got the following message:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", "&''',specified on this form or
report does not exist.
This is the code I entered, showing the form my list box and text boxes are
in:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!Search By Date!txtStart OR Forms!Search By Date!Start IS
NULL)
AND
(EndDate < Forms!Search By Date!txtEnd + 1 OR Forms!Search By Date!End IS
NULL)
ORDER BY LastName, FirstName;
Do you see anything wrong with this, or did I miss a step?

Ron:

Its pretty straightforward. Lets assume the form is called frmMyForm, the
two text boxes on the form are called txtStart and txtEnd and the list box is
called lstMyList. There are various ways you can update the list when dates
are entered in the text boxes, but lets say you want to update the list as
soon as either text box’s value is changed. Lets also assume that you want
each text box to an optional parameter, i.e. if you enter a date in txtStart
only you’ll get all rows on or after that date, if you enter a date in txtEnd
only you’ll get all rows on or before that date, if you enter both you’ll get
all rows on or after the first date and on or before the second date.

To do the above, as well as referencing the text boxes for a value you also
test for them being Null. As you are dealing with dates its also as well to
declare the parameters as DateTime as a value in short date format can
otherwise be interpreted as an arithmetical expression. So the RowSource
property for the list box would be along these lines:

PARAMETERS
Forms!frmMyForm!txtStart DATETIME,
Forms!frmMyForm!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate >= Forms!frmMyForm!txtStart OR Forms!frmMyForm!Start IS NULL)
AND
(SatrtDate < Forms!frmMyForm!txtEnd + 1 OR Forms!frmMyForm!End IS NULL)
ORDER BY LastName, FirstName;

The first expression within parentheses will evaluate independently to TRUE
if the date in the row is on or after txtStart or if txtStart is Null, i.e.
left blank. Similarly the second expression will evaluate to TRUE if the
date is before one day after txtEnd or txtEnd is Null. Defining the range in
this way is better than using BETWEEN….AND as that will not return any rows
on the last date if the values contain a non-zero time of day, i.e. the time
would have to be midnight at the start of the day, which is what you get if
you enter a date into a date/time column without a time of day. However,
non-zero times of day can easily creep in unseen, the usual culprit being the
inappropriate use of Now() as the default value. On the whole it is best to
play safe and assume that dates might also include a non-zero time of day.

To update the list box when a value is entered into either of the text boxes
you requery the list box in the AfterUpdate event procedure of each text box
with:

Me.lstMyList.Requery

You can further enhance this if you wish by including some validation code
in the BeforeUpdate event procedure of each text box to do things such as
check that a valid date has been entered (or the control has been made
Null),or that if both text boxes have dates txtEnd does not contain an
earlier date than txtStart. If the validation fails you cancel the update
with:

Cancel = True

This prevents the AfterUpdate event from firing so the list box is not
requeried until the validation criteria have been satisfied.

Ken Sheridan
Stafford, England

:

I have a form with a list box connected to a query. I am trying to limit the
list to criteria between two dates. It finally works, but not until I cycle
through the beginning and ending dates twice. Once in a while it works on the
first try. I know I am entering the correct values. Also, is there a tutorial
out there That shows how to create this same function on the form with two
text boxes?