|
| |
|
|
Ro
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
 |
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
Posts: 653
Joined: 1/30/2006
Status: offline
|
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.
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Ro
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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;
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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;
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Ro
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Ro
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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::#
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Between 2 Dates Query - 6/20/2006 23:34:51
OK, can you post the code as it looks now?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Ro
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Between 2 Dates Query - 6/21/2006 17:00:36
So is date_closed not a date/time field???
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Ro
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
|
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
Posts: 60
Joined: 12/14/2004
From: Auckland's North Shore, New Zealand
Status: offline
|
RE: Between 2 Dates Query - 6/21/2006 17:13:38
Hmmm... did my test and it didn't recall any data either....
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
