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

On Sun, 3 Jul 2011 11:13:18 +1000, "Matt Spoljarevic" <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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
news:(E-Mail Removed)...

On Sun, 3 Jul 2011 11:13:18 +1000, "Matt Spoljarevic" <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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

On 7/3/2011 7:37 AM, Matt Spoljarevic wrote:
> 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
> news:(E-Mail Removed)...
>
> On Sun, 3 Jul 2011 11:13:18 +1000, "Matt Spoljarevic" <(E-Mail Removed)>
> wrote:
>
>> 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

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 news:iusi97$jt6$(E-Mail Removed)...

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

On 7/3/2011 7:37 AM, Matt Spoljarevic wrote:
> 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
> news:(E-Mail Removed)...
>
> On Sun, 3 Jul 2011 11:13:18 +1000, "Matt Spoljarevic" <(E-Mail Removed)>
> wrote:
>
>> 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.

On Sun, 3 Jul 2011 11:13:18 +1000, "Matt Spoljarevic" <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

"Matt Spoljarevic" <(E-Mail Removed)> wrote in
newsuNQp.8002$(E-Mail Removed):

> 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]

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

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
news:Xns9F19D40F2F4B2f99a49ed1d0c49c5bbb2@74.209.136.91...

"Matt Spoljarevic" <(E-Mail Removed)> wrote in
newsuNQp.8002$(E-Mail Removed):

> 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]

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

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
news:(E-Mail Removed)...

On Sun, 3 Jul 2011 11:13:18 +1000, "Matt Spoljarevic" <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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?

Matt Spoljarevic wrote:
> 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
> news:Xns9F19D40F2F4B2f99a49ed1d0c49c5bbb2@74.209.136.91...
>
> "Matt Spoljarevic" <(E-Mail Removed)> wrote in
> newsuNQp.8002$(E-Mail Removed):
>
>> 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]