Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

About Compare two MDB DATE Field

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
hxb
Asked:
hxb
  • 4
  • 4
  • 2
  • +1
1 Solution
 
AzraSoundCommented:
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
 
hxbAuthor Commented:
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
 
BerCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
BerCommented:
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
 
AzraSoundCommented:
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
 
hxbAuthor Commented:
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
 
AzraSoundCommented:
try this:

rsTable1.FindFirst "StartDate = #" & rsTable2![AccessDate] & "#"
0
 
hxbAuthor Commented:
what's diffrent between
rsTable2("AccessDate") and
rsTable2![AccessDate] ?
Is it same ?
0
 
AzraSoundCommented:
yes you can use either one
0
 
bmatumburaCommented:
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
 
hxbAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now