Sorting based on field not in table

[Geir Baardsen]

Hi!
On a form I give information on produced invoices, sorted by date. Now
I want to have the user be able to choose a range based on the
dat-field. So I make two textboxes (FromDate and ToDate) and have the
user enter a range of dates in these within the dates given in the
datefield. How can I update the form showing only the daterange
(because these fields, FromDate and ToDate are not fields in the
underlying table)? Is there an example out there somewhere?

Kind regards,

[PC Datasheet]

Geir,

FromDate and ToDate do not have to be fields in the underlying table. Put the
following expression in the criteria of the InvoiceDate field:
Between Forms!NameOfYourForm!FromDate And Forms!NameOfYourForm!ToDate

Between ...And is inclusive; the two specified dates will be included in the
date range.

You can get the invoices for any day by just making the ToDate the same as the
FromDate.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Geir Baardsen" <al****@start.no> wrote in message
news:9e**************************@posting.google.c om...

Hi!
On a form I give information on produced invoices, sorted by date. Now
I want to have the user be able to choose a range based on the
dat-field. So I make two textboxes (FromDate and ToDate) and have the
user enter a range of dates in these within the dates given in the
datefield. How can I update the form showing only the daterange
(because these fields, FromDate and ToDate are not fields in the
underlying table)? Is there an example out there somewhere?

Kind regards,

[Geir Baardsen]

"PC Datasheet" <no****@nospam.spam> wrote in message news:<so******************@newsread3.news.atl.eart hlink.net>...

Geir,

FromDate and ToDate do not have to be fields in the underlying table. Put the
following expression in the criteria of the InvoiceDate field:
Between Forms!NameOfYourForm!FromDate And Forms!NameOfYourForm!ToDate

Between ...And is inclusive; the two specified dates will be included in the
date range.

You can get the invoices for any day by just making the ToDate the same as the
FromDate.

Sorry, I forgot to mention that I'm showing the data in a listbox
which is being filled in the form's onload event. So the date-field
will be nr.3 and I can access it through: =lstShowMyData.Column(2).

[Jim Allensworth]

On 7 Aug 2004 07:50:20 -0700, al****@start.no (Geir Baardsen) wrote:

"PC Datasheet" <no****@nospam.spam> wrote in message news:<so******************@newsread3.news.atl.eart hlink.net>...

Geir,

FromDate and ToDate do not have to be fields in the underlying table. Put the
following expression in the criteria of the InvoiceDate field:
Between Forms!NameOfYourForm!FromDate And Forms!NameOfYourForm!ToDate

Between ...And is inclusive; the two specified dates will be included in the
date range.

You can get the invoices for any day by just making the ToDate the same as the
FromDate.

Sorry, I forgot to mention that I'm showing the data in a listbox
which is being filled in the form's onload event. So the date-field
will be nr.3 and I can access it through: =lstShowMyData.Column(2).

Use the forms Filter property. You could use a button or the after
update event of the ToDate textbox.

Me.Filter = "InvoiceDate Between #" & Me.FromDate & "# And #" _
& Me.ToDate & "#"
Me.FilterOn = True

You would most likely want to validate the entries before applying the
filter.

- Jim

[Geir Baardsen]

Ji****@datacentricsolutions.com (Jim Allensworth) wrote in message news:<41**************@netnews.comcast.net>...

On 7 Aug 2004 07:50:20 -0700, al****@start.no (Geir Baardsen) wrote:
Use the forms Filter property. You could use a button or the after
update event of the ToDate textbox.

Me.Filter = "InvoiceDate Between #" & Me.FromDate & "# And #" _
& Me.ToDate & "#"
Me.FilterOn = True

You would most likely want to validate the entries before applying the
filter.

- Jim

Hi!
Thank you very much for answering. After a time in a sandbox I found
the following solution:

I have a form and a listbox which is retrieving data in the
form_OnLoad event. So I put
two textboxes, (txtFromDate, txtToDate) and a button, and wrote in the
buttons's OnClick
event:

Private Sub cmdSort_Click()
With Me.lstInvoice
.RowSource = ""
.ColumnCount = 10
.ColumnHeads = True
.ColumnWidths = "2cm;2cm;2cm;2cm;2cm;2cm;3,5cm;2cm;2cm;2cm;"
..RowSource = "SELECT tblOrd.OrdID AS Invoicenr, tblOrd.MyDate,
tblOrd.CustID, tblKunder.Custnr, tblOrd.Printed, tblOrd.Deleted,
tblOrd.DeletedDate, tblOrd.CreditMem, tblOrd.CreditMemnr,
tblOrd.CreditMemDate " _
& "FROM tblCustomer INNER JOIN tblOrd ON tblCustomer.CustID =
tblOrd.CustomerID " _
& "WHERE (((tblOrd.
MyDate) Between Forms!frmMyInvoices!txtFromDate And
Forms!frmMyInvoices!txtToDate));"
End With
End Sub

and of course I have a button that restores the listbox content.