Solved

About Compare two MDB DATE Field

Posted on 2000-04-18
11
548 Views
Last Modified: 2010-08-05
VB6, MDB

strSQL="SELECT * from Table1 where StartDate=" & rsTable2("AccessDate")

'StartDate' is a  MDB field,
Type is DATE in Table1.
'AccessDate' is a MDB
field in Table2 (Recordset rsOthers),
Type is DATE.

There is some problem in the SQL string.
I want to SELECT * from myTable where
StartDate= (another Date).

I want to know how to compare two
Date Data.
How to write the SQL string.
"SELECT * from table1 where ...."

0
Comment
Question by:hxb
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
strSQL="SELECT * FROM Table1,Table2 where StartDate = AccessDate"

However instead of using SELECT *, specify the fields from Table1 you want to show

e.g.,
SELECT Table1.Field1,Table1.Field2,Table1.Field3....
0
 

Author Comment

by:hxb
Comment Utility
Do not Use the Table2 dirrectly,
I have open it to a recordset.

I want to use the code such as:
rsTable1.findfirst "StartDate=" &  OtherDate

otherdate=rsTable2("AccessDate")

Why "StartDate=" & otherdate is not
correct?
it always return False

Thanks
0
 
LVL 2

Expert Comment

by:Ber
Comment Utility
I think this is what you are looking for

Syntax:
Sql = "SELECT Table1.*"
Sql = Sql & " FROM Table1, Table2"
Sql = Sql & " WHERE (Table1.Date = Table2.Date);"

Your SQL Statement:
strSQL="SELECT Table1.*"
strSQL = strSQL & " FROM Table1, Table2"
strSQL = StrSQL & " Where (Table1.StartDate = Table2.AccessDate);"

In this case you will have to change the Table and field names to your own but that should be no problem.
I tested this and it should do the Job if I understand the Question properly

Good Luck
Ber...

P.S.Any Further Problems Just leave a note and I'll answer as soon as I can  
0
 
LVL 2

Expert Comment

by:Ber
Comment Utility
I added this as you were posting you Message and did not Know you were Not using the database drectly....I'll come back to you .....Post the actual code  
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
why dont you just create another recordset to handle this query??

Dim rs As Recordset
Set rs = New Recordset

rs.Open "SELECT Table1.Field1, Table1.Field2 FROM Table1,Table2 WHERE StartDate = AccessDate", dbConnection, adOpenStatic, adLockOptimistic
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:hxb
Comment Utility
No,
Another way to say:

I want to find a record in the
recordset, and must compare two
date data.
rsTable1.findfirst "StartDate=" &
rsTable2("AccessDate")
if rsTable1.nomatch=false then
   'Find
   'do something
endif

I know it should found, because in
Table1, there is a record that
match rsTable2("AccessDate"),
But rsTable1.nomatch always return
True.
I want to know why.

if I try to find another field, rsTable1.findfirst "CardID=" &
rsTable2("Cardid"),
it can found.
But When the field is a DATE field,
It can not. why?
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
try this:

rsTable1.FindFirst "StartDate = #" & rsTable2![AccessDate] & "#"
0
 

Author Comment

by:hxb
Comment Utility
what's diffrent between
rsTable2("AccessDate") and
rsTable2![AccessDate] ?
Is it same ?
0
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
yes you can use either one
0
 
LVL 11

Accepted Solution

by:
bmatumbura earned 50 total points
Comment Utility
What i think is that the date type in MDB Databases stores the date and the time so that if you want to compare dates you have to take the time into account. To work around this problem, use an SQL statement such as the following, for example:

Str = "SELECT * FROM ThisTable WHERE " & FieldName & " = #" & Format(yourDate, "dd-MMM-yyyy") & "#"

assumming that you stored the dates in FieldName in the format 'dd-MMM-yyyy' and yourDate contains the date.
0
 

Author Comment

by:hxb
Comment Utility
Thanks
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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 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

8 Experts available now in Live!

Get 1:1 Help Now