date in access (Full Version)

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



Message

s2bn1 -> 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 -> 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. [:D]








Spooky -> 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.



s2bn1 -> 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 -> 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 -> 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 -> 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 -> 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 -> 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.



Long Island Lune -> 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



s2bn1 -> 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 -> 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???




s2bn1 -> 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::'




Long Island Lune -> 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 -> RE: RE: date in access (9/3/2002 10:36:35)

Lune,
Still it is giving the same error.



Long Island Lune -> RE: date in access (9/3/2002 20:35:03)

What value did you assemble in T1??? Send me a sample.



s2bn1 -> 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.



Long Island Lune -> 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 -> 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 -> 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 -> 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 -> 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




Long Island Lune -> 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



hhammash -> 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



Long Island Lune -> 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



hhammash -> 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 -> 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 -> 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



hhammash -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
9.399414E-02