Problems with a "filter" in a query 2

[Jahappz]

i have one form where i have the following field

Date: (default value=today) where the user gets today's date

in my query i want to have a selection on all posts between today's date and 6 months back in time how should i write the
"code"?

i cant just write Between 2002-04-30 and 2002-10-30 since the last date is dynamic (todays date)
any ideas???

 

[essnrv]

You need to write a code to find out last date of that particular month.Replace strMonth with 6 Month and strYear with respective year
strMonth= thismonth+6
select case strMonth
case 1,3,5,7,8,10,12
strDay = 31
case 4,6,9,11
strDay = 30
case 2
if (strYear/4) = 0 then
strDay = 29
else
strDay =28
end if
end select
strEndDate = strMonth & "/" & strDay & "/" & strYear Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[Jahappz]

Okey, but where do i put that code?

i have a query right now and i cant put the vb code there right? so where should i put it?

Sorry iam a newbie =)

 

[essnrv]

Before executing your query, declare another variable called
Like
Dim dtDate as Date

'Place above code here

dtDate=cdate(strEndDate)

In your query
Where PostDate between Date and dtDate

Hope this helps Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[frontside]

I had some problem getting the code to work, i dont know if its me that is stupid (probably) can i send you the database (200kb) to you somehow? so you can have a look at it and tell me whats wrong???


Thanx

 

[essnrv]

Sure, You can mail me at needjob9@yahoo.com.Is it access 2000 or 97. I have access 97 software on my system. Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[essnrv]

If you want I will send you a sample form with this code which dispalys the date after 6 months Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[Jahappz]

Ok i have emailed you the file now

Many thanx for your help!

 

[chip38]

Jumping in late here... but I have a simple question... If today is 8/30/02... do you consider 6 months back as 3/3/02 (180 days)... or 2/28/02 (the last day of the 6th month back)???

 

[Jahappz]

as the last day of the 6th month back in my case

 

[Jahappz]

Essnrv its a 2000 database.. but ill send you the same one in 97-format

 

[essnrv]

I mailed you back. but I didn't check the report. If you have any problem please send back in Access 97.It shows 2/28/02 as per calender and I think the same. But If you want to get a date 180 days back it is also possible, need to change code.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[Jahappz]

Big thanks to you essnrv! it works perfect!

 

[arwhite]

In your query under the date field, enter this in criteria:
>Now()-180

This filters for anything greater than today's date minus 180days (6 months)

 

[Jahappz]

Yes that works but now i have run into another problem, When i look at the date field in the economic system it has a format like "200211" (YYYYMM) how do i count down a date in that format?