Matching on Dates & times

[Bunky]

I have a third party application I link to via and OBDC connection to
retrieve call data information. I also have another application that we
import the forecast data from. The forecast information contains a timestamp
that I have been able to break down into a MM/DD/YY and then further get down
to the Time interval using TimeValue([timestamp]) The call data has a date
field that is defined as a text on the import and the time interval is
defined as a number. I used CDate on the text field to get MM/DD/YY then
went further using TimeValue([datefield]) to get the time interval. When I
try to join these two tables, some of the data is missed. I think my problem
is the dates and Time intervals but I really do not know how to define them
so they can be joined. Ideas?

 

[KARL DEWEY]

Try using DateValue instead of CDate.

 

[Bunky]

Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import as a
number with a field size of Long Integer.

Try using DateValue instead of CDate.

--
Build a little, test a little.

I have a third party application I link to via and OBDC connection to
retrieve call data information. I also have another application that we
import the forecast data from. The forecast information contains a timestamp
that I have been able to break down into a MM/DD/YY and then further get down
to the Time interval using TimeValue([timestamp]) The call data has a date
field that is defined as a text on the import and the time interval is
defined as a number. I used CDate on the text field to get MM/DD/YY then
went further using TimeValue([datefield]) to get the time interval. When I
try to join these two tables, some of the data is missed. I think my problem
is the dates and Time intervals but I really do not know how to define them
so they can be joined. Ideas?

 

[Douglas J. Steele]

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import as
a
number with a field size of Long Integer.

Try using DateValue instead of CDate.

--
Build a little, test a little.

I have a third party application I link to via and OBDC connection to
retrieve call data information. I also have another application that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has a
date
field that is defined as a text on the import and the time interval is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think my
problem
is the dates and Time intervals but I really do not know how to define
them
so they can be joined. Ideas?

 

[Bunky]

Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import as
a
number with a field size of Long Integer.

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection to
retrieve call data information. I also have another application that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has a
date
field that is defined as a text on the import and the time interval is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think my
problem
is the dates and Time intervals but I really do not know how to define
them
so they can be joined. Ideas?

 

[Douglas J. Steele]

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[KARL DEWEY]

Does the query run if you leave off the WHERE?

--
Build a little, test a little.

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

It will execute but it will not give me good results.

Does the query run if you leave off the WHERE?

--
Build a little, test a little.

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[KARL DEWEY]

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.

It will execute but it will not give me good results.

Does the query run if you leave off the WHERE?

--
Build a little, test a little.

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.

It will execute but it will not give me good results.

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

It will execute but it will not give me good results.

That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[KARL DEWEY]

Formating the table does nothing except change the display. It has NO effect
on how the data is outputted in a query.
You need to convert in a query and then use the queries and not the tables
when you join the data,

Just a currious question have you actually looked at the data to be sure
there are records that match?

--
Build a little, test a little.

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.


:

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

I just changed my application to use the queries with the same results; I am
still missing 8:30 AM Then I have 8:45 AM and 9:00 AM then miss 9:15 AM.

Yes, I have looked at the data. Many times! I had to convert the data from
the forecast table from a text to a good date. I used the CDate Function and
created a new table with the created date element defined as a date/time.
The Time Interval is defined as a date/time from the start and looks fine on
the original table. It comes in with 8:30 AM etc.

When I look at the table, I should be able to see all the formatting etc.
correct? How can I look at the table and just see data? In the olden times,
you could request a data dump of the file. Is there a way to do that with
Access?

Formating the table does nothing except change the display. It has NO effect
on how the data is outputted in a query.
You need to convert in a query and then use the queries and not the tables
when you join the data,

Just a currious question have you actually looked at the data to be sure
there are records that match?

--
Build a little, test a little.

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

:

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.


:

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[KARL DEWEY]

How can I look at the table and just see data?
Run a straigh query, no formats but just displaying the field data.

Is it possible the 8:30 AM is something else like 8:30:05 AM? that should
show up in the straight query.

--
Build a little, test a little.

I just changed my application to use the queries with the same results; I am
still missing 8:30 AM Then I have 8:45 AM and 9:00 AM then miss 9:15 AM.

Yes, I have looked at the data. Many times! I had to convert the data from
the forecast table from a text to a good date. I used the CDate Function and
created a new table with the created date element defined as a date/time.
The Time Interval is defined as a date/time from the start and looks fine on
the original table. It comes in with 8:30 AM etc.

When I look at the table, I should be able to see all the formatting etc.
correct? How can I look at the table and just see data? In the olden times,
you could request a data dump of the file. Is there a way to do that with
Access?

Formating the table does nothing except change the display. It has NO effect
on how the data is outputted in a query.
You need to convert in a query and then use the queries and not the tables
when you join the data,

Just a currious question have you actually looked at the data to be sure
there are records that match?

--
Build a little, test a little.

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

:

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

:

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.


:

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

Okay - The forecast table shows
#fields:date period
10072009 5:45:00 PM
10072009 5:30:00 PM
10072009 5:15:00 PM
10072009 5:00:00 PM
10072009 4:45:00 PM

The Call Data table looks like this
Timestamp
Wednesday October 07 2009 8:00:00 AM
Wednesday October 07 2009 8:15:00 AM
Wednesday October 07 2009 8:30:00 AM
Wednesday October 07 2009 8:45:00 AM
Wednesday October 07 2009 9:00:00 AM

What would you do to get the forecast tbl and the call tbl to be able to
successfully join? I did not show all of either table but you have to take
my word for it they should match up. The call data has the timestamp and
the forecast has the date and period in two separate fields.

Run a straigh query, no formats but just displaying the field data.

Is it possible the 8:30 AM is something else like 8:30:05 AM? that should
show up in the straight query.

--
Build a little, test a little.

I just changed my application to use the queries with the same results; I am
still missing 8:30 AM Then I have 8:45 AM and 9:00 AM then miss 9:15 AM.

Yes, I have looked at the data. Many times! I had to convert the data from
the forecast table from a text to a good date. I used the CDate Function and
created a new table with the created date element defined as a date/time.
The Time Interval is defined as a date/time from the start and looks fine on
the original table. It comes in with 8:30 AM etc.

When I look at the table, I should be able to see all the formatting etc.
correct? How can I look at the table and just see data? In the olden times,
you could request a data dump of the file. Is there a way to do that with
Access?

Formating the table does nothing except change the display. It has NO effect
on how the data is outputted in a query.
You need to convert in a query and then use the queries and not the tables
when you join the data,

Just a currious question have you actually looked at the data to be sure
there are records that match?

--
Build a little, test a little.


:

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

:

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

:

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.


:

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[KARL DEWEY]

Create this query as Forecast_Merge --
SELECT DateSerial(Right([Date],4),Left([Date],2),Right(Left([Date],4),2)) +
+CVDate([Date]) AS Forecast_Date
FROM Forecast;

Then run this query to check for match records --
SELECT [Forecast_Merge].[Forecast_Date], [Call Data].[Timestamp]
FROM [Forecast_Merge] INNER JOIN [Call Data] ON
[Forecast_Merge].[Forecast_Date] = [Call Data].[Timestamp];

If the above works then edit Forecast_Merge to include the other fields and
use in your query instead of Forecast table.

--
Build a little, test a little.

Okay - The forecast table shows
#fields:date period
10072009 5:45:00 PM
10072009 5:30:00 PM
10072009 5:15:00 PM
10072009 5:00:00 PM
10072009 4:45:00 PM

The Call Data table looks like this
Timestamp
Wednesday October 07 2009 8:00:00 AM
Wednesday October 07 2009 8:15:00 AM
Wednesday October 07 2009 8:30:00 AM
Wednesday October 07 2009 8:45:00 AM
Wednesday October 07 2009 9:00:00 AM

What would you do to get the forecast tbl and the call tbl to be able to
successfully join? I did not show all of either table but you have to take
my word for it they should match up. The call data has the timestamp and
the forecast has the date and period in two separate fields.

How can I look at the table and just see data?

Run a straigh query, no formats but just displaying the field data.

Is it possible the 8:30 AM is something else like 8:30:05 AM? that should
show up in the straight query.

--
Build a little, test a little.

I just changed my application to use the queries with the same results; I am
still missing 8:30 AM Then I have 8:45 AM and 9:00 AM then miss 9:15 AM.

Yes, I have looked at the data. Many times! I had to convert the data from
the forecast table from a text to a good date. I used the CDate Function and
created a new table with the created date element defined as a date/time.
The Time Interval is defined as a date/time from the start and looks fine on
the original table. It comes in with 8:30 AM etc.

When I look at the table, I should be able to see all the formatting etc.
correct? How can I look at the table and just see data? In the olden times,
you could request a data dump of the file. Is there a way to do that with
Access?

:

Formating the table does nothing except change the display. It has NO effect
on how the data is outputted in a query.
You need to convert in a query and then use the queries and not the tables
when you join the data,

Just a currious question have you actually looked at the data to be sure
there are records that match?

--
Build a little, test a little.


:

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

:

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

:

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.


:

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[John W. Vinson]

Okay - The forecast table shows
#fields:date period
10072009 5:45:00 PM
10072009 5:30:00 PM
10072009 5:15:00 PM
10072009 5:00:00 PM
10072009 4:45:00 PM

The Call Data table looks like this
Timestamp
Wednesday October 07 2009 8:00:00 AM
Wednesday October 07 2009 8:15:00 AM
Wednesday October 07 2009 8:30:00 AM
Wednesday October 07 2009 8:45:00 AM
Wednesday October 07 2009 9:00:00 AM

What would you do to get the forecast tbl and the call tbl to be able to
successfully join? I did not show all of either table but you have to take
my word for it they should match up. The call data has the timestamp and
the forecast has the date and period in two separate fields.

One possible problem is that a Date/Time value is a Double Float number, the
number of days and fractions of a day since midnight, December 30, 1899. As
such, it's actually accurate to better than a microsecond:

?Cdbl(#10/8/09 11:00:00#);CDbl(#10/8/09 11:00:01#)
40094.4583333333 40094.4583449074

So it's quite possible that your stored date/time value and the one calculated
from your two separate fields are in fact NOT identical, differing by .00005
seconds or so!

You may need to use a less efficient process: use the Format() function to
convert the two date/time values to a text string such as "10/08/2009 10:15"
and compare those text strings - at the appropriate level of precision.

 

[Bunky]

Hey Karl!

I copied and pasted your code and changed it where it needed for names and
got all #Error on each row. Here is my code.

SELECT CTActiveForecast100809.[#fields:date],
DateSerial(Right([#fields:date],4),Left([#fields:date],2),Right(Left([#fields:date],4),2))++CVDate([#fields:date]) AS Forecast_Date
FROM CTActiveForecast100809;

What did I do in error?

Create this query as Forecast_Merge --
SELECT DateSerial(Right([Date],4),Left([Date],2),Right(Left([Date],4),2)) +
+CVDate([Date]) AS Forecast_Date
FROM Forecast;

Then run this query to check for match records --
SELECT [Forecast_Merge].[Forecast_Date], [Call Data].[Timestamp]
FROM [Forecast_Merge] INNER JOIN [Call Data] ON
[Forecast_Merge].[Forecast_Date] = [Call Data].[Timestamp];

If the above works then edit Forecast_Merge to include the other fields and
use in your query instead of Forecast table.

--
Build a little, test a little.

Okay - The forecast table shows
#fields:date period
10072009 5:45:00 PM
10072009 5:30:00 PM
10072009 5:15:00 PM
10072009 5:00:00 PM
10072009 4:45:00 PM

The Call Data table looks like this
Timestamp
Wednesday October 07 2009 8:00:00 AM
Wednesday October 07 2009 8:15:00 AM
Wednesday October 07 2009 8:30:00 AM
Wednesday October 07 2009 8:45:00 AM
Wednesday October 07 2009 9:00:00 AM

What would you do to get the forecast tbl and the call tbl to be able to
successfully join? I did not show all of either table but you have to take
my word for it they should match up. The call data has the timestamp and
the forecast has the date and period in two separate fields.

How can I look at the table and just see data?
Run a straigh query, no formats but just displaying the field data.

Is it possible the 8:30 AM is something else like 8:30:05 AM? that should
show up in the straight query.

--
Build a little, test a little.


:

I just changed my application to use the queries with the same results; I am
still missing 8:30 AM Then I have 8:45 AM and 9:00 AM then miss 9:15 AM.

Yes, I have looked at the data. Many times! I had to convert the data from
the forecast table from a text to a good date. I used the CDate Function and
created a new table with the created date element defined as a date/time.
The Time Interval is defined as a date/time from the start and looks fine on
the original table. It comes in with 8:30 AM etc.

When I look at the table, I should be able to see all the formatting etc.
correct? How can I look at the table and just see data? In the olden times,
you could request a data dump of the file. Is there a way to do that with
Access?

:

Formating the table does nothing except change the display. It has NO effect
on how the data is outputted in a query.
You need to convert in a query and then use the queries and not the tables
when you join the data,

Just a currious question have you actually looked at the data to be sure
there are records that match?

--
Build a little, test a little.


:

Karl / Doug

I was looking at some documentation we have for the third party files that
we are using. It looks like our files are generated using XML format. The
Date format that is being provided is mmddyyyy and the time format is on 24
hrs. This is for the Forecast file. Does this help finding an answer?

:

Hi Karl - - - It means the Join from the Forecast table to the Call Data
table does not include all of the time intervals. What should happen is this.
I am joining on date, Period, and CTID. I can see a good date, a good
period, and a good CTid on both tables but they do not join up or match in
the query. I have formatted the date on both tables trying to find why it is
not matching up but no help. The CTid is defined as a number and the period
is defined as date/time. The period shows okay but who knows? The period is
time showing as HH:mm:ss AM/PM.

:

It will execute but it will not give me good results.
That is not very helpful to anyone.

What does 'will not give me good results' mean? Does it mean that the data
is correct except for too many records or is there other things wrong with
the data?

--
Build a little, test a little.


:

It will execute but it will not give me good results.

:

Does the query run if you leave off the WHERE?

--
Build a little, test a little.


:

The SQL is as follows - Test-it is the Forecast data and ScoreCardwCT is the
call data
SELECT [test-it].GoodDate, [test-it].period, [test-it].ctID,
ScoreCardwCT.InputDate, ScoreCardwCT.Period, ScoreCardwCT.CTID,
[test-it].ctName, [test-it].fcstContactsReceived, ScoreCardwCT.CallsOffered,
ScoreCardwCT.CallsAnswered, ScoreCardwCT.abd
FROM [test-it] LEFT JOIN ScoreCardwCT ON ([test-it].ctID =
ScoreCardwCT.CTID) AND ([test-it].period = ScoreCardwCT.Period) AND
([test-it].GoodDate = ScoreCardwCT.InputDate)
WHERE ((([test-it].period)>#12/30/1899 7:45:0# And
([test-it].period)<#12/30/1899 21:0:0#))
ORDER BY [test-it].period;

Everything matches perfectly until the time Interval (period) gets to 8:30
am and then it goes haywire.

:

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug!

I tried the DateValue as presented and the query worked fine (Thank you!).
However, The two tables are still not matching. Is there a way I can look
at
the value of the elements that I am saying use to Join? A query shows the
data already formatted.

:

DateValue needs to be able to recognize the value as a date, so you need
10/02/2009, not 10022009. You can use the Format function to put the
slashes
in there:

DateValue(Format(10022009, "##/##/####"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Karl,

When I use the DateValue function, I get *ERROR* in the output for all
records. The input data is like 10022009 and is defined by the import
as
a
number with a field size of Long Integer.

:

Try using DateValue instead of CDate.

--
Build a little, test a little.


:

I have a third party application I link to via and OBDC connection
to
retrieve call data information. I also have another application
that
we
import the forecast data from. The forecast information contains a
timestamp
that I have been able to break down into a MM/DD/YY and then further
get down
to the Time interval using TimeValue([timestamp]) The call data has
a
date
field that is defined as a text on the import and the time interval
is
defined as a number. I used CDate on the text field to get MM/DD/YY
then
went further using TimeValue([datefield]) to get the time interval.
When I
try to join these two tables, some of the data is missed. I think
my
problem
is the dates and Time intervals but I really do not know how to
define
them
so they can be joined. Ideas?

 

[Bunky]

Hi John!
I tried to do this but I was never able to get the time to show AM/PM. And
we need to have that since our calls start at 8 AM and stop at 9 PM. The
reporting is one every 15 min intervals so there are 52 intervals per day.
Any ideas how to format each to a date and time and still keep the interval?