|
| |
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
 |
date in access - 8/29/2002 8:43:04
In my table I made a date field and in my search page I made 3 drop down menus one for day, one for month and one for year and I used the sql statement for search as
Select * from table1 where date like ' %::day::%' and date like ' %::month::%' and date like ' %::year::%'
but it is not giving me any results.
2) If I want the above select statement to find out the records till the selected date how should be the select statement be?
|
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 8/30/2002 1:34:31
ok, Lune but in this case how can I find the records till a selected date.
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 8/30/2002 11:57:56
It would be something like this:
If you want the date to go back 10 days:
Select * from table1 where day BETWEEN #" & Day(Date()) & " # AND #" & Day(Date())-10 & " #" and month like ' %::month::%' and year like ' %::year::%'
_____________________________
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 8/30/2002 15:18:33
But of course - that might not be what you want. The method I showed you in my last post dealt with TODAY' s date. But if you want to let a user enter a month / day / year from your site and then go back 10 days, that would be different:
Select * from table1 where day BETWEEN Day And Day - 10 and month like ' %::month::%' and year like ' %::year::%'
_____________________________
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 8/30/2002 15:23:03
Also,
If you using Date, Day, Month or Year in your actual query, illegal, change them to something like myDate, myDay, myMonth and myYear.
_____________________________
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 8/30/2002 15:28:59
And your database too. Your databse fields should be something other than Date, Day, Month and Year. If your using Date, Day, Month and Year in your database, change them to myDate, myDay, myMonth, myYear.
Select * from table1 where myDay BETWEEN myDay And myDay - 10 and myMonth like ' %::myMonth::%' and myYear like ' %::myYear::%'
_____________________________
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 8/31/2002 1:29:44
For me I want something like this, the user enter a date and I want the records from the beginning till the entered date.
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 8/31/2002 13:21:02
You got it.My boss want it using 3 drop down menus.Now my problem is I won' t get it by submitting once.Means after the user select the drop down menus and submit it go to a form page as an edit field and again he should submit it.Is there any way I get the results after submitting it once.
Also I made [u]mydate field as short date
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 8/31/2002 13:32:05
What you could do is this:
1): Create a form on page 1.
2): Insert your 3 drop down fields.
3): Post the results to page two.
4): In page two use .asp or VBScript to combine the three date segment values into one 6-digit date value.
5): Apply this value to the DRW to return your database results.
Combining the three date segments is basic .asp type scripting.
I think this would solve your problem quickly.
Can you do that???
_____________________________
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 9/1/2002 11:53:49
s2bn1,
The code in the Database Results Wizard that is generated by FrontPage, does not support querying a date/time data type in an Access database.
Try this:
Select name,no from table1 where my_date=#::t1::#
_____________________________
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 9/3/2002 10:36:35
Lune,
Still it is giving the same error.
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 9/3/2002 20:35:03
What value did you assemble in T1??? Send me a sample.
_____________________________
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 9/4/2002 8:20:04
It is in the attachment which I had send you before. It is like 26/08/02.
< Message edited by s2bn1 -- 9/3/2002 8:20:55 AM >
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 9/4/2002 14:40:38
s2bn1,
Sorry - I forgot about your attachment. I just looked at it. I am creating a two page scenario on my machine to test this out. I' ll make another post a little later.
LL
_____________________________
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 9/5/2002 11:56:04
s2bn1,
I did some testing with the website I have that queries dates. The problem is that my query is not the same as yours.
The " <" is your problem.
If you do this:
WHERE (DateJoined = #::eDate::#)"
The query works.
But if you:
WHERE (DateJoined <= #::eDate::#)
or
WHERE (DateJoined < #::eDate::#)
It does not work. FP tries to close the open < like the following:
WHERE (DateJoined = #::eDate::#)>
which is not right.
There is a way to use the " <" symbol but I just have to figure it out.
I' ll make another post when I solve this problem.
Anybody else out there have any suggestions???
LLL
_____________________________
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 9/6/2002 4:59:45
ok,I thought it is some problem with my access.Thankyou, waiting for your reply.
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: date in access - 9/7/2002 12:49:52
s2bn1.
I made another post in the ASP/Database section. I can' t figure out the problem. Let' s hope someone else is familiar with your problem.
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: date in access - 9/7/2002 17:15:10
Hi,
Here is the solution.
In the DRW HTML view, locate your date field in the grey code. Let' s say your date field is " Mydate" locate it in the grey code where it says
' ::Mydate::' and change the ' into #, so your date field in the grey code becomes
#::MyDate::#
Save the page and that' s it.
Then Check the SQL line in the HTML view to make sure that it says:
fp_sQry=" SELECT * FROM Dates WHERE (MyDate < #::DateEntered::#)"
I repeat: Complete your DRW, set the date " <" or " <=" ..etc, no matter then go to the grey code after you finish and save it.
Regards
Hisham
< Message edited by hhammash -- 9/6/2002 5:38:57 PM >
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: date in access - 9/8/2002 0:30:10
Hi,
Thank you LLL.
When I changed the date in the custom query In noticed that it did not change in the DRW HTML grey code, so I gave it a try and changed it in the DRW grey code. And you know, when you change it in the grey code, it also does not change in the custom query.
Regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: date in access - 9/8/2002 12:14:08
Hi,
Thank you LLL.
I don' t know what to say, I am speechless.
Thanks
Hisham
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: date in access - 9/9/2002 12:45:12
Thankyou Hisham and LLL.I was really worried about the date problem.Now, Could you give me the javascript to convert mm/day/yr to day/mm/yr.
Thankyou once again.
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: date in access - 9/9/2002 12:48:28
Hi,
1- Is your initial problem solved?
2- Do you need the JavaScript for the Field that forces the user to enter date in dd/mm/yyyy from the web?
Regards
Hisham
| |
|
 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 
k¥ 
