No of days

[Guest]

I have a FP2000 form that populates a table for somone who wishes to book
holidays. I have a simple table at the moment that records the date and
time, e.g. 01/08/2005 13:33:00 for a particular person.

Here is the table schema:

ID :: Autonumber, 4
Event_Name :: Text, Joe Bloggs
Date :: Date/Time, 08/09/2005 01:00
EndDate :: Date/Time
Location :: Text
Description :: Memo, I will be on holiday
Category :: Text, Holiday
noofdays :: 5

I have a new record for each event that occurs. What I need to do is to
create a query that replacates the record x number of times depending on the
number of days selected. For instance, if I select 5 in my table as the
noofdays, then I will have in my Query x5 records that are identical to the
one above corresponding to the days 09/09/2005, 10/09/2005 etc.... for 5 days.

Please help!

skc

 

[Stefan B Rusynko]

The field named "Date" is invalid
(Date is a reserved name in Access and ASP)
Change it to say Start Date

Not sure why you want to do the record replication
If you replicate the record 5 times, each time you would probably need to change the start date and duration (decrementing by 1)
- you could do that w/ server side code but it is very inefficient and unnecessary

I suspect you what are trying to add the EndDate value (since it has no value in your example record) to reflect the noofdays when
they submit the form
- can be done from the form before you write to the DB in your ASP code as
which say has only the StartDate and Duration as values as:

<% EndDate = DateAdd( "d", CInt(Request.Form("noofdays"), CDate(Request.Form("StartDate") )

And then you should just be querying that single record for a date range using say
StartDate >= StartDate AND EndDate <= EndDate

If sending to the DB direct from the form using say the DBRW you would need a client side script to add the hidden form field
EndDate field value based on the StartDate and Duration field changes
IMHO
Dates are Best done w/a date picker script to avoid bad user date value entries
See http://www.javascriptkit.com/script/script2/tengcalendar.shtml


--




|I have a FP2000 form that populates a table for somone who wishes to book
| holidays. I have a simple table at the moment that records the date and
| time, e.g. 01/08/2005 13:33:00 for a particular person.
|
| Here is the table schema:
|
| ID :: Autonumber, 4
| Event_Name :: Text, Joe Bloggs
| Date :: Date/Time, 08/09/2005 01:00
| EndDate :: Date/Time
| Location :: Text
| Description :: Memo, I will be on holiday
| Category :: Text, Holiday
| noofdays :: 5
|
| I have a new record for each event that occurs. What I need to do is to
| create a query that replacates the record x number of times depending on the
| number of days selected. For instance, if I select 5 in my table as the
| noofdays, then I will have in my Query x5 records that are identical to the
| one above corresponding to the days 09/09/2005, 10/09/2005 etc.... for 5 days.
|
| Please help!
|
| skc

 

[Kevin Spencer]

Change it to say Start Date

It's also a good idea to avoid spaces in table and column names. How about
"StartDate" instead?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.