How to Change Input String Data to Date/Time Date!!

[Guest]

Hi,
I don't know if what I am saying is feasible, but if it isn't please suggest
other ways to tackle my problem.

I have a billing system that produces bills for clients every month. The
user of the system should be able to view theses bills before they are
printed.

I have created a query to pull up the desired records from the 'Bookings'
table that match the criteria "Between [First Date] and [Last Date]". I was
wondering if there is any way for the user to input the Month's name and the
query to auto fill those to boxes!

The maonth's name will be in a list box control in a form. I'm thinking of
creating a table containing the name of each month and their start and end
date, but the problem is how does the query know which dates (from which
months) should it pull? Also, if I do this, the dates will need to be updated
every year, which I don't want the user to do manually!

Please help ASAP.
Thanks for taking time to read this.

 

[Guest]

First, your list box really should be a combo box. If you want to show the
months by name, that is no problem. What you will also want is the month
number. A table is not necessary. Just make the rowsource of the combo a
Value list, then set the list like:
1;Jan;2;Feb;3;Mar;4;Apr etc.
Make it a 2 column combo with the bound column being Column 1. Then make
your column width property something like 0";.5" (The 0 will make the column
with the month number invisible. The .5" is an example. Make it the width
you need to show the month name.

You want the bound column to be the month number so = Me.cboMonth will
return the number. If you want the name, it would be Me.cboMonth.Column(1)
(Combo box columns start with 0, so 0 wll give the number and 1 will give the
name) I know, setting the bound column to 1 sound incosistent, but setting
the bound column starts with 1 (go figure)

Now, to fill your Start and End Dates"
Me![Start Date] = DateSerial(Year(date), Me.CboMonth, 1)
Me.[End Date] = DateAdd("d", -1, DateSerial(Year(date), Me.CboMonth + 1, 1))

The Endate code may look a little confusing, but what it does is set the
date the the first day of the next month, then subtract 1, so you get the
last day of the selected month without having to know how many days are in
the mont.

 

[Guest]

Where exactly should I put the following code in?::

Me![Start Date] = DateSerial(Year(date), Me.CboMonth, 1)
Me.[End Date] = DateAdd("d", -1, DateSerial(Year(date), Me.CboMonth + 1, 1))

First, your list box really should be a combo box. If you want to show the
months by name, that is no problem. What you will also want is the month
number. A table is not necessary. Just make the rowsource of the combo a
Value list, then set the list like:
1;Jan;2;Feb;3;Mar;4;Apr etc.
Make it a 2 column combo with the bound column being Column 1. Then make
your column width property something like 0";.5" (The 0 will make the column
with the month number invisible. The .5" is an example. Make it the width
you need to show the month name.

You want the bound column to be the month number so = Me.cboMonth will
return the number. If you want the name, it would be Me.cboMonth.Column(1)
(Combo box columns start with 0, so 0 wll give the number and 1 will give the
name) I know, setting the bound column to 1 sound incosistent, but setting
the bound column starts with 1 (go figure)

Now, to fill your Start and End Dates"
Me![Start Date] = DateSerial(Year(date), Me.CboMonth, 1)
Me.[End Date] = DateAdd("d", -1, DateSerial(Year(date), Me.CboMonth + 1, 1))

The Endate code may look a little confusing, but what it does is set the
date the the first day of the next month, then subtract 1, so you get the
last day of the selected month without having to know how many days are in
the mont.

Hi,
I don't know if what I am saying is feasible, but if it isn't please suggest
other ways to tackle my problem.

I have a billing system that produces bills for clients every month. The
user of the system should be able to view theses bills before they are
printed.

I have created a query to pull up the desired records from the 'Bookings'
table that match the criteria "Between [First Date] and [Last Date]". I was
wondering if there is any way for the user to input the Month's name and the
query to auto fill those to boxes!

The maonth's name will be in a list box control in a form. I'm thinking of
creating a table containing the name of each month and their start and end
date, but the problem is how does the query know which dates (from which
months) should it pull? Also, if I do this, the dates will need to be updated
every year, which I don't want the user to do manually!

Please help ASAP.
Thanks for taking time to read this.

 

[Guest]

Sorry, I forgot to mention that. In the After Update event of the combo box.

Where exactly should I put the following code in?::

Me![Start Date] = DateSerial(Year(date), Me.CboMonth, 1)
Me.[End Date] = DateAdd("d", -1, DateSerial(Year(date), Me.CboMonth + 1, 1))

First, your list box really should be a combo box. If you want to show the
months by name, that is no problem. What you will also want is the month
number. A table is not necessary. Just make the rowsource of the combo a
Value list, then set the list like:
1;Jan;2;Feb;3;Mar;4;Apr etc.
Make it a 2 column combo with the bound column being Column 1. Then make
your column width property something like 0";.5" (The 0 will make the column
with the month number invisible. The .5" is an example. Make it the width
you need to show the month name.

You want the bound column to be the month number so = Me.cboMonth will
return the number. If you want the name, it would be Me.cboMonth.Column(1)
(Combo box columns start with 0, so 0 wll give the number and 1 will give the
name) I know, setting the bound column to 1 sound incosistent, but setting
the bound column starts with 1 (go figure)

Now, to fill your Start and End Dates"
Me![Start Date] = DateSerial(Year(date), Me.CboMonth, 1)
Me.[End Date] = DateAdd("d", -1, DateSerial(Year(date), Me.CboMonth + 1, 1))

The Endate code may look a little confusing, but what it does is set the
date the the first day of the next month, then subtract 1, so you get the
last day of the selected month without having to know how many days are in
the mont.

Hi,
I don't know if what I am saying is feasible, but if it isn't please suggest
other ways to tackle my problem.

I have a billing system that produces bills for clients every month. The
user of the system should be able to view theses bills before they are
printed.

I have created a query to pull up the desired records from the 'Bookings'
table that match the criteria "Between [First Date] and [Last Date]". I was
wondering if there is any way for the user to input the Month's name and the
query to auto fill those to boxes!

The maonth's name will be in a list box control in a form. I'm thinking of
creating a table containing the name of each month and their start and end
date, but the problem is how does the query know which dates (from which
months) should it pull? Also, if I do this, the dates will need to be updated
every year, which I don't want the user to do manually!

Please help ASAP.
Thanks for taking time to read this.