Return Date 1 month after text inputted date?

[chris-uk-lad]

Hi,

I have a textbox for the User to enter a date, i want to then retrieving the records from my table which have a date within a month of a textbox date, so say i have a text box return:

    strDateC = txtDateC (e.g 10/01/09)
    strDateC1 = **txtDateC + 1 month** (e.g 10/02/09)

Im unfamiliar with how to break down the date into segments or do i need to do a mid function after converting to a string??

Many Thanks

 

[namliam]

Look up the "Dateadd" function, you will love it

 

[chris-uk-lad]

lovely stuff, thank you

 

[chris-uk-lad]

Hi, just posting here instead of makingnew one as its a follow up, I am running an SQL command in access that returns

ObjConn.Execute  "select * into tblMain from dboMain where DateCreated BETWEEN #" & CDate(strDateC) & "#" and #" & Cdate(strDateC1) & "#"

Both of these work if i do a singular respective search where >= or a <= search, but not when together.

 

[namliam]

The dates have to be entered in US format! (mm/dd/yyyy) or in ISO format (YYYY/MM/DD)

It looks like you may be using Euro format instead (dd/mm/yyyy) which will turn 10/01/2009 into October 1st instead of January 10

Use the left, right and mid functions to pick it appart then use the Dateserial to put it back together or... put it together in the right order.

 

[chris-uk-lad]

Hi Namliam, appreciate the comment but dont believe that to be the case as using the following:

ObjConn.Execute "select * into tblMain from dboADD_HIST_DATA where DateCreated >= #" & CDate(strDateC) & "#"

Does produce the correct result (in this case, DateCreated = 12/01/2008) and the error returned from the BETWEEN sample shown previously (with the between dates being 12/01/2008 and 12/02/2008) is.

Compile Error
Expected: expression

Yet i dont see an error in the syntax

 

[namliam]

where DateCreated BETWEEN #" & CDate(strDateC) & "#" and #" & Cdate(strDateC1) & "#"

The bolded and underlined bit is the cause, but also keep in mind the US format thing, if you dont take care of it, this WILL break down at some point even if right now it is (or seems to be) working.

 

[chris-uk-lad]

i see, 1 extra ", something so simple

thanks for the help, i shall heed your warning of the US date, its a simple tool for one user so shouldnt be an issue too large if ever occurs

 

[namliam]

The date though doesnt fail, it simply produces odd results.... So the user may not notice untill its come and gone and produced days of extra work in double checking results.

Dates like 13-01-2009 will generaly be OK, as there is no month 13.
However 12-01-2009 or 2-1-2009 can be "US-i-fied" and give bad results...