convert dates to Series

A

a

thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
 
A

a

Thank you for your help and answer:
If I will write the dates will take a long time:
1/1/2004 to 30/12/2004 and so on and the mistake will be hugeThe question know Is:
If I have combo box on a (form) and user enter dates in this combo
box....now (vba) or SQL or Query or validation rule ... make CHECK to see is
this date exist or not in the table1 or when user enter date between begin
date and end date now (vba) or SQL or Query or validation rule CHECK to see
is this date exist or not and display msgbox...




Duane Hookom said:
You can't create the extra records using straight SQL. The easiest method is
to create a table of dates and then creating a query where the criteria
under the date field is
Between [Begin Date] and [End Date]

--
Duane Hookom
MS Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
 
A

a

Thank you for your help and answer:
when run the query this message appear:
1
enter paramter value
ones.intvalue
2
enter paramter value
tens.intvalue
3
enter paramter value
hundreds.intvalue

thank you
 
A

a

Thank you for your help and answer:
If I will write the dates will take a long time:
1/1/2004 to 30/12/2004 and so on and the mistake will be hugeThe question know Is:
If I have combo box on a (form) and user enter dates in this combo
box....now (vba) or SQL or Query or validation rule ... make CHECK to see is
this date exist or not in the table1 or when user enter date between begin
date and end date now (vba) or SQL or Query or validation rule CHECK to see
is this date exist or not and display msgbox...




Duane Hookom said:
You can't create the extra records using straight SQL. The easiest method is
to create a table of dates and then creating a query where the criteria
under the date field is
Between [Begin Date] and [End Date]

--
Duane Hookom
MS Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
 
A

a

Thank you for your help and answer:
If I will write the dates will take a long time:
1/1/2004 to 30/12/2004 and so on and the mistake will be hugeThe question know Is:
If I have combo box on a (form) and user enter dates in this combo
box....now (vba) or SQL or Query or validation rule ... make CHECK to see is
this date exist or not in the table1 or when user enter date between begin
date and end date now (vba) or SQL or Query or validation rule CHECK to see
is this date exist or not and display msgbox...




Duane Hookom said:
You can't create the extra records using straight SQL. The easiest method is
to create a table of dates and then creating a query where the criteria
under the date field is
Between [Begin Date] and [End Date]

--
Duane Hookom
MS Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
 
D

Duane Hookom

You can't create the extra records using straight SQL. The easiest method is
to create a table of dates and then creating a query where the criteria
under the date field is
Between [Begin Date] and [End Date]
 
D

Dale Fye

what is the maximum difference between your BeginDate and EndDate?

Yes, this is possible.

1. Create a table (tbl_Numbers) with on field (intValues). Add the numbers
0-9 to this table
2. Create a query (qry_Numbers) that looks something like the following, to
give you numbers that range from 0 through 1000 (or whatever you expect the
maximum difference to be between the two dates mentioned.

SELECT Ones.intValue + (Tens.intValue * 10) + Hundreds.intValue * 100 as
intNumber
FROM tbl_Numbers as Ones, tbl_Numbers as Tens, tbl_Numbers as Hundreds

3. Now write the query to get your result set

SELECT DateAdd("d", intNumber, BeginDate) as ConvertedDate
, Company
, ID
FROM Table1, qry_Numbers
WHERE DateAdd("d", intNumber, BeginDate) <= EndDate
Order by ID, DateAdd("d", intNumber, BeginDate)

HTH
Dale
 
D

Duane Hookom

You just need one table of dates. If you want, you can open Excel and do
this which should take about 2 minutes. Then copy the dates to an Access
table.

I don't understand your "question now..."
--
Duane Hookom
MS Access MVP


a said:
Thank you for your help and answer:
If I will write the dates will take a long time:
1/1/2004 to 30/12/2004 and so on and the mistake will be hugeThe question know Is:
If I have combo box on a (form) and user enter dates in this combo
box....now (vba) or SQL or Query or validation rule ... make CHECK to see is
this date exist or not in the table1 or when user enter date between begin
date and end date now (vba) or SQL or Query or validation rule CHECK to see
is this date exist or not and display msgbox...




Duane Hookom said:
You can't create the extra records using straight SQL. The easiest
Click to expand...
method
is
to create a table of dates and then creating a query where the criteria
under the date field is
Between [Begin Date] and [End Date]

--
Duane Hookom
MS Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Sorry about that, in the first query (qry_Numbers) replace the references
to intValue with int_Value.

Dale
 

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

Similar Threads

Advanced problem using auto-number and datediff to find readmissio 1
convert date to Series 12
Delay counting 1
Access and duplicates 2
show top values 5
Date Expressions & Calculations 3
Group individual dates into seperate months 3
Transpose rows to columns in Access. 3

Top