PDA

View Full Version : listing results that are between 2 dates


andy106
09-20-2005, 10:33 AM
hi,

I would like to know how you would list records from a database (Access) that are between two dates in dreamweaver.

e.g.

- show records that are between the 20th september 2005 and 20th october 2005.

There is a column that is labeled Date and is set to show 'Date/Time' in access.

Note:- I am using VBScript

Thankyou

davidj
09-20-2005, 10:49 AM
select * from table where date1 between '01-01-2005' and '01-09-05'

or

select * from table where date1 >= '01-01-2005' and date1 <= '01-09-05'

andy106
09-20-2005, 10:59 AM
hi,

i have got that bit of SQL in my recordset but i dont think that i have entered it in properly

e.g.

-----------------------
SELECT *
FROM table
WHERE date1 >= '01-01-2005' and date1 <= '01-09-05' FROM Advertisements
-----------------------

what do i make the variable

davidj
09-20-2005, 11:01 AM
if you have it written exactly as

SELECT *
FROM table
WHERE date1 >= '01-01-2005' and date1 <= '01-09-05' FROM Advertisements

then it will error as you are calling a FROM after a WHERE

it should be

SELECT *
FROM Advertisements
WHERE date1 >= '01-01-2005' and date1 <= '01-09-05'

andy106
09-20-2005, 11:13 AM
hi,

cheers but also its bringing up this error now

-------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/index02.asp, line 34
--------------------------

i know that it is because i haven't assigned a variable on the recordset.

so what do i put in the variable fields:

e.g. Name: date1
Default value: 1
Run-time value: ?

i dont know whether what i have put in those fields (above) are right but its just an example.

cheers

davidj
09-20-2005, 11:43 AM
as a php developer i would write this

e.g. Name: date1
Default value: 1
Run-time value: $_GET[fieldname1]

i think asp is Request Querystring['fieldname1']

ASP people please feel free to hurl abuse when correcting me

andy106
09-20-2005, 12:51 PM
cheers,

one other problem though.

this is what i have put in my recordset so far:

SQL:

SELECT *
FROM CommunityEvents
WHERE Date >= '01-01-2005' AND Date <= '01-09-05'

Variable:

Name: Date Default Value: 1 Run-time Value: Request("Date")

This error comes up:

--------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/index02.asp, line 41
---------------------------------

It could have something to do with the run-time value though i've tried different combinations and it still doesn't work.

im stuck!

davidj
09-20-2005, 01:18 PM
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

I think that error is when you use an Insert or update statement and are trying to send "" (empty value) to a date type field in your database.

you need to echo / print your query to the screen to see whats being output

andy106
09-20-2005, 08:51 PM
hi ya, me again,

still having troubles...

this is what i have in my recordset:

-------------------------------------------
SQL:

SELECT *
FROM CommunityEvents
WHERE Date BETWEEN date AND date+10
ORDER BY Date
-------------------------------------------

- Now this brings up the dates from the 'CommunityEvents' table in the database.

BUT! It doesn't bring up the the dates from today upto the next 10 days.

As you can see i have them set as variables (date and date+10)

This is the code for those variables:

--------------------------
Dim date1, date2
date1 = date
date2 = date+10
%>
-------------------------

- now, the variables above dont do much. What i would like them to look like is this:

---------------------
Dim date1, date2
date1 =<%=date%>
date2 =<%=date+10%>
---------------------

- Now these variables should call todays date (<%=date%>) and the date 10 days from now (<%=date+10%>). BUT! they dont, it doesnt work at all.

If these variables did work then i could just call the variables that are shown in the SQL statement at the top.

Now, i would love to know how you do this becuase i would be really really happy and mainly because i've been trying to work it out all day and its getting a bit frustrating.

thankyou

itsjithu
09-21-2005, 08:27 AM
I believe the problem could be because of the field name - DATE, because date Access confuses between the field name date and the type date...hence try changing the field name of date to something else date1 or so and check if it gives error