MS Access & ADO Query DateTime Field Joins

I have a MS Access DB containing 2 Tables CreditCard Charges and  EmployeeCharges.
I am trying to match employee charges with bank credit card charges via two fields
amount and [tdate].  The Tdate field in both tables is a datetime field.
Using the following sql command both as an MS Access Query and as a Delphi ADO Query
misses records that are visiually present in both tables and should match.

This happens for apparently random  records with no discernable, visual reason for them not to match. The same records fail for either approach.
I'm wondering if the field format has something to do with it( ie general vs short date, etc )


select * from [CreditCard Charges] a
LEFT OUTER JOIN
 EmployeeCharges b
on
 a.Amount=b.amount and  a.tdate=b.tdate

Can anybody offer another way to do datetime field joins?
meyerworksAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
As date values really are doubles, thus may not be exactly equal, you could try with:

select * from [CreditCard Charges] a
LEFT OUTER JOIN
 EmployeeCharges b
on
 a.Amount=b.amount and  int(a.tdate)=int(b.tdate)

which will cut off a time part and bit errors.

/gustav

0
 
peter57rCommented:
"The Tdate field in both tables is a datetime field."

Containing what? A date with zero time or a date with a time value?  Or perhaps one has a time and the other doesn't.
The field format has nothing to do with the problem.  That is just to control the display.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To expand on what Peter wrote:

Access stores Dates and a combination of Date + Time. Unless you specifically store ONLY the date, Access would store the a Date as something like 03/24/2011 05:42:30, meaning March 24, 2011 at 5:42:30 AM. So if you try to compare that to a date of 03/24/2011, that existing record would NOT be shown, since it doesn't match.

In order to do this you need to compare the relevant parts of the Date. For the most part, that's the Month/Day/Year. You can use DateSerial in VBA to do that; I'm not sure what the equivalent would be in Delphi.

DateSerial: http://msdn.microsoft.com/en-us/library/bbx05d0c(v=vs.80).aspx
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
meyerworksAuthor Commented:
If it's a time issue, wouldn't the whole query fail to produce matches?
I've thought of using clng() on the two date fields.
However there is some sort of problem :
clng on the credicard date field returns a long integer, but clng on the employee charges date field returns an error.
I'm not that familiar with MS Access,; how can i verify the field type in the second table, considering one of your replies that the format of a field only deals with visual presentation.
Is the empoyee charge tablle damaged or is there a way to reformat that particular field again?
Thanks all for replying so quickly
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
<If it's a time issue, wouldn't the whole query fail to produce matches?>

Not necessarily. It would depend on how the data got into the field to begin with. If you have code storing a value like this:

03/24/2011

Then that is the only value stored in the field (the Time part would be 00:00:00).

If you are storing a value like this:

YourFieldName = Now()

This would store both Date and Time.

So - it would depend on how the data got into the the database as to exactly how it's formatted and stored.

What sort of error does the "employee chages date" field return? If there are Null values in the field, you'll have to account for those.
0
 
Gustav BrockCIOCommented:
You cannot use CLng as it rounds the values (up or down).
They have to be rounded down only as Int does.
This will not fail except for Null values, so apply a filter for the date field: Is Not Null

/gustav
0
 
meyerworksAuthor Commented:
Thanks both..


"So - it would depend on how the data got into the the database as to exactly how it's formatted and stored."

Exactly: is there someway to 'drill' into this field , so to speak, to see exactly everything that is stored in it,
for the particular records that fail the query>

I thought of copying the table structure as a new table and reloading the data itnto the new table..but time's a wasting.
0
 
Gustav BrockCIOCommented:
First thing is to check the suggestions already offered.

If success, then find the cause for the trouble.
If not - get other ideas.

/gustav
0
 
peter57rCommented:
You have to look at the table definition in Access to find out the field characteristics.

From the question tags, I take it you are using an Access database as the backend and are producing your frontend in Delphi.  If that is so then I'm not sure that any of the functions for standardising the date fields will be available to you in sql.
But to be blunt - you have the application , you have the data, and everyone here is just guessing based on very little information.  
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Good point.

> .. I'm not sure that any of the functions for standardising
> the date fields will be available to you in sql.

Some are. Int is native SQL while CLng is not.

/gustav
0
 
meyerworksConnect With a Mentor Author Commented:
True,true...
Thanks.
Hope you guys are around later.
0
 
meyerworksConnect With a Mentor Author Commented:
Success..
the problem was related to both the date AND the amount field.
After applying the Date-only and Currency Conditioning, the records matched.

Thanks for the insights.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
meyerworksAuthor Commented:
For Delphi users, Int(),Cur()r,Clng() all work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.