Web Development

Ask something
Discussion Locked

Access dates

0 helpful
19 replies

by mr. origami

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How do I compare dates? Let's say I have a table (tblEmp) with 2 fields - Name and HireDate. I need to display all employees hired before a certain date. Problem is I am using Dreamweaver to create my recordsets - so here's what I have so far:
SELECT *
FROM tblEmp
WHERE HireDate <= 'varDate'

Then I define a variable 'varDate' and set it's default value to Date() and runtime value also to Date() just for testing (to see who was hired before today), but get an error saying there's a type mismatch. How do I get the results I want?

19 replies

by timfox

By rule of thumb, when comparing values a < b, both a and b should be the same data type.
ie Both HireDate and varDate must both be the same data type.

Secondly - if varDate is a variable, why is it enclosed in single quotes? Variables shouldn'tneed quotation marks (which in a number of programming languages reflect a textual string value, rather than a variable).

If the format of dates are giving you too many headaches, it might be easier for you to convert both values to numbers in the format yyyymmdd (or yyyymmddhhss, using a 24hour clock)
ie
200305200100 equals 20-May-2003 1am
200305200820 equals 20-May-2003 8:20am
200305201200 equals 20-May-2003 midday
200305202130 equals 20-May-2003 9:30pm
200305212131 equals 21-May-2003 9:31pm

All the best
TimFox

by timfox

You can use the CDate, CInt function etc to convert data types - but you're better off doing this in the code, rather than changing database field types (as I suspect you will).

The help section here is quite good - look at topic "Type ConversionFunctions"

Also look at using the format function to nominate your date component order, ie
dd/mm/yy or mm/dd/yy or dd-mmm-yyyy etc

by mr. origami

you:
The help section here is quite good - look at topic "Type Conversion Functions"

Where do I find this?

by timfox

Sorry - I have no experience with Dreamweaver, so you will need to review some Dreamweaver doco - but I suggest that the theory still stands.

Good luck
TimFox

by mr. origami

Okay - but where do I find this rumored "help section" with the topic "Type Conversion Functions" of which you speak?

by timfox

Your title: 'Access Dates' made me think of Microsoft Access, which has the mentioned help section.

by mr. origami

Still doesn't work. 2 strange things - I can set an equal and it works, greater than or = and it works, less than or equal and no good:
hireDate = varMyDate works
hireDate >= varMyDate works
hireDate <= varMyDate does not

Also - I can test the recordset within Dreamweaver and see my results. When I pull it through the ASP page, I get an empty recordset.

by john_wills

You're getting closer. In what concrete way does hireDate <= varMyDate not work? For what combinations of relative values do you get false results? There are three possible relations, viz. >,=,<. When you know which of these gives a false result, try putting a literal date into one side of the relation. Do you still get the wrong result?

by mr. origami

I've been using literal dates and having the same problem. Maybe it's a problem with the Access/Dreamweaver combination as I can say:
hireDate = #5/20/2003#
and have Dreamweaver test the recordset and display results. Then pull the page through my browser and get no results. Of course, other stuff works fine so I know the db connection is good.

by mail2krisk

hai...
just tell me from where u r storing the data. i mean in ms-access,oracle..etc. I will try to help u.

by mail2krisk

hai...
just tell me from where u r storing the data. i mean in ms-access,oracle..etc. I will try to help u.

by seyes

Greetings,
I've not used Dreamweaver, but use Access all the time.
Have you tried using a Access querry to get your results first then pull the results up in Dreamweaver?

SeYeS

by mr. origami

Don't know how to include the variables I need in the Access query. Users are typing in a number of days (see employees hired in the last x-days). In Dreamweaver, I can define that as Date()-Request.QueryString("days"), meaning today's date minus what the user typed in to the form. How would I do that in an Access query?

by seyes

Hi!
Again forgive me for not knowing Dreamweaver, but...
I work in Access 97, and the simple querry for my records is:
SELECT Emp.HireDate
FROM Emp
WHERE (((Emp.HireDate)<=[Date]));

then make
Date = varDate
or vice versa in Dreamweaverthis is only a guess!
SeYeS

This discussion is closed to new replies.