[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

About Compare two MDB DATE Field

Posted on 2000-04-18
11
Medium Priority
?
557 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
Independent Software Vendors: 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

872 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