generate date

[hair]

Hi guys, I'm a little new on this+ sorry about my english.
So : I have a table with clients and I want in this table to generate for each client the 7 dates of the week, based on the user's input (for instance the user chooses week 25 and a query populates my table with the weekdays). How is that suppose to be done in Access? Thanks for help and sorry if my question sounds stupid.



Dan

 

[Rich]

What is your reason for filling a table with dates that might not be used? Will the calControl not do what you want?

 

[hair]

It's about clients of the company and each day they have a security agent. Let's say as an average 6 days a week are busy. The eventualy empty fields can be deleted after, but it wouldn't be too much to do. I'm not sure that's the best way to do it but I don't find other way.

 

[hair]

Meanwhile my problem is different. So I generate the date some other way and it works (I found the answer in the forum). Now, I have the date in the table, but I want to display on the form the day of the week that correspond to this date. How do I do that? Thanks in advance

 

[Rich]

Add an unbound text box, set its control source to =[YourDateField], set the format on the property sheet to dddd

 

[hair]

Thanks Rich, I should of think of that.
I owe you a beer (if u ever come to Strasbourg )

 

[Rich]

It's tempting

 

[ColinEssex]

I'm going to Strasbourg in October Rich _ I'll have a beer for you

Col

 

[Rich]

No! bring it back

 

[hair]

I'm gonna raise the quantity to 2 beers, but the risk is that each time I need help on the forum somebody will ask for beer so I'm gonna be soon out of resources.

 

[Mile-O]

Then offer l'escargot, and see how many take you up on the offer.

 

[Rich]

I'm gonna raise the quantity to 2 beers, but the risk is that each time I need help on the forum somebody will ask for beer so I'm gonna be soon out of resources.

With the price of beer over there compared to here, I don't think so

 

[hair]

what's the price for a regular beer in Eden, anyway?
and because I feel you guys in the right mood, I ask the last question (today)
-- In the form that covers the table x, I have a date field. Which have a date entered by the user. How can I fill the next 6 fields with the next 6 dates ? (suppose it's 01/01/2070 and I want the next six fields like 02/01/2070, 03/...etc). I can't manage to figure it out and I didn't find it on the forum neither.
Thanks in advance

PS My wife says the escargots are good with garlic but this stuff is really disgusting. Tried it though. Mais 'les huitres', (the oysters), are my favourites. And anyway if you're talking about good things in France, it's not the beer who's in charge of that, but the wine. And that's a fact (as not being french I say I am quite objective in this issue, and I've worked hard to have an opinion on that)

 

[Mile-O]

The DateAdd() function.

 

[Rich]

6 fields or records?

 

[hair]

records actualy. Again, my english might not be good enough. So I input a date and after that to be able to add automatically 6 other records with the 6 next dates. Since the last answer Im trying to figure it out how to use dateadd but no results yet. What I want is that the guy who's doing the planning to have the dates generated and to complete the rest (add from combos the name of the client and the name of the agent who work that day for the client) . I'm stocked in the form - I can't manage to say something like ' date = date+1' for the next record. Is it by chance something that involves the word 'clone'?

 

[Mile-O]

You only need to use the DateAdd function in a query where you calculate the extra dates.

You shouldn't store calculated values in your table.

By making a query on the table you have, you can add further fields in your query that do the calculations for you.

Have a look at the table, then the query in this database.

 

[Rich]

I think hair wants to actually generate records so that users can enter corresponding data into the fields Mile.
It actually requires vba to do this and I have to go to work for a few hours so I can't write it for you yet.
I'm sure it's been posted here before though, a search may reveal it

 

[hair]

thanks a lot

 

[Mile-O]

Ah, got what's meant now.

Here's a quick VBA sample, to give an idea.

Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs= db.OpenRecordset("YourTable")

Dim intCounter As Integer
For intCounter = 1 To 6
   With rs
      .AddNew
      .Fields("YourDateField") = DateAdd("d", intCounter, Me.txtYourDateTextbox)
      .Update
   End With
Next intCounter

rs.Close
db.Close