dates and database

D

dawney32

Guppy
Hi there
I am developing a database for my company and need to be able to "ask" the db which persons statements are due on a certain date , for example i open a diag box , type in the date, and the db shows me a list of all persons due a statement on that day........my problem is the statements are due every 28 days and i don't know how to code this.....can any1 help please?
 
is this ment to be a self run script or are you gonig t o run it every 28 days manulally..

what databse type is this in question.

also what language is this ment to be in, or is it ment to be in the database its self. or online.
 
Ideally i'd like it to pop up every 28days and tell me but i think it might be betta to enter the date manually .

Its MSaccess database and its in the db itself, although i do have a little knowledge of visual basic
 
use the WHERE part of the query like this then

WHERE [tablename].[datefield] >= NOW() AND [tablename].[datefield] < [tablename].[datefield] + 28

that is saying get all records that equal today and are within the next 28 days

hope that helps.
 
Emagine said:
use the WHERE part of the query like this then

WHERE [tablename].[datefield] >= NOW() AND [tablename].[datefield] < [tablename].[datefield] + 28

that is saying get all records that equal today and are within the next 28 days

hope that helps.
Click to expand...

Although this is the right direction, you must be very careful here. Dates fields in MS-Access are date/time fields, which means they also store the time. If you update or insert a date field having only provided the date, Access will assume the time is midnight. For example: if you set a date/time field to #2/6/05#, it will actually be 2/6/2005 00:00:00. If you run a query against it WHERE date >= Now(), the Now function will return the current date AND TIME (2/06/2005 11:30:00 for example), which will be greater than the stored date/time. Hence that row will not be returned, even though it has the same date. I would suggest using DateValue(NOW()) instead of simply using NOW(). This will give you today?s date and Access will assume you mean midnight.

riley
 
i did think of that afterwards, but haven't had time to get back to the forums.

that
nks for letting him know..
 
IF someone imports access tables that have datetime field with time only,SQL Server 2000 always uses 12-30-1899 as default date for this field?
Any way to avoid this?
thanks
manos
 
Hi there,

I think the easiest way to do it is to transfer the data via DTS and use a transformation script on those fields to alter them accordingly.

Cheers.
 
Thanks for reply!
As there are only datetime fields and not time fields some asp or other lang script needed anyway for manipulation.
manos
 
You must log in or register to reply here.
Back
Top