Between 2 Dates Query (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Ro -> Between 2 Dates Query (6/18/2006 20:37:47)

Hi
I'm having trouble with a between 2 dates query - I've read most relevant info previously listed and have tried the suggestions.
I'm using FP03 - pasting the query into DRW.
My current query looks like this.

SELECT main.description, main.ID, main.orig_name, main.person_to_action
FROM main
WHERE (((main.date_closed) Between '%%Start_date%%' And '%%End_date%%'))
GROUP BY main.description, main.ID, main.orig_name, main.person_to_action;

I'm not getting any errors - but I'm also not getting any search criteria fields to enter my between date options in my asp page.
Any suggestions would be much appreciated.
Cheers



yb2 -> RE: Between 2 Dates Query (6/19/2006 6:44:05)

try hard coding any variables, like the dates, at first and then replace them with the variables as you see the code working. It helps to check you're getting the data out the way you want and find any errors in the absence of a debugger.

You can do the same with form fields.



Ro -> RE: Between 2 Dates Query (6/20/2006 19:34:52)

Tried that and it worked fine with fixed dates - however still can't get the fields to generate in my asp.
I'll look at trying some other code.... any ideas.[sm=BangHead.gif]



BeTheBall -> RE: Between 2 Dates Query (6/20/2006 20:07:04)

Where to Start_date and End_date come from? Are they variables, form field values?



Ro -> RE: Between 2 Dates Query (6/20/2006 20:10:16)

They are just fields names - they aren't listed in the database table.



BeTheBall -> RE: Between 2 Dates Query (6/20/2006 20:26:31)

Form field names? If so, try:

SELECT main.description, main.ID, main.orig_name, main.person_to_action
FROM main
WHERE (((main.date_closed) Between '::Start_date::' And '::End_date::'))
GROUP BY main.description, main.ID, main.orig_name, main.person_to_action;



Ro -> RE: Between 2 Dates Query (6/20/2006 20:39:11)

Hmmm... I've tried that one in the past... just tried it again.... in DRW I get this message when I try and verify the query... "Custom query contains errors". If I change the "::" for "%%" I can verify the query - but I still don't get any fields to enter the between dates. I do however get a Data Type mismatch in criteria expression error in my DRR. Then when I open the DRR again the "%%" are gone and I've got "::"s back in the custome query.
Do you think it might have something to do with my field main.date_closed being a Date/Time fields?
[X(]



BeTheBall -> RE: Between 2 Dates Query (6/20/2006 20:45:47)

What type of db are you using?

If Access, try:

SELECT main.description, main.ID, main.orig_name, main.person_to_action
FROM main
WHERE (((main.date_closed) Between #::Start_date::# And #::End_date::#))
GROUP BY main.description, main.ID, main.orig_name, main.person_to_action;



Ro -> RE: Between 2 Dates Query (6/20/2006 20:51:29)

Just tried that.... I am using Access... and I see where you're coming from....
BUT i get this when I try and verify.
The server encountered an error..... details....

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(((main.date_closed) Between #1# And #2#))'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)



BeTheBall -> RE: Between 2 Dates Query (6/20/2006 21:09:00)

Rather than do a custom query, try just using the wizard. In step 3, click the More Options button and then Criteria. Click the "Add" button. Choose your date field in the field dropdown. Then choose Greater Than in the Comparison dropdown and then type start_date in the Value box. Then click Add again, repeat the process, except in the comparison dropdown, choose Lesser Than and in the Value box type end_date. Finish the wizard. That should do it.



Ro -> RE: Between 2 Dates Query (6/20/2006 21:16:06)

OK - done that.... got the fields to type in my criteria....
Getting Data Type Mismatch in criteria expression -2147217913
Seems be be related to my field type still......



BeTheBall -> RE: Between 2 Dates Query (6/20/2006 22:59:35)

Sorry, forgot one step. Go into the gray code and there you will see your SQL statement. Within the SQL statement will be something like:

WHERE date_closed > '::start_date::' and date_closed < '::end_date::'

Change all the single quotes to # and save the page. So the above would become:

WHERE date_closed > #::start_date::# and date_closed < #::end_date::#



Ro -> RE: Between 2 Dates Query (6/20/2006 23:22:44)

Hmmm... better - I had to edit the page in Notepad - as I was getting "The contents of a FrontPage component has been modified. These contents will be overwritten when you save the page." - the error still shows in the DRR - but that doesn't matter I can just put the between fields on a different page. BUT I'm not getting any results - there is data in the database - but when I search a between two dates nothing is showing.... We are getting somewhere though - I'm optimistic! [sm=rolleyes.gif]



BeTheBall -> RE: Between 2 Dates Query (6/20/2006 23:34:51)

OK, can you post the code as it looks now?



Ro -> RE: Between 2 Dates Query (6/21/2006 15:53:41)

fp_sQry="SELECT * FROM main WHERE (date_closed > #::Start_date::# AND date_closed < #::End_date::#)"

I'm thinking it might have something to do with the data type in my column. I might try adding a new column - set the data type to Date/Time and enter some new sample data - then change the query to reflect the new column name then give that a go. I'll get back to you when I done that. BUT if you have any other suggestions I'd welcome then.
Cheers



BeTheBall -> RE: Between 2 Dates Query (6/21/2006 17:00:36)

So is date_closed not a date/time field???



Ro -> RE: Between 2 Dates Query (6/21/2006 17:03:38)

Yeah it is now.... but it didn't start out life like that - it was a text field I think. Just doing the test now...



Ro -> RE: Between 2 Dates Query (6/21/2006 17:13:38)

Hmmm... did my test and it didn't recall any data either....



Ro -> RE: Between 2 Dates Query (6/21/2006 22:42:32)

Hi Duane
You are the BOMB! - (In "Kiwi" - that means the best!)
I went in and added the #::start_day::# in the gray code. Added the Default Value and U-Beauty it works!
I get an Red - Custome Query contains error message - but no error when I view it in the Browser.
I think the date field is browser specific - I didn't even need to use US date format.
Thanks again.
[sm=yupi3ti.gif]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375