How to Using DateAdd in Query2

M

Marc

Hi,

Sorry Beter place data to understand...

Could someone help me with next problem,


first table with start dates and days to add

ID Name StartDate Days
5 Louise 11-jul-08 2
2 Robert 03-nov-08 9
6 Bram 14-nov-08 5



The query need to generate the new dates like below


ID Name StartDate
5 Louise 11-jul-08
5 Louise 12-jul-08
2 Robert 13-jul-08
2 Robert 14-jul-08
2 Robert 15-jul-08
2 Robert 16-jul-08
2 Robert 17-jul-08
2 Robert 18-jul-08
2 Robert 19-jul-08
2 Robert 20-jul-08
2 Robert 21-jul-08
6 Bram 14-nov-08
6 Bram 15-nov-08
6 Bram 16-nov-08
6 Bram 17-nov-08
6 Bram 18-nov-08


Many thanks in advance

Marc.
 
K

Ken Snell \(MVP\)

Before we get into suggestions for queries, tell us what you want to do with
the results of the query? Do you want to show the results on a form or
report? Do you want to populate a table? Do you want to export the results
to a text file or EXCEL file?
 
M

Marc

Hi Ken,

Thanks for answering,
The results is to populate a combobox

Now I have written a function to add all data to an temporary table and from
there i can get the wright data for my combobox

I am always interested if the solution can be mede by a query

Many tanks in advance,
Marc
 
J

John Spencer

If you have a table with sequential numbers in it,
Table: Iotas
Field: Iota (numbers from 1 to the max needed) - unique

SELECT YourTable.[Id Name]
, DateAdd("d",Iotas.Iota -1,StartDate) as theDate
FROM Iotas, YourTable
WHERE Iotas.Iota <= YourTable.Days

The number table is handy to have and you only need to create as many
records in it as the highest number you want to use. In this example
you would only need 9 records - if that were your maximum value in the
Days field.

You can actually use just one table with the numbers from 0 to 9 and
then use the table multiple times (aliased) to get any number you want

Iotas1000.Iota * 1000 + Iotas100.Iota * 100 + Iotas10.Iota*10 +
Iotas.Iota would generate every number from 0 to 9999

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top