Pls Help - "between" Criterea returning no results

[Matt Spoljarevic]

Hello

I have created a Continuous Form to give a list of invoices and total them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source†of the
form properties) and has 2 linked tables being “Job Details†and “invoice
Item detailsâ€

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

 

[John W. Vinson]

Hello

I have created a Continuous Form to give a list of invoices and total them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source” of the
form properties) and has 2 linked tables being “Job Details” and “invoice
Item details”

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

What are you putting in the textboxes? What are the values of the invoice
number in the table field that you expect to find and aren't? What's the
datatype of the field? And could you post the complete SQL of the query?
Several things that could be going wrong...

The most likely is that you have a Join to the Invoice Item Details table
and/or the Job Details Table and there are no records saved to disk for those
tables for the chosen invoice. There might be data in bound controls on the
subforms, but unless you explicitly save that data (by moving off the record
or leaving the subform), and requery these unbound textboxes, you won't see
the results of the query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Matt Spoljarevic]

Hello John

Thank you for helping out

The Invoice numbers are 5 - 6 digits only. In the source table the Invoice
Number's format is set to a Long Integer. (The Text boxes I am using in the
form are also set to the same). I have also tried changing these to combo
boxes which list the Invoice numbers from the table, this did not work
either.

The SQL is:

SELECT Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type, Sum([Invoice Details].Extended) AS SumOfExtended,
Sum([Extended]*0.1) AS GST, Sum([Extended]*1.1) AS Total FROM Workorder
INNER JOIN [Invoice Details] ON Workorder.Invoice=[Invoice Details].Invoice
GROUP BY Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type HAVING (((Workorder.Invoice) Between [Forms]![Invoice
Totals]![invstart] And [Forms]![Invoice Totals]![invfinish]));

The "Workorder" Table is what I referred to in my original post as the Job
Details table

I also tried deleting the Invoice details table form the query, and only
querying the Workorder table data, but this did not work either.

When I am not using a criteria in the query, it does return all the invoices
entered and all the information correctly as I have it in the query. As soon
as I put back the Criteria all the information disappears regardless if I
enter an invoice number into the textboxes or not.

Any guidance you can give me with this would be greatly appreciated

Regards

Matt


"John W. Vinson" wrote in message

Hello

I have created a Continuous Form to give a list of invoices and total them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source†of the
form properties) and has 2 linked tables being “Job Details†and “invoice
Item detailsâ€

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

What are you putting in the textboxes? What are the values of the invoice
number in the table field that you expect to find and aren't? What's the
datatype of the field? And could you post the complete SQL of the query?
Several things that could be going wrong...

The most likely is that you have a Join to the Invoice Item Details table
and/or the Job Details Table and there are no records saved to disk for
those
tables for the chosen invoice. There might be data in bound controls on the
subforms, but unless you explicitly save that data (by moving off the record
or leaving the subform), and requery these unbound textboxes, you won't see
the results of the query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

What happens if you hard code the values in the query?

HAVING WorkOrder.Invoice Between 1 and 9999999

What are the values in invStart and invFinish?

One thing to try is to declare the parameters at the beginning of the query.

Parameters Forms]![Invoice Totals]![invstart] Long,
[Forms]![Invoice Totals]![invfinish] Long;
SELECT ...
FROM ...

John Spencer
Access MVP 2002-2005, 2007-2011

Hello John

Thank you for helping out

The Invoice numbers are 5 - 6 digits only. In the source table the Invoice
Number's format is set to a Long Integer. (The Text boxes I am using in the
form are also set to the same). I have also tried changing these to combo
boxes which list the Invoice numbers from the table, this did not work either.

The SQL is:

SELECT Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type, Sum([Invoice Details].Extended) AS SumOfExtended,
Sum([Extended]*0.1) AS GST, Sum([Extended]*1.1) AS Total FROM Workorder INNER
JOIN [Invoice Details] ON Workorder.Invoice=[Invoice Details].Invoice GROUP BY
Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type HAVING (((Workorder.Invoice) Between [Forms]![Invoice
Totals]![invstart] And [Forms]![Invoice Totals]![invfinish]));

The "Workorder" Table is what I referred to in my original post as the Job
Details table

I also tried deleting the Invoice details table form the query, and only
querying the Workorder table data, but this did not work either.

When I am not using a criteria in the query, it does return all the invoices
entered and all the information correctly as I have it in the query. As soon
as I put back the Criteria all the information disappears regardless if I
enter an invoice number into the textboxes or not.

Any guidance you can give me with this would be greatly appreciated

Regards

Matt


in message

Hello

I have created a Continuous Form to give a list of invoices and total them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source†of the
form properties) and has 2 linked tables being “Job Details†and “invoice
Item detailsâ€

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

What are you putting in the textboxes? What are the values of the invoice
number in the table field that you expect to find and aren't? What's the
datatype of the field? And could you post the complete SQL of the query?
Several things that could be going wrong...

The most likely is that you have a Join to the Invoice Item Details table
and/or the Job Details Table and there are no records saved to disk for those
tables for the chosen invoice. There might be data in bound controls on the
subforms, but unless you explicitly save that data (by moving off the record
or leaving the subform), and requery these unbound textboxes, you won't see
the results of the query.

 

[Matt Spoljarevic]

John

I did the experiment of making the criteria Workorder.Invoice Between 30002
and 30020 and it work perfectly

when I set it back to Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish] and entered 30002 and 30020 to the text
boxes, it returned no results again

I then tried declaring the parameters at the beginning of the query as
below, however this still returned no results.

I also tried changing the text boxes to Combo boxes populating them with the
invoice numbers from the Workorder table to ensure the values are identical,
but that again has given me no results

I'm at a loss with it, do you have any other thoughts on what could be
happening ?




"John Spencer" wrote in message
What happens if you hard code the values in the query?

HAVING WorkOrder.Invoice Between 1 and 9999999

What are the values in invStart and invFinish?

One thing to try is to declare the parameters at the beginning of the query.

Parameters Forms]![Invoice Totals]![invstart] Long,
[Forms]![Invoice Totals]![invfinish] Long;
SELECT ...
FROM ...

John Spencer
Access MVP 2002-2005, 2007-2011

Hello John

Thank you for helping out

The Invoice numbers are 5 - 6 digits only. In the source table the Invoice
Number's format is set to a Long Integer. (The Text boxes I am using in
the
form are also set to the same). I have also tried changing these to combo
boxes which list the Invoice numbers from the table, this did not work
either.

The SQL is:

SELECT Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type, Sum([Invoice Details].Extended) AS SumOfExtended,
Sum([Extended]*0.1) AS GST, Sum([Extended]*1.1) AS Total FROM Workorder
INNER
JOIN [Invoice Details] ON Workorder.Invoice=[Invoice Details].Invoice
GROUP BY
Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type HAVING (((Workorder.Invoice) Between [Forms]![Invoice
Totals]![invstart] And [Forms]![Invoice Totals]![invfinish]));

The "Workorder" Table is what I referred to in my original post as the Job
Details table

I also tried deleting the Invoice details table form the query, and only
querying the Workorder table data, but this did not work either.

When I am not using a criteria in the query, it does return all the
invoices
entered and all the information correctly as I have it in the query. As
soon
as I put back the Criteria all the information disappears regardless if I
enter an invoice number into the textboxes or not.

Any guidance you can give me with this would be greatly appreciated

Regards

Matt


in message

Hello

I have created a Continuous Form to give a list of invoices and total
them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a
start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source†of
the
form properties) and has 2 linked tables being “Job Details†and “invoice
Item detailsâ€

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

What are you putting in the textboxes? What are the values of the invoice
number in the table field that you expect to find and aren't? What's the
datatype of the field? And could you post the complete SQL of the query?
Several things that could be going wrong...

The most likely is that you have a Join to the Invoice Item Details table
and/or the Job Details Table and there are no records saved to disk for
those
tables for the chosen invoice. There might be data in bound controls on
the
subforms, but unless you explicitly save that data (by moving off the
record
or leaving the subform), and requery these unbound textboxes, you won't
see
the results of the query.

 

[John W. Vinson]

Hello

I have created a Continuous Form to give a list of invoices and total them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source” of the
form properties) and has 2 linked tables being “Job Details” and “invoice
Item details”

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

Is the Invoice field in your table perhaps a Lookup Field? If so, the stored
value is not what you see on the screen, and the criteria will not match.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

I did the experiment of making the criteria Workorder.Invoice
Between 30002 and 30020 and it work perfectly

when I set it back to Between [Forms]![Invoice Totals]![invstart]
And [Forms]![Invoice Totals]![invfinish] and entered 30002 and
30020 to the text boxes, it returned no results again

I then tried declaring the parameters at the beginning of the
query as below, however this still returned no results.

I also tried changing the text boxes to Combo boxes populating
them with the invoice numbers from the Workorder table to ensure
the values are identical, but that again has given me no results

I'm at a loss with it, do you have any other thoughts on what
could be happening ?

What does this return in the Immediate Window?

?[Forms]![Invoice Totals]![invstart]

 

[Matt Spoljarevic]

Hello David

I'm sorry, I'm a novice at this and I don't know how to use the immediate
window




"David-W-Fenton" wrote in message

I did the experiment of making the criteria Workorder.Invoice
Between 30002 and 30020 and it work perfectly

when I set it back to Between [Forms]![Invoice Totals]![invstart]
And [Forms]![Invoice Totals]![invfinish] and entered 30002 and
30020 to the text boxes, it returned no results again

I then tried declaring the parameters at the beginning of the
query as below, however this still returned no results.

I also tried changing the text boxes to Combo boxes populating
them with the invoice numbers from the Workorder table to ensure
the values are identical, but that again has given me no results

I'm at a loss with it, do you have any other thoughts on what
could be happening ?

What does this return in the Immediate Window?

?[Forms]![Invoice Totals]![invstart]

 

[Matt Spoljarevic]

Hello John

The field is set as a Number (Long Integer). The value for this is created
when we generate an invoice using a "Create Invoice" Form in which I have
created a the below code when the form opens

If IsNull(Invoice) Then
Me.Invoice = DMax("Invoice", "Workorder") + 1
End If

The form I am having trouble with is completely independent from the above

Regards

Matt



"John W. Vinson" wrote in message

Hello

I have created a Continuous Form to give a list of invoices and total them
in the form footer

On the form header I have created 2 unbound Control Boxes to enter a start
and finish Invoice number, so I can enter an Invoice number range to be
shown on the form. (both of these refresh after update)

The Data of the form is based on a Query (within the “record source†of the
form properties) and has 2 linked tables being “Job Details†and “invoice
Item detailsâ€

I have set the criteria of the Invoice number within the Query as

Between [Forms]![Invoice Totals]![invstart] And
[Forms]![Invoice Totals]![invfinish]

But this is returning a blank result.

Could someone please help, and tell what I am doing wrong, I have been
trying to resolve this for days but I just can’t see what the problem is?

Thank you in advance

Regards
Matt

Is the Invoice field in your table perhaps a Lookup Field? If so, the stored
value is not what you see on the screen, and the criteria will not match.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

Press ctrl-g. A Module Window will open. At the bottom left is the Immediate
window. enter David's text and press Enter. Is the content of the textbox
returned into the Immediate window?

Hello David

I'm sorry, I'm a novice at this and I don't know how to use the
immediate window




"David-W-Fenton" wrote in message

I did the experiment of making the criteria Workorder.Invoice
Between 30002 and 30020 and it work perfectly

when I set it back to Between [Forms]![Invoice Totals]![invstart]
And [Forms]![Invoice Totals]![invfinish] and entered 30002 and
30020 to the text boxes, it returned no results again

I then tried declaring the parameters at the beginning of the
query as below, however this still returned no results.

I also tried changing the text boxes to Combo boxes populating
them with the invoice numbers from the Workorder table to ensure
the values are identical, but that again has given me no results

I'm at a loss with it, do you have any other thoughts on what
could be happening ?

What does this return in the Immediate Window?

?[Forms]![Invoice Totals]![invstart]

 

[Matt Spoljarevic]

Thanks Bob

I have done that and it returns the value exactly as I have it entered in
the textbox
i.e I entered "30000" in the textbox it returned "30000" in the immediate
window



"Bob Barrows" wrote in message
Press ctrl-g. A Module Window will open. At the bottom left is the Immediate
window. enter David's text and press Enter. Is the content of the textbox
returned into the Immediate window?

Hello David

I'm sorry, I'm a novice at this and I don't know how to use the
immediate window




"David-W-Fenton" wrote in message

I did the experiment of making the criteria Workorder.Invoice
Between 30002 and 30020 and it work perfectly

when I set it back to Between [Forms]![Invoice Totals]![invstart]
And [Forms]![Invoice Totals]![invfinish] and entered 30002 and
30020 to the text boxes, it returned no results again

I then tried declaring the parameters at the beginning of the
query as below, however this still returned no results.

I also tried changing the text boxes to Combo boxes populating
them with the invoice numbers from the Workorder table to ensure
the values are identical, but that again has given me no results

I'm at a loss with it, do you have any other thoughts on what
could be happening ?

What does this return in the Immediate Window?

?[Forms]![Invoice Totals]![invstart]

 

[Bob Barrows]

And the other one as well, I trust? If so, I'm out of ideas. One more look
at your sql statement ... hmmm:

SELECT Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type, Sum([Invoice Details].Extended) AS SumOfExtended,
Sum([Extended]*0.1) AS GST, Sum([Extended]*1.1) AS Total FROM Workorder
INNER JOIN [Invoice Details] ON Workorder.Invoice=[Invoice Details].Invoice
GROUP BY Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type HAVING (((Workorder.Invoice) Between [Forms]![Invoice
Totals]![invstart] And [Forms]![Invoice Totals]![invfinish]));


You should change that HAVING clause to a WHERE clause so that the filter is
performed before the records are grouped. Like this:

SELECT Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type, Sum([Invoice Details].Extended) AS SumOfExtended,
Sum([Extended]*0.1) AS GST, Sum([Extended]*1.1) AS Total FROM Workorder
INNER JOIN [Invoice Details] ON Workorder.Invoice=[Invoice Details].Invoice
WHERE (((Workorder.Invoice) Between [Forms]![Invoice
Totals]![invstart] And [Forms]![Invoice Totals]![invfinish]))
GROUP BY Workorder.Invoice, Workorder.[Invoice Date], Workorder.Client,
Workorder.Workorder, Workorder.[Site Address], Workorder.[Site Suburb],
Workorder.Type;

.... but that's a performance suggestion (the fewer records that need to be
grouped, the better the performance will be), not intended to solve your
problem unfortunately.

The HAVING clause is intended to allow the results of a grouping to be
filtered, and should only contain criteria that are based on grouped
results. For example, if you only wanted to return records having
Sum([Extended]*1.1) > 10000.

Unfortunately, the Design grid defaults to HAVING for criteria in a grouping
query. You have to change the "Having" to "Where" in the Totals row yourself
(or modify the sql directly, of course)

Thanks Bob

I have done that and it returns the value exactly as I have it
entered in the textbox
i.e I entered "30000" in the textbox it returned "30000" in the
immediate window



"Bob Barrows" wrote in message
Press ctrl-g. A Module Window will open. At the bottom left is the
Immediate window. enter David's text and press Enter. Is the content
of the textbox returned into the Immediate window?

Hello David

I'm sorry, I'm a novice at this and I don't know how to use the
immediate window




"David-W-Fenton" wrote in message

I did the experiment of making the criteria Workorder.Invoice
Between 30002 and 30020 and it work perfectly

when I set it back to Between [Forms]![Invoice Totals]![invstart]
And [Forms]![Invoice Totals]![invfinish] and entered 30002 and
30020 to the text boxes, it returned no results again

I then tried declaring the parameters at the beginning of the
query as below, however this still returned no results.

I also tried changing the text boxes to Combo boxes populating
them with the invoice numbers from the Workorder table to ensure
the values are identical, but that again has given me no results

I'm at a loss with it, do you have any other thoughts on what
could be happening ?

What does this return in the Immediate Window?

?[Forms]![Invoice Totals]![invstart]