Solved

About Compare two MDB DATE Field

Posted on 2000-04-18
11
550 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
ID: 2729304
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
ID: 2729320
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
ID: 2729321
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Expert Comment

by:Ber
ID: 2729340
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
ID: 2729406
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
 

Author Comment

by:hxb
ID: 2729427
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
ID: 2729452
try this:

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

Author Comment

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

Expert Comment

by:AzraSound
ID: 2729614
yes you can use either one
0
 
LVL 11

Accepted Solution

by:
bmatumbura earned 50 total points
ID: 2730005
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
ID: 2733007
Thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro which automatically send Excel spreadsheet to Outlook problem 2 68
Add a task in Outlook from access 11 41
MsgBox 4 62
MS Access 03, TransferText, decimal places 8 74
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

825 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