Greater Than and Less Than

[Ianpen]

I have boxes labeled FromDate and ToDate and inside the boxes are files that fall between the dates. I now want to be able to do on a date and it will show which box to go to.

I have been able to achieve this in Access by doing this

SELECT Heritage.ID, Heritage.barcode, Heritage.FromDate, Heritage.ToDate, Heritage.location, Heritage.type
FROM Heritage
WHERE ((([enterdate])>=[heritage.fromdate] And ([enterdate])<[heritage.todate]));


I have come to replicate it in ASP and have done this

set objRS = con.execute ("SELECT heritage.ID, heritage.Barcode, heritage.FromDate, heritage.ToDate, heritage.location, heritage.type FROM Heritage WHERE Heritage.FromDate >= #"& request("showdates") &"# and Heritage.ToDate < #"& request("showdates") &"# ORDER BY heritage.type ASC;")

But it doesn't show anything. If you take out either of the 2 > or < then it will return results as expected.

Any help on this would be much apprieciated.

Thanks

 

[Kendel]

In ASP, you don't need # around date value.

 

[DotNetGnat]

Kendel, he is using Access so i think he needs # around dates...the problem is with the date formats...

-DNG

 

[Ianpen]

Thanks DotNetGnat, I will look into that.

 

[DotNetGnat]

First try hardcoding the values to see if it works:

something like this:

set objRS = con.execute ("SELECT heritage.ID, heritage.Barcode, heritage.FromDate, heritage.ToDate, heritage.location, heritage.type FROM Heritage WHERE Heritage.FromDate >= #11/23/2005# and Heritage.ToDate < #11/23/2005# ORDER BY heritage.type ASC;")

if that works...then try using some date conversion functions...

-DNG

 

[Ianpen]

It doesn't work hardcoded. ???

 

[DotNetGnat]

does that same query work when u try it directly in ACCESS.

-DNG

 

[Ianpen]

This does which is the same thing in essence (isn't it?)

SELECT Heritage.ID, Heritage.barcode, Heritage.FromDate, Heritage.ToDate, Heritage.location, Heritage.type
FROM Heritage
WHERE ((([enterdate])>=[heritage.fromdate] And ([enterdate])<[heritage.todate]));

I'm a bit stupid so you will have to bear with me!

 

[theniteowl]

Try something like this:

("SELECT heritage.ID, heritage.Barcode, heritage.FromDate, heritage.ToDate, heritage.location, heritage.type FROM Heritage WHERE " & request("showdates") & " Between Heritage.FromDate AND Heritage.ToDate ORDER BY heritage.type ASC;")

Are the parens necessary?

I do not think that you will need to surround the request("showdates") with # signs in this case because you are just comparing a string to what is returned from the query, but it is an easy change if you do need to.

Also, I would recommend not doing the request("showdates") right inside the query. You should get that value and test it to not be null first, then call the variable you stored it in within the query.



Paranoid? ME?? WHO WANTS TO KNOW????

 

[DotNetGnat]

can you please do a Response.Write strSQL

where strSQL your sql query string

-DNG

 

[Ianpen]

I'll have to try it when I get home. Thanks for the help upto now and I will let you know how I get on.

 

[Chopstik]

Though this might not necessarily be related to the problem at hand, isn't "type" a keyword in Access? If so, you may also want to re-name that particular field.

One other thing to look at is whether your date fields are indeed date fields (and not text or numeric fields). What kind of data are you supposed to be passing back to them (or have you been passing back when in Access)? It's a small thing, but stranger things have been known to occur.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)