Rank: ? (1224)
Member #: 15685

Hy guys...i have a big problem here

Im currently using ADO to call out my database in MS Access and using ASP to display it. The problem now is i need to call out a certain range of data from between two dates. And to note the dates also has the time written with it...do its a "date and time format"

Code:


now i need to group them up by the date and only the date....but it seems to be grouping them up into date and time as well

Any ideas? Any help here would be appreciated

Rank: ? (4821)
Member #: 3416

where are you grouping it? i don't see any grouping in your query...

Rank: ? (1224)
Member #: 15685

Oh Sorry...im grouping it at the SQL statement so the SQL would be like this

Code:

The thing is the withdraw_date has date and time.....if i did that then it would group by the time as well....i was hoping i could just group the date and leave out the time

Rank: ? (4821)
Member #: 3416

you should be able to use your withdraw_date field, but not directly. you can actually use a formula to group by, and i think access stores date/time the way excel does: the number of days since 12/31/1899, with a fractional part for the time.

you could try something like 'group by trunc(withdraw_date)' but i'm not sure if trunc() is a function that access supports. you would probably need to look up a truncate function for access and use that.

Rank: ? (1224)
Member #: 15685

Code:


i got this solution from the net and CDate does only take out the date and strips of the time....but its only for display purposes im afraid.....still cant group by using the CDate.....Know what distinct does?

Rank: ? (4821)
Member #: 3416

distinct means it won't select multiple rows witht he same data. you don't need it if you're using a group by. if it won't let you use functions in your group by clause, you can try something like this:

1. select the date as a column, "CDate(Int([withdraw_date])) as withdraw_date_no_time" gets added to your select clause
2. group by the name of that column, "group by withdraw_date_no_time"

Rank: ? (1224)
Member #: 15685

Doesnt work tho....i got errors....it doesnt seem to accept the withdraw_date_no_time as a valid group by field

Rank: ? (4821)
Member #: 3416

well i'm out of ideas then, unless you can switch to a better database

Rank: ? (1224)
Member #: 15685

hmmm well there is another longer way i could try i guess.....thanks for the help