[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

About Compare two MDB DATE Field

Posted on 2000-04-18
11
Medium Priority
?
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses

656 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