OutFront Forums
     Home    Register     Search      Help      Login    

Follow Us
On Facebook
On Twitter
RSS
Via Email

Recent Posts
Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Sponsors
Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.
Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.
Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax
 

date in access

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP, PHP, and Database >> date in access 		Page: [1]
 
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?
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: date in access - 8/29/2002 12:58:57   
s2bn1,

[:j] The reason no results are being returned to you is because you are asking the query to search an entire date by it' s individual component.

Your comparisons are: 6 digit date against 2 digit month, 6 digit date against 2 digit day, 6 digit date against 2 digit year (or 4 digit year). No results would be returned.

You have two choices:

1): Combine your 3 dropdowns in .asp to form a date string, then compare this string to your stored dates.

or...

2): Create 3 separate fields in your database. One for month, one for day and one for year.

Then your SQL would look like this:

Select * from table1 where day like ' %::day::%' and month like ' %::month::%' and year like ' %::year::%'

I have this exact scenario in one of my databases. It makes report generating very easy. :)







_____________________________


(in reply to s2bn1)
Spooky

 

Posts: 26680
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: date in access - 8/29/2002 17:03:51   
Note that " date" is a reserved word when using access, and as such shouldnt really be used as a field name or object name.

You may be able to avoid errors by using [] around the text, or preferably, renaming the access column.

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to s2bn1)
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.

(in reply to Long Island Lune)
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::%'



_____________________________


(in reply to s2bn1)
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::%'





_____________________________


(in reply to s2bn1)
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.

_____________________________


(in reply to s2bn1)
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::%'



_____________________________


(in reply to s2bn1)
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.

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: date in access - 8/31/2002 11:06:02   
s2bn1,
It sounds to me like you are going to have to use an edit-field that allows the user to enter a 6-digit date. Or leave your three drop down list boxes the way they are and assemble the 3 drop down list box values in .asp to form one 6-digit date prior to the DRW. Then you could create your query like this:

SELECT * from table1 WHERE myDate < #" & Date() & " #"
:)

It all depends on how you want to accomplish the job, the easy way or the hard way. [:j]

Easy: Edit-field
Hard: Drop downs

Good luck :)
LLL

_____________________________


(in reply to s2bn1)
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

(in reply to Long Island Lune)
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???



_____________________________


(in reply to s2bn1)
s2bn1

 

Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline

  		RE: RE: date in access - 9/1/2002 10:33:01   
I done it the same way and I got the following error messages, which is embeded as an image.and my query is

Select name,no from table1 where my_date=' ::t1::'



Attachment (1)

(in reply to Long Island Lune)
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::#



_____________________________


(in reply to s2bn1)
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.

(in reply to Long Island Lune)
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.

_____________________________


(in reply to s2bn1)
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 >

(in reply to Long Island Lune)
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

_____________________________


(in reply to s2bn1)
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


_____________________________


(in reply to s2bn1)
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.

(in reply to Long Island Lune)
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.



_____________________________


(in reply to s2bn1)
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 >

(in reply to s2bn1)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: date in access - 9/7/2002 19:38:14   
Hisham,

You found the original post that I asked the " < Date Problem" question on... Very good. I responded on the other post so check it when you get a chance. Thanks again. :)

s2bn1,

Give Hisham' response a try. Maybe it' s ME and I' m doing something wrong.

LLL

_____________________________


(in reply to s2bn1)
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

(in reply to s2bn1)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: date in access - 9/8/2002 12:09:28   
s2bn1,

hhammash :) SOLVED your problem. Here is the link to his remedy concerning your date query question:

http://www.frontpagewebmaster.com/tm.asp?m=88593&appid=&p=&mpage=1&key=&language=&tmode=0&s=#88736

Go there now and take a look at what he did. I tested it. It worked. So thanks go to Hisham (hhammash). :)

Cheers!!!
LLL

_____________________________


(in reply to s2bn1)
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

(in reply to s2bn1)
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.

(in reply to hhammash)
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

(in reply to s2bn1)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: date in access - 9/9/2002 13:56:19   
Hi,

Please check the link below for the Java Button Code:

http://javascript.internet.com/forms/format-date.html

Regards
Hisham

(in reply to s2bn1)
Page:   [1]

All Forums >> Web Development >> ASP, PHP, and Database >> date in access 		Page: [1]
Jump to: 1





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


Copyright © 2009 Thomas Brunt's OutFront Inc. All Rights Reserved.