List Box on Form

[Jonny]

Hello,

I have a List Box in my DB on a form, which when the form is opened,
the list box displays the date relating to the records. (Date, Person
etc)

How can I get it so that when I open the form, ONLY the records with a
date within the current month are shown.

Thanks in advance.

J.

[WindAndWaves]

for the lookup box (using the properties)
set to two columns
set the column widths to 0;10cm

set the rowsource to

SELECT [your ID field] AS 1, [your description] AS 2
FROM [your table]
WHERE (((DatePart("m",[your date field]))=DatePart("m",Date())));

and make sure that you replace the 'your fields' and table with the proper
names.

Any questions, please ask.

[fredg]

On 15 Jun 2004 03:39:28 -0700, Jonny wrote:

Hello,

I have a List Box in my DB on a form, which when the form is opened,
the list box displays the date relating to the records. (Date, Person
etc)

How can I get it so that when I open the form, ONLY the records with a
date within the current month are shown.

Thanks in advance.

J.

I assume you mean the current month of the just the current year, not
previous years as well.

Base the List Box Row Source on a Query.
Set the criteria on the Date Field to:
Format([DateField],"mm yyyy")=Format(Date(),"mm yyyy")

Only June 2004 dates will appear this month.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[John Winterbottom]

"Jonny" <jo**********@yahoo.com> wrote in message
news:14*************************@posting.google.co m...

Hello,

I have a List Box in my DB on a form, which when the form is opened,
the list box displays the date relating to the records. (Date, Person
etc)

How can I get it so that when I open the form, ONLY the records with a
date within the current month are shown.

you shouldn't use "Date" as a column name. Try something like this:

select hireDate, personName
from jonny
where hireDate >= CDate(Month(Date()) & "/01/" & Year(Date()))
and hireDate < DateAdd ("m",1,CDate(Month(Date()) & "/01/" & Year(Date())))

[Katie]

J,
In the properties of your list box set your row source to run from a
query based on the appropriate data table. In the query select the
fields you want to display in the order you wish them to appear in
your list box.

Add an exrta field into your query:
Field: DatePart("m",[DateField])
Criteria: DatePart("m",Date())
Untick the Show: check box

Baisically, you're limiting the list to only records where
month of [DateField] = month of the current date.

Similarly, you could use any date value in place of Date(), a field on
your form for example.

Hope this helps

K

jo**********@yahoo.com (Jonny) wrote in message news:<14*************************@posting.google.c om>...

Hello,

I have a List Box in my DB on a form, which when the form is opened,
the list box displays the date relating to the records. (Date, Person
etc)

How can I get it so that when I open the form, ONLY the records with a
date within the current month are shown.

Thanks in advance.

J.

[Jonny]

ka********@northernrock.co.uk (Katie) wrote in message news:<43*************************@posting.google.c om>...

J,
In the properties of your list box set your row source to run from a
query based on the appropriate data table. In the query select the
fields you want to display in the order you wish them to appear in
your list box.

Add an exrta field into your query:
Field: DatePart("m",[DateField])
Criteria: DatePart("m",Date())
Untick the Show: check box

Baisically, you're limiting the list to only records where
month of [DateField] = month of the current date.

Similarly, you could use any date value in place of Date(), a field on
your form for example.

Hope this helps

K

jo**********@yahoo.com (Jonny) wrote in message news:<14*************************@posting.google.c om>...

Hello,

I have a List Box in my DB on a form, which when the form is opened,
the list box displays the date relating to the records. (Date, Person
etc)

How can I get it so that when I open the form, ONLY the records with a
date within the current month are shown.

Thanks in advance.

J.

Hello,

I have created a query in the rowsource of the list box, and it works
fine from within the query builder.
However, it doesn't work when I open the form.

I have a feeling it is because when the form it opened, it is already
set to do the following :

----------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim strsql As String
strsql = "SELECT ID,Person,Task,Area,DateFrom,DateTo FROM TblMain
WHERE 1 = 1"
If Len(TxtDateFrom) > 0 Then
strsql = strsql & " AND DateFrom like '*" & TxtDateFrom & "*'"
End If
If Len(TxtPerson) > 0 Then
strsql = strsql & " AND Person like '*" & TxtPerson & "*'"
End If
If Len(TxtTask) > 0 Then
strsql = strsql & " AND Task like '*" & TxtTask & "*'"
End If
If Len(TxtDateTo) > 0 Then
strsql = strsql & " AND DateTo like '*" & TxtDateTo & "*'"
End If

strsql = strsql & " Order by Person;"
Lstcustomers.RowSource = strsql
Call Countrecords
LblDateTime.Caption = Time
PersonSort.Visible = True
TaskSort.Visible = False
AreaSort.Visible = False
DateFromSort.Visible = False
DateToSort.Visible = False
Call CmdClear_Click
End Sub
---------------------------------------

Also, just to give you bit of background as to the code itself :

1) WHERE 1=1 is used because on this list form I have 3 boxes at the
top. (criteria boxes if you like). I put my name in one of these
boxes, then it filters the list box to only show my records. Then I
filer further by choosing a certain person, and it shows my records
with this person.

Without the WHERE 1=1, it would filter out my records, but when I then
chose a person, it showed others people's records with that person
aswell.

2) I have tried to put this on the onLoad event, but I got a message
saying "The expression On Load you entered as the event property
setting produced the following error : procedure declaration does not
match description of event or procedure having the same name."

I then choose ok to this, the form comes up (with the correct
records), then I get the same message again, but relating to the on
timer instead of the on load (on timer only populates a lable with
todays date and time)
I hope this makes sense, if not then please let me know.

Thanks.

J.