By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Dates don't compare right...

P: 65
I've got this date I queryd from a table, and I want to compare this date to the same field as where it came from but it keeps returning an empty recordset. Can anyone help me.

Expand|Select|Wrap|Line Numbers
  1.     Set rst2 = db.OpenRecordset("SELECT DISTINCT Datum FROM patParameters WHERE eadnr =" & Form_Testinvoer.eadnr & ";")
  2.  
This is the query where I get the date first.

Expand|Select|Wrap|Line Numbers
  1. Do Until rst2.EOF
  2.         dag = DateValue(rst2.Fields(0))
  3.         If Not (rst2.EOF) Then
  4.             Set rst = db.OpenRecordset("SELECT p.Datum, p.Parameter, p.Waarde FROM PatParameters p WHERE p.eadnr=" & Form_Testinvoer.eadnr & " AND p.Datum=#" & dag & "#;")
  5.             Do Until rst.EOF
  6.                 Debug.Print rst.Fields(0) & " " & rst.Fields(1) & " " & rst.Fields(2)
  7.                 rst.MoveNext
  8.             Loop
  9.             rst2.MoveNext
  10.         End If
  11.     Loop
This is where I try to compare it.

I'm using access 2003.

Kind regards

Twanne
Jul 18 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Replace Form_Testinvoer with Me
Jul 18 '07 #2

P: 65
Thanx

I'll try it out and hopfully I'll be on my way then.

Greetz
Jul 18 '07 #3

P: 65
Hi again,

Apparently my problem wasn't an error in comparing dates. I'm not sure why but when there is only one record to select from my table, and I try to read it, an error emerges saying there ain't no record. From the moment there are more the problem disapears.

Greetz
Jul 19 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
I think the "why" is that with only 1 record, you are at the EOF (end of file) immediately! I haven't used looping in a longggggggggg time, but I think if you changed your looping syntax from

Do Until rst2.EOF...Loop

to

Do Loop...Until rst2.EOF

it would run with only a single record.

Linq ;0)>
Jul 19 '07 #5

P: 65
Thanx

I figured it out the way you said and it's doing something now :)

Normally I'll be on my way progging like hell :D

Greetz
Jul 19 '07 #6

P: 65
And again we got a problem

I've got to check a date in a table, and I select it from the same table.

Now when I check with most of the dates it gives a good result, but when access checks the date it does this in mm/dd/yyyy format if it can.

Now all my dates are in dd/mm/yyyy format so for some it gives a weird result.

example:
When I have 14/12/2006 it check like it should because there is no other option to check it. When I have 11/01/2007, access cheks this date as 01/11/2007.

Is there a way to get around this?

I use this function to make sure it is in dateformat.
dag = DateValue(rst.Fields(0).Value)

Greetz
Jul 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.