Solved

MS Access &  ADO Query DateTime Field Joins

Posted on 2011-03-24
14
697 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:meyerworks
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35212963
"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
 
LVL 84
ID: 35213962
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 300 total points
ID: 35214034
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
 

Author Comment

by:meyerworks
ID: 35215475
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 35215601
<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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35215752
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
 

Author Comment

by:meyerworks
ID: 35216149
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35216177
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
 
LVL 77

Expert Comment

by:peter57r
ID: 35216277
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 35216363
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
 

Assisted Solution

by:meyerworks
meyerworks earned 0 total points
ID: 35216380
True,true...
Thanks.
Hope you guys are around later.
0
 

Assisted Solution

by:meyerworks
meyerworks earned 0 total points
ID: 35222265
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35222411
You are welcome!

/gustav
0
 

Author Closing Comment

by:meyerworks
ID: 35275253
For Delphi users, Int(),Cur()r,Clng() all work.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now