convert dates to Series

  • Thread starter Thread starter a
  • Start date Start date
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
 
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...
 
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
 
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...
 
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...
 
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]
 
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
 
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...
 
Sorry about that, in the first query (qry_Numbers) replace the references
to intValue with int_Value.

Dale
 
Back
Top