More using DateDiff to create a recordset in UD 1

[Jables]

Here's what I want to do. I have a site where users login and update information about themselves. I have been trying to create a page that shows people who have updated say within the last three days only.

I have been trying to create this recordset using the ultradev dialog box, but I can't seem to get it right. In the database table, there is a field called DateUpdated which gets populated when the user logs in to update information. I am trying to use the DateDiff function to select the relevant information. My SQL query looks something like this...

SELECT username, DateUpdated
FROM tblPeople
WHERE timelapsed > 4

In the variable list-box I have

variable name: timeelapsed
default value: 1
Run-time value: DateDiff("d",Date(),DateUpdated)

When I test the recordset in Ultradev, it just selects all of the records. If I change the default value to 5 or anything > 4, it selects no records. So timeelapsed is getting populated with the default value.

I'm stuck. Any ideas?

 

[mlawson]

Jables,
I make all my queries up in MS-Access and then load the query into the recordset with no other changes to it (as the query carries over the criteria with it).
The meaty bit of the coding as follows;

-In access create a new query and add all your fields to the query in design view
-Under the DateUpdated field in the criteria field type in
"between Date() and (Date()-3)", this, when executed in Access will only report back the events that occurred from today and 3 days previous.
UD is great for designing and basic recordset layouts, if you need to create a query, create it IN Access, not UD.

Any probs, drop a post

Have Fun.

M

 

[Jables]

Works like a charm. With your solution, I was up and going in about 5 minutes!!!

Yeah, my initial SQL statement didn't really make any sense. I was trying to restrict records by criteria that didn't even exist in the database.

SELECT username
FROM tblPEOPLE
WHERE x < 4

in that example, there's no field in the dbase to which x refers. Silly me. Thought myself into a black hole there.

 

[mlawson]

Jables,

Glad to be of help. Just as a reminder to folks trying to make fairly complex queries, remember that Access is a lot more flexible and you can test your queries as lot more quickly when they are created in Access.
Sorry to go on but it's best to try to prevent folk going down the wrong path (been down that one-way dead-end street myself!)

Have fun!

M